Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
Don't you have any quantities?
Hi Sang,
The quantites are in Query A as several columns per product: "Quantity Product A", Quantity Product B" and so on.
Br,
G.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |