Check if cell contents match items in a list

[rilzniak]

I've tried to utilize ChatGPT and Google Gemini but the results just aren't what I'm looking for. I'm close, but just not sure how to tweak the formula to resolve my issue.

I have two tables: T1 and T2. In T2 I have a list of "cleaned" item numbers. In T1 I have a list of item numbers with additional characters - the number of characters will vary - before and/or after the item number. I want to use PQ to look in T2 to see if any of the results match what exists in T1 and add a column with that information if there is a match. Here's an example:

The result the engines give me is:

 = List.Count(List.Intersect(Text.ToList([ItemNumber]), Text.ToList(Text.Combine(T2[CleanedItemNumber])))) > 0

This results in an error saying that the data is not in a list format. I believe the issue is related to the List.Count and List.Intersect functions but I'm not sure how to correct. Any suggestions on how I can resolve this?

 

[alex78]

Hi Rilzniak,

Try something like this,

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Result = Table.AddColumn(T1, Table.ColumnNames(T2){0}, each try List.RemoveNulls(List.Accumulate(Table.ToList(T2),{},(s,c)=> s & {if Text.Contains(Record.Field(_,Table.ColumnNames(T1){0}), c) then c else null })){0} otherwise null)
in
    Result

Regards,

 

[rilzniak]

Hi Rilzniak,

Try something like this,

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Result = Table.AddColumn(T1, Table.ColumnNames(T2){0}, each try List.RemoveNulls(List.Accumulate(Table.ToList(T2),{},(s,c)=> s & {if Text.Contains(Record.Field(_,Table.ColumnNames(T1){0}), c) then c else null })){0} otherwise null)
in
    Result

Regards,

Holy crap! Alex, you're amazing.

Is there any chance you could break down how this works?

 

[alex78]

Added a column and compared each item of column “ItemNo” with “Cleaned values” with Text.Contains function into list. Accumulate (powerfull function for looping).

List.Accumulate goes through each item in a list, in this case your table T2 – Cleaned values.
Seed value is in this case an empty list.

Try … otherwise statement allows to deal with error value.

Column names were replaced by Table.ColumnNames(X) to make query dynamic.

Hope this helps.

Regards,

 

[rilzniak]

This makes sense now looking at the formula but to do it on my own is not possible based on my current skill set. I have a favour to ask: I want to add a second column to T2 adjacent to the 'Cleaned' column and pull that into the "Desired Future" T1 table, how could I modify the code to accomplish that?

I modified the code for T2 slightly:

Table.TransformColumnTypes(Table.SelectColumns(Excel.CurrentWorkbook(){[Name="T2"]}[Content],{"Cleaned","Lookup"}),{{"Cleaned", type text}})

And here is the updated Result table code:

Table.TransformColumnTypes(Table.AddColumn(T1, Table.ColumnNames(T2){0}, each try List.RemoveNulls(List.Accumulate(Table.ToList(T2),{},(s,c)=> s & {if Text.Contains(Record.Field(_,Table.ColumnNames(T1){0}), c) then c else null })){0} otherwise null),{{"Cleaned", type text}})

I tried changing a few of the {0} statements to {0,1} and {{0},{1}} but that just generated errors. I can complete steps in PQ and make minor changes but this isn't straight-forward to me on where to adjust...

 

[alex78]

I'm not sure to fully understand your request. Could you provide a example.

 

[rilzniak]

I'm not sure to fully understand your request. Could you provide a example.

Below is what I was looking for. I could just merge queries and look it up after but thought if there was a way to do it in that one step it would be easier.

Also, if I wanted to look for the 'Cleaned' codes AND the 'Style' codes in the T1 table, how could I modify the code to accomplish that? I ask because sometimes we have have DEF1 or DEF2 in a column and it would be helpful to add logic to the current code that looked for both.

Thanks in advance.

 

[alex78]

Hi Rilzniak,

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Result = Table.ExpandRecordColumn(Table.ExpandListColumn(Table.AddColumn(T1,"Custom", each List.RemoveNulls(List.Accumulate(Table.ToRows(T2) , {}, (s,c)=> 
    s & {if Text.Contains(Record.Field(_,Table.ColumnNames(T1){0}),c{0}) then Record.FromList({c{0},c{1}}, Table.ColumnNames(T2)) else null}))), "Custom"),"Custom",Table.ColumnNames(T2))
in
    Result

Regards,

 

[rilzniak]

Hi Rilzniak,

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Result = Table.ExpandRecordColumn(Table.ExpandListColumn(Table.AddColumn(T1,"Custom", each List.RemoveNulls(List.Accumulate(Table.ToRows(T2) , {}, (s,c)=>
    s & {if Text.Contains(Record.Field(_,Table.ColumnNames(T1){0}),c{0}) then Record.FromList({c{0},c{1}}, Table.ColumnNames(T2)) else null}))), "Custom"),"Custom",Table.ColumnNames(T2))
in
    Result

Regards,

You're the best!

How did you learn all of this? I need to up my game.

 

[alex78]

Hi Rilzniak,

Better way.

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Result = Table.ExpandTableColumn(Table.AddColumn(T1, "Custom", each let x = Record.Field(_,Table.ColumnNames(T1){0}) in 
    Table.SelectRows(T2, each Text.Contains(x, Record.Field(_, Table.ColumnNames(T2){0})))), "Custom",Table.ColumnNames(T2))
in
    Result

Regards,