Compare value in two lists with power query

[Anonymous]

Hallo,

I have a table with two columns and I want to add a third column which should show "true" if the value of the first column exists somewhere (not only in the same row) in the second column otherwise "false". I tried to use List.Contain but this works only with values in the same row. Maybe a short example what I mean:

C1   C2  C3

A      A     True

A      B      True     because A exist in row 1 of C2

C      B       False

B      D      True     because B exists in row 2 and 3 in C2

The examples in row 1 and 3 work fine because they are in the same row. Is there a solution for row 2 and 4????

Thank you for your support

Fred

[Mariusz]

Hi @Anonymous,

Ypu can use the below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeJYHQjLCcxyhrOcgCwXpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
    tbl = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
    compaireValues = List.Distinct(tbl[Col2]),
    #"Added Custom" = Table.AddColumn(tbl, "Custom", each List.ContainsAny({[Col1]}, compaireValues))
in
    #"Added Custom"

Hope this Helps!
Mariusz

[Anonymous]

Hi Mariusz,

Thank you for your answer. There is a good and a bad message. First the good one, the code works. The bad one, the report has about 14000 rows, to load the query in excel takes about 20 minutes. My notebook is not the newest one with the fastest processor but 20 minutes is heavy. On the other hand excel compares every row with the comparerlist row by row 14000 times which is a lot but 20 Minutes for that, there might be another reason for that long runtime, any idea??

Best regards and thank you for your support

Fred

[Mariusz · Accepted solution]

Hi @Anonymous 

This should perform better, however the final output is different table structure.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSAeJYHQjLCcxyhrOcgCwXpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
    tbl = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
    col1 = List.Distinct(tbl[Col1]),
    #"Converted to Table" = Table.FromList(col1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Col1"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Col1", type text}}),
    col2 = List.Distinct(tbl[Col2]),
    #"Converted to Table1" = Table.FromList(col2, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns1" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "Col2"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Col2", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type1", {"Col2"}, #"Changed Type", {"Col1"}, "Col1", JoinKind.FullOuter),
    #"Expanded Col1" = Table.ExpandTableColumn(#"Merged Queries", "Col1", {"Col1"}, {"Col1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Col1", "Check", each [Col2]=[Col1], type logical)
in
    #"Added Custom"

Hope this helps.

Mariusz

[Greg_Deckler]

@ImkeF 

[Anonymous]

Hi Greg,

Thank you for your reply but there is no answer only a link to ImkeF. Maybe I should follow this link or something went wrong.

Fred