Forum Discussion

rkaushik's avatar
rkaushik
Frequent Visitor
3 years ago

Matrix table not showing all the values between two tables

Hi everyone, I have been trying to create a table from two datasets Orders and Prev Orders.  Orders dataset looks like this: UniqueID Order Sign Date Products Amount Prev Order Prev Order...
Need Help
rkaushik's avatar
rkaushik
Frequent Visitor
3 years ago

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.OrderTable1.ProductsTable2.OrderTable2.Products
1A4C
1B4A
1C4B
1 4D

 

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's avatar
lbendlin
Icon for Super User rankSuper User
3 years ago

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"