cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
UncleLewis
Super User
Super User

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
Super User
Super User

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
Super User
Super User

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.