Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

HELP: Matching a value in one column to a value from another column (but different row)

Hi,

 

I'm relatively new to using Power Query, and I just can't figure out how to do what I can do with DAX in Power Query.

I'm trying to get a new column that returns True if the value in a row of one column matches with any of the rows in another column, and a False if it does not match. 

 

In DAX I used this function: CONTAINS(Table1,Table1[column1],Table1[column2]) and this worked fine. However, I want to be able to use that column for further transformations or conditions in the Query Editor, so I need to replicate this using Power Query, but I can't find any examples that do this. 

 

It should look something like this:

So if the values from either the Other_PersonID1 or the values from the Other_PersonID2 match any case in the column Person_ID, then you return "True", if there is no match, then it returns "False".

Person_IDOther_PersonID1Other_PersonID2Match
1 2True
27 True
3 20False
48 True
5 4True
69 True
7 9True
8  False
9  False

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    Add custom columns

    List.Contains(#"Removed Columns"[Person_ID],[Other_PersonID1]) or List.Contains(#"Removed Columns"[Person_ID],[Other_PersonID2])

    Replace #"Removed Columns" with the previous step in your case.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    This code will handle looking up mutliple columns in one merge.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NYnJDQAgCAR72TcPb6UWYv9tyCEJITM7Iqgg6DVcEv2EbYNJ/6W4DcWTaUYaLkuJs+wo7HJCnDn5Pg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Person_ID = _t, Other_PersonID1 = _t, Other_PersonID2 = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Person_ID"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Value"}, #"Unpivoted Other Columns", {"Person_ID"}, "Unpivoted Other Columns", JoinKind.LeftOuter),
    Transform = Table.TransformColumns(#"Merged Queries",{{"Unpivoted Other Columns", each not List.IsEmpty([Value])}}),
    #"Grouped Rows" = Table.Group(Transform, {"Person_ID"}, {{"Match", each 
List.AnyTrue([#"Unpivoted Other Columns"])}})
in
    #"Grouped Rows"

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    Add custom columns

    List.Contains(#"Removed Columns"[Person_ID],[Other_PersonID1]) or List.Contains(#"Removed Columns"[Person_ID],[Other_PersonID2])

    Replace #"Removed Columns" with the previous step in your case.

     

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you very much, this works perfectly!

    • BenDC's avatar
      BenDC
      Regular Visitor
      4 years ago

      Hello,

       

      Thank you for this quick and detailed response.

      I am struggling to apply this logic to my scenario.

      I've implemented the following steps so I can get the same comparison as this scenario:

       

      Created a new custom column:
      = Table.AddColumn(#"Previous Step", "TrainingID", each if [Training] = true then [OrderID] else null)

       

      Applied your logic to an additional custom column:

      = Table.AddColumn(#"PreviousStep", "Training Order", each List.Contains(#"PreviousStep"[OrderID],[TrainingID])).

       

      I was hoping this would check if the OrderID in each row existed somewhere in the TrainingID column, but it isn't doing this.


      Any idea on where I am going wrong?