Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Compare value in two lists with power query

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

Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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

5 Replies

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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