Forum Discussion
Matrix table not showing all the values between two tables
Super UserPlease provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi lbendlin , thanks for pointing that out. I have added the sample data into my post and also posted how the result table should look like.
- lbendlin
Unique Id is just a concatenation of Order and Productswhat made you choose that? Generally you want to separate order header information from order detail information.
How is order 3 associated to order 6 ?
lbendlin , I chose to go with the concanetation is because without it, the order with its product wasn't aligned with the previous order and its product
Without it, I was getting this:Table1.Order Table1.Products Table2.Order Table2.Products 1 A 4 C 1 B 4 A 1 C 4 B 1 4 D The idea here is that order 4 was ordered in 2021 and order 1 (it is order 4 which got renewed for next year) was ordered in 2022. I want to see what products were involved and what was the difference between the two orders. As you can see from the results table, the difference between 1A and 4A is a loss of $100.
So to keep them in the same row, I had to find a way to make Power BI understand that order with product is related. Does that answer your question?Regarding order 3 and 6, that was my bad. They are not related. I have fixed that in the post. Sorry about that.
Thanks again for looking into this- lbendlin
You can achieve that with a simple full outer join as the join type.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1BCsAgDAS/Ujx70JhUeqz2F+L/v9FsFGqFLGGTgWnNxeN23kUNs2TSjS4h4ILov3twZeOsL1yZXN049LRw1TgyLxnHwj+vINNL5l25zzu44U3HA49xZ750o2fjML2/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UniqueID = _t, Order = _t, #"Sign Date" = _t, Products = _t, Amount = _t, #"Prev Order" = _t, #"Prev Order Unique ID " = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"UniqueID", type text}, {"Order", Int64.Type}, {"Sign Date", Int64.Type}, {"Products", type text}, {"Amount", Int64.Type}, {"Prev Order", Int64.Type}, {"Prev Order Unique ID ", type text}}), Source2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc49CsAgDAXgq4izkB9U2rFWvYR4/2vUFBWHDBny+HhJa9abxzrrxyABETAyjUXCiGi7E5I0IiFv8mpEwrBJ1kg+DoX/lzDmBgoicL7CWyRFSEZTRFOkUU4BX+tKOTqiqYqoq6N/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UniqueID = _t, Order = _t, #"Close Date" = _t, Products = _t, Amount = _t]), #"Changed Type2" = Table.TransformColumnTypes(Source2,{{"UniqueID", type text}, {"Order", Int64.Type}, {"Close Date", type date}, {"Products", type text}, {"Amount", Int64.Type}}), #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Prev Order Unique ID "}, #"Changed Type2", {"UniqueID"}, "PrevOrders", JoinKind.FullOuter), #"Expanded PrevOrders" = Table.ExpandTableColumn(#"Merged Queries", "PrevOrders", {"UniqueID", "Order", "Close Date", "Products", "Amount"}, {"PrevOrders.UniqueID", "PrevOrders.Order", "PrevOrders.Close Date", "PrevOrders.Products", "PrevOrders.Amount"}) in #"Expanded PrevOrders"