Merge two Queries - horitzontal data and vertical data

[Guillemje]

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.

[dax]

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.

[Anonymous]

Don't you have any quantities?

[Guillemje]

Hi Sang,

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

Br,

G.