cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Guillemje Frequent Visitor
Frequent Visitor

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:

    Capture 1.PNG

    Query B:

Capture 2.PNG

 

   Any ideas?

  Thanks for your help.

3 REPLIES 3
Sang Frequent Visitor
Frequent Visitor

Re: Merge two Queries - horitzontal data and vertical data

Don't you have any quantities?

Community Support Team
Community Support Team

Re: Merge two Queries - horitzontal data and vertical data

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

430.PNG

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.

Guillemje Frequent Visitor
Frequent Visitor

Re: Merge two Queries - horitzontal data and vertical data

Hi Sang,

 

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

 

Br,

G.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors