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?

 

[rilzniak]

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,

Thanks. I'm curious, aside from the code being shorter, what makes this a better way of writing the statements?

Also, my plans for the file design have changed and I'm looking to incorporate these statements into an already-existing table. I've tried to append it to the end of what I've already got, but I believe I'm erring regarding the column name references. Here is the existing:

let
    Source = Excel.Workbook(File.Contents(fPur), null, true),
    report1710258494169_Sheet = Source{[Item="report1710258494169",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(report1710258494169_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"C1", type text}, {"C2", type text}, {"C3", Int64.Type}, {"C4", type date}, {"C5", type text}, {"C6", type number}, {"C7", type number}, {"C8", type number}, {"C9", type text}, {"C10", type date}, {"C11", type text}})
in
    #"Changed Type"

And I'd like to modify what you had written and append it to the bottom.

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

T1 is the existing table and T2 is the table containing the 'Cleaned' and 'Style' data. My guess is that I need to modify

Table.ColumnNames(T1){0}

and

Table.ColumnNames(T2){0}

but I haven't had any luck with my attempts. Suggestions?

 

[alex78]

Hi Roczniak,

In my example, T1 and T2 are query steps - not the tables name (Table1 and Table2).

You only have to adjust names accordingly.

Regards,