Get Parts List For Shipped Equipment

[UncleLewis]

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

[UncleLewis · Accepted solution]

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"

[Kishore_Kadhir]

Hi,

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

Regards,

Kishore