Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
rkaushik
Frequent Visitor

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:

UniqueIDOrderSign DateProductsAmountPrev OrderPrev Order Unique ID 

1 A

1

01/11/2022A

500

44 A
1 B101/11/2022B10044 B

1 C

1

01/11/2022C30044 C

2 A

2

9/15/2021A50055 A

2 B

2

9/15/2021B10055 B

3 D

3

4/28/2022D700  

Unique Id is just a concatenation of Order and Products.

 

Prev Orders Dataset:

UniqueIDOrderClose DateProductsAmount
4 A401/11/2021

A

600
4 B401/11/2021B200
4 C401/11/2021C500
4 D401/11/2021D600
5 A 59/15/2020A200
5 B59/15/2020B100
6 E64/28/2021E

200

6 F64/28/2021F100

 

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:

OrderTable1.ProductsTable1.AmountTable2.OrderTable2.ProductsTable2.Amount
1A$5004A$600
1B$1004B$200
1C$3004C$500
2A$5005A$200
2B$1005B$100
3D$700   

 

What I need to get:

OrderTable1.ProductsTable1.AmountTable2.OrderTable2.ProductsTable2.Amount
1A$5004A$600
1B$1004B$200
1C$3004C$500
1  4D$600
2A$5005A$200
2B$1005B$100
3D$700   
   6F$100
   6E$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

5 REPLIES 5
lbendlin
Super User
Super User

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...

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.

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 ?

@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

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"

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.