cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
UncleLewis
Helper V
Helper V

Get Parts List For Shipped Equipment

Hi all,

Using PBIRS, Sept, 2021.

I am trying to generate a list of needed parts for orders that have been shipped.

The shipping table has a list of modules that have been shipped and are en route.
There may be more than 1 of the same module that has been shipped.


I created a dim table with unique module_id's and module_names
I created relationships to the other 2 tables so I have 2 1:M relationships

I added a Table Visual to the Report Canvas and added the Shipped[Module_id]
I tried to add the ListOfParts[Part_id] but I get a "Fix this" error.

How can I get a list of parts for each of the en-route modules?
I cannot create M:M bi-directional relationship.

The pbix file is available here.

Thanks,

-w





A module has a list of parts. This is given in ListOfParts Table.

The 2 tables contain mutliple instances

1 ACCEPTED SOLUTION
UncleLewis
Helper V
Helper V

I was either overthinking it or not thinking about it clearly.
In the end, it was a simple merge query.

For module 10, I had 3 en-route and each module has 3 parts so I expecteded 9 rows in the merge query, which is exactly what I have.

let
Source = Table.NestedJoin(Shipped, {"Module_id"}, ListOfParts, {"Module_id"}, "ListOfParts", JoinKind.LeftOuter),
#"Expanded ListOfParts" = Table.ExpandTableColumn(Source, "ListOfParts", {"Part_id", "Part_Name"}, {"Part_id", "Part_Name"})
in
#"Expanded ListOfParts"



modules_parts.png

View solution in original post

2 REPLIES 2
UncleLewis
Helper V
Helper V

I was either overthinking it or not thinking about it clearly.
In the end, it was a simple merge query.

For module 10, I had 3 en-route and each module has 3 parts so I expecteded 9 rows in the merge query, which is exactly what I have.

let
Source = Table.NestedJoin(Shipped, {"Module_id"}, ListOfParts, {"Module_id"}, "ListOfParts", JoinKind.LeftOuter),
#"Expanded ListOfParts" = Table.ExpandTableColumn(Source, "ListOfParts", {"Part_id", "Part_Name"}, {"Part_id", "Part_Name"})
in
#"Expanded ListOfParts"



modules_parts.png

Kishore_Kadhir
Resolver II
Resolver II

Hi,

 

Can you create a measure like this, drag it to the table and see if it works?

Kishore_Kadhir_0-1657324645133.png

Regards,

Kishore

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors