Need help on Match value PowerQuery

[Zoplie]

Hello, I'm having trouble using power query and I can't get the desired result.

I have a column with a list of countries separated by ",".
The table header shows the country names.
I'd like an "X" to be added to the intersection of the data, as if circled in green.
For example, for "France" I need to add an "x" in all the columns named "France (....)" but if it says "Frances (...)" I don't add an "x".
I had done a header split for this last point but power query automatically adds a number if the column name already exists (France, France2 ect).

Is this possible? Can you help me?

In the example below, I've filled in the "X" manually to show you the desired result:

[AlienSx · Accepted solution]

Hello, @Zoplie if you start with blank table with all headers in place then try this:

let
    Source = your_table,
    f = (countries as text, h as list) =>
        [a = Splitter.SplitTextByDelimiter(", ")(countries),
        b = List.Select(h, (x) => List.ContainsAny(Splitter.SplitTextByDelimiter(" ")(x), a)),
        c = Record.FromList(List.Repeat({"x"}, List.Count(b)), b)][c],
    columns = List.Buffer(Table.ColumnNames(Source)),
    headers = List.Buffer(List.Skip(columns, 2)),
    x_columns = 
        Table.AddColumn(
            Table.SelectColumns(Source, List.FirstN(columns, 2)),
            "rec",
            each f(Record.Field(_, columns{1}), headers)
        ),
    expand = Table.ExpandRecordColumn(x_columns, "rec", headers)
in
    expand

[Zoplie]

Thanks a lot 

[rubayatyasmin]

Hi, @Zoplie 

here is the suggestation. 

1. Start with a column where you've split the countries by ",".

2. Turn this into a list by using the 'To Table' function.

3. Expand the list so that you have a single column with all the countries.

4. In your original table, ensure that all your headers start with the name of the country, followed by the rest of the text in parentheses. This is crucial, because the next step will look for the exact country name at the beginning of the text.

5. Join this table with the original one. When doing so, join on the headers of the original table and the country name from the new table. In the join kind, select 'Left Outer' to ensure that all the original columns are kept even if they don't find a match.

6. Replace nulls with blank. This should leave an "X" where the country name matches the beginning of the header, and blank where it doesn't.

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // adjust as needed
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), // adjust as needed
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(","), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column1", Text.Trim, type text}}),
CountryTable = Table.RenameColumns(#"Trimmed Text",{{"Column1", "Country"}}),
OriginalTable = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Merged Queries" = Table.NestedJoin(OriginalTable,Table.ColumnNames(OriginalTable),CountryTable,{"Country"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Country"}, {"Country.1"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded NewColumn",null,"X",Replacer.ReplaceValue,{"Country.1"})
in
#"Replaced Value"

[Zoplie]

Hello,

it's not working :'( 

[rubayatyasmin]

Hi, @Zoplie 

Good News.

I tried with demo data and it worked. A little bit workaround, but successfully achieved the result. 

below is the ss with PQ steps that need to be followed. I am attaching the file for you. remember file will be deleted after the download. 

[rubayatyasmin]

 "Expression.Error: A join operation implies the same number and type of columns for each key."

same error?

that means, joining columns are not of the same data type. change the data type from the power query. then try again. data type change step should be added before the merging.you can change data type from to ribbon, look for data type, and then change it to match just like the desired column. make sure two column values match, and data type match. while doing that, it will ask to insert new step. click ok. 

[Zoplie]

I have a problem with one of the steps, an error message appears: "Expression.Error: A join operation implies the same number and type of columns for each key."

The "Original Table" table no longer has the same header, is this related?

[rubayatyasmin]

Yes, the error message you're seeing is related to the join operation between OriginalTable and CountryTable. The issue here is that the join operation is attempting to match the column names in the OriginalTable and the single Country column in CountryTable.

When performing a join operation in Power Query, the columns you're joining on (in this case, table column names and Country) need to have the same data type. Here, Table.ColumnNames(OriginalTable) returns a list of text values (the column names), and these are being compared to the Country column in CountryTable.

The problem arises from attempting to join a table with a single column (CountryTable) to a list of values (Table.ColumnNames(OriginalTable)).

One potential solution to this problem could be to transform Table.ColumnNames(OriginalTable) into a table with a single column before performing the join operation. Here's how you can do this:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // adjust as needed
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), // adjust as needed
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(","), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column1", Text.Trim, type text}}),
CountryTable = Table.RenameColumns(#"Trimmed Text",{{"Column1", "Country"}}),
OriginalTable = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
ColumnNameTable = Table.FromList(Table.ColumnNames(OriginalTable), Splitter.SplitByNothing(), null, null, ExtraValues.Error), // Transform list into table
#"Merged Queries" = Table.NestedJoin(ColumnNameTable,{"Column1"},CountryTable,{"Country"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Country"}, {"Country.1"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded NewColumn",null,"X",Replacer.ReplaceValue,{"Country.1"})
in
#"Replaced Value"

The line ColumnNameTable = Table.FromList(Table.ColumnNames(OriginalTable), Splitter.SplitByNothing(), null, null, ExtraValues.Error) is added to transform the list of column names into a table with a single column, which can then be joined to CountryTable.

Note: If the column names in your original table are not unique, you might still encounter issues with this solution. The Power Query join operation assumes that the key columns in both tables contain unique values.

[Zoplie]

Thank you very much for your reply, I'll see about adapting it to my table. I'll keep you posted on the resolution of my request. 🙂