How to Get respective row data based on a matched criteria from a different row using Power Query

Hi Anonymous ,

Please try the following steps.

1. Merge Queries:

[Member1 Id]--[Ids]

[Member2 Id]--[Ids]

2. Expand Names:

3. Add custom column:

4. Remove the [Name.1] and [Name.2] columns.

5. Steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMnQ0MFTSUYIgj9LcgpJKpVgdsLgRXNwFRdwYLu6VmJwNEzVBiGbm5EBFTQ2AfKgVMBMhMmZQGWMIBdYdGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Ids = _t, #"Member1 Id" = _t, #"Member2 Id" = _t, Names = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Ids", type text}, {"Member1 Id", type text}, {"Member2 Id", type text}, {"Names", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Member1 Id"}, #"Changed Type", {"Ids"}, "Changed Type", JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries", {"Member2 Id"}, #"Merged Queries", {"Ids"}, "Merged Queries", JoinKind.LeftOuter),
    #"Expanded Changed Type" = Table.ExpandTableColumn(#"Merged Queries1", "Changed Type", {"Names"}, {"Names.1"}),
    #"Expanded Merged Queries" = Table.ExpandTableColumn(#"Expanded Changed Type", "Merged Queries", {"Names"}, {"Names.2"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Merged Queries", "Custom", each [Names.1] & "/" & [Names.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Names.1", "Names.2"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Ids", Order.Ascending}})
in
    #"Sorted Rows"

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum