Matrix table not showing all the values between two tables

[rkaushik]

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 Unique ID
1 A	1	01/11/2022	A	500	4	4 A
1 B	1	01/11/2022	B	100	4	4 B
1 C	1	01/11/2022	C	300	4	4 C
2 A	2	9/15/2021	A	500	5	5 A
2 B	2	9/15/2021	B	100	5	5 B
3 D	3	4/28/2022	D	700

Unique Id is just a concatenation of Order and Products.

Prev Orders Dataset:

UniqueID	Order	Close Date	Products	Amount
4 A	4	01/11/2021	A	600
4 B	4	01/11/2021	B	200
4 C	4	01/11/2021	C	500
4 D	4	01/11/2021	D	600
5 A	5	9/15/2020	A	200
5 B	5	9/15/2020	B	100
6 E	6	4/28/2021	E	200
6 F	6	4/28/2021	F	100

The relationship is based on column "UniqueID" from table 2 to "Prev Order Unique ID" from table 1. 

Here's what I am currently getting when creating a table:

Order	Table1.Products	Table1.Amount	Table2.Order	Table2.Products	Table2.Amount
1	A	$500	4	A	$600
1	B	$100	4	B	$200
1	C	$300	4	C	$500
2	A	$500	5	A	$200
2	B	$100	5	B	$100
3	D	$700

What I need to get:

Order	Table1.Products	Table1.Amount	Table2.Order	Table2.Products	Table2.Amount
1	A	$500	4	A	$600
1	B	$100	4	B	$200
1	C	$300	4	C	$500
1			4	D	$600
2	A	$500	5	A	$200
2	B	$100	5	B	$100
3	D	$700
			6	F	$100
			6	E	$200

Could anyone help me out with what I am doing wrong here or what can I do to achieve the desired result? Any help would be much appreciated.
Please let me know if I can provide any more information. 

Thanks in advance

[lbendlin]

Please 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-...
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/1447...

[rkaushik]

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 Products

what made you choose that?  Generally you want to separate order header information from order detail information.

How is order 3 associated to order 6 ?

[rkaushik]

@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"