How to link related items?

[mraka9]

Greeting,
I need help regarding the table below.
I have a column:
Product A i
Product B.
I want to get the result for Product C.
I need in Power Query that based on Product B, Product A is searched and all sales items are added to Product B.
Examples:
Find Mobile-12 where it is in Product A and summarize all related items in Product C, which are Mobile-24 and Mobile-50.
I need a formula, not a join of tables, because there is a large amount of data.
Thank you very much in advance

Product A	Product B	Product C
Mobile-24	Mobile-12	Mobile-24;Mobile-50
Car-13	Car-7	Car-10;Car-13
Mobile-12	Mobile-24
Car-7	Car-10
House-30	House-22
Car-7	Car-13
House-22	House-30
Mobile-12	Mobile-50

[v-xinruzhu-msft · Accepted solution]

Hi @mraka9 

You can put the following code in Advanced Editor in power query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s1PysxJ1TUyUdKBsQ2NlGJ1opWcE4t0DY2BwiCGOVgIoUAHSSNMsTlUraEBWMgjv7Q4VdfYACgKYRoZYSo1RlJqZARXamyAyz5ToEwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product A" = _t, #"Product B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product A", type text}, {"Product B", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=>Text.Combine(Table.SelectRows(#"Changed Type",(y)=>y[Product A]=x[Product B] )[Product B] ,",")       )
in
    #"Added Custom"

Output:

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mraka9]

The solution is great! I don't understand why it doesn't work in my file.
Thanks a lot in advance!

[v-xinruzhu-msft]

Hi @mraka9 

You can try the following ways:

1.Delete "each" from your code.

2.Add a blank step:

then copy the sample code to the step

= Table.AddColumn(#"Changed Type", "Custom", (x)=>Text.Combine(Table.SelectRows(#"Changed Type",(y)=>y[Product A]=x[Product B] )[Product B] ,","))

if the table name or column name is different from yours, you need to change it.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mraka9]

Unfortunately, the data is being processed for a long time, but there are no results. Anyway, I'll accept your solution as it's great, although I can't get it to work on my base. Thanks again