Forum Discussion

mraka9's avatar
mraka9
Icon for Helper III rankHelper III
3 years ago
Solved

How to link related items?

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 AProduct BProduct C
Mobile-24Mobile-12Mobile-24;Mobile-50
Car-13Car-7Car-10;Car-13
Mobile-12Mobile-24 
Car-7Car-10 
House-30House-22 
Car-7Car-13 
House-22House-30 
Mobile-12Mobile-50 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      mraka9
      Icon for Helper III rankHelper III
      3 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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.