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

[Priya_N999]

Hi All, I am fairly new to Power Query platform and am looking for a way to get data displayed in a cell based on matched criteria . The belwo table will give a bettr insight on my requirements. 

Available datas	Expected Result - Get all members names displayed based on the  data in member1 Id and Member2 Id
Ids	Member1 Id	Member2 Id	Names
1A01			Humpty
1A02			Dumpty
1A03			Jack
1A04			Jill
1A50	1A01	1A02		Humpty/Dumpty
1A60	1A03	1A04		Jack/Jill

[Manoj_Nair]

@Priya_N999 - Try this PBIX file. Power BI file 

Please don't hesitate to give kudos to useful posts and mark solution as accepted. Many Thanks 

[pratyashasamal]

Hi @Priya_N999 ,
Can you share a clear raw dataset and how the desired dataset show look like .
Thanks ,
Pratyasha Samal.

[Priya_N999]

The table seems to have got mis-allginde in my original query. I hope the below helps.

[Anonymous · Accepted solution]

Hi @Priya_N999 ,

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

[Priya_N999]

This helped. Thank you so much for  your time in understanding my requirement and providing the step by step solution along with the screenshots. Appreciate it.

Thank you everyone else who took time to look into this query and help me find a solution.

[Manoj_Nair]

@Priya_N999 - have you checked the attached PBIX  file? your misalignment is already considered in the power query and the result shown as desired. 

 

 Desired Output 

[Priya_N999]

*mis-alligned