Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Merge two Queries - horitzontal data and vertical data

Hi,

   I hope someone can help me with following issue.

   In Query A there are the customers with the products that are buying. In Query B there are the prices per Product. I would like to merge both Queries in order to identify the Price per Product per Customer. 

   Query A:

    

    Query B:

 

   Any ideas?

  Thanks for your help.

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Don't you have any quantities?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Sang,

       

        The quantites are in Query A as several columns per product: "Quantity Product A", Quantity Product B" and so on.

       

      Br,

      G.

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi Guillemje,

    You could try below M code to acheve this goal.

    Unpivot column in Query A

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUcrTTwSSBUZ6hiDKGEwVKsXqRCs5gQQMwQJQRcZ6RkCqHCzrDJE1gmg2gkgbA6lUpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [customer = _t, p1 = _t, p2 = _t, p3 = _t, address = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer", type text}, {"p1", type text}, {"p2", type text}, {"p3", type text}, {"address", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"customer", "address"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

    Query B M code

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjDUM1TSUTI0UIrVAfOMgDwjKM8ILGcM54HkTKA8Y7CcEZxnhKTSWM8YyDMF8mIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"p description" = _t, amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"p description", type text}, {"amount", Int64.Type}})
in
    #"Changed Type"

    Then merge two tables in new table like below

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUcrTTwSSBUZ6hiDKGEwVKsXqRCs5gQQMwQJQRcZ6RkCqHCzrDJE1gmg2gkgbA6lUpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [customer = _t, p1 = _t, p2 = _t, p3 = _t, address = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"customer", type text}, {"p1", type text}, {"p2", type text}, {"p3", type text}, {"address", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"customer", "address"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

     The you will  get  result like below

    Best Regards,
    Zoe Zhi

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