Forum Discussion
how to create a batch tree
I have a table with multiple lines with the following info:
Line no. | Product | Batch | Material 1 | Batch 1 | etc
123 A B20 XD B80
143 A B21 XD B82
163 A B22 XD B82
456 XD B80 ED C57
And I would like to create a batch tree. i.e. I want a table that is filtered by product/batch. If I select product A and batch B20 , it appears the lines 123 and 456.
the line 123 is the batch A - B20 manufactured and the line 456 is the line of batch XD -B80 that it was consumed to produce batch A - B20.
it is that possible ?
Hi jps_HHH
Thanks for reaching out ot the fabric community.
Please do follow the below steps to resolve the issue.- Unpivot your material and batch columns into a parent-child relations table using:
Table.UnpivotOtherColumns(
RawBatches,
{"Line no.","Product","Batch"},
"MaterialProduct",
"MaterialBatch"
)
``` :contentReference[oaicite:0]{index=0} - Add ParentKey = [Product] & "|" & [Batch] and ChildKey = [MaterialProduct] & "|" & [MaterialBatch] columns to the relations table.
- Create a blank query BatchTree and paste in an M script that:
-
Starts with a list {SelectedBatch}
- Uses List.Generate to repeatedly find rows where ParentKey is in the current list, collect their ChildKeys, and stop when no new keys appear https://learn.microsoft.com/en-us/powerquery-m/list-generate
-
- Filter the relations table to only rows whose ParentKey is in the final expanded key list.
- Merge those filtered rows back to RawBatches on the matching key to retrieve full row details.
- Expose SelectedBatch as a slicer (via a disconnected parameter table or What-If parameter) so users pick e.g. A|B20.
- Bind your visual (table or matrix) to the BatchTree query—selecting a batch will now show it plus all downstream batches (XD|B80 → ED|C57 → CV|D90) at any depth.
If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.- Unpivot your material and batch columns into a parent-child relations table using:
6 Replies
Hello jps_HHH
Use this DAX to create a calculated table
BatchTree =
UNION (
SELECTCOLUMNS(MainTable, "ParentLine", [Line no.], "ChildLine", [Line no.]),
SELECTCOLUMNS(
FILTER(
CROSSJOIN(MainTable, MainTable),
MainTable[Material 1] = MainTable_1[Product]
&& MainTable[Batch 1] = MainTable_1[Batch]
),
"ParentLine", MainTable_1[Line no.],
"ChildLine", MainTable[Line no.]
)
)
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
thanks for your reply.
My batch tree could have 4 or 5 products.
Example:
Product A batch B20 is manufactured used Product XD, batch B80.
XD.B80 is manufactured used Product ED.C57.
ED.C57 is manufactured used Product CV.D90.
I would like to select product A and batch B20 and the system automatically filters
XD.B80
ED.C57
CV.D90
Hi jps_HHH
Thanks for reaching out ot the fabric community.
Please do follow the below steps to resolve the issue.- Unpivot your material and batch columns into a parent-child relations table using:
Table.UnpivotOtherColumns(
RawBatches,
{"Line no.","Product","Batch"},
"MaterialProduct",
"MaterialBatch"
)
``` :contentReference[oaicite:0]{index=0} - Add ParentKey = [Product] & "|" & [Batch] and ChildKey = [MaterialProduct] & "|" & [MaterialBatch] columns to the relations table.
- Create a blank query BatchTree and paste in an M script that:
-
Starts with a list {SelectedBatch}
- Uses List.Generate to repeatedly find rows where ParentKey is in the current list, collect their ChildKeys, and stop when no new keys appear https://learn.microsoft.com/en-us/powerquery-m/list-generate
-
- Filter the relations table to only rows whose ParentKey is in the final expanded key list.
- Merge those filtered rows back to RawBatches on the matching key to retrieve full row details.
- Expose SelectedBatch as a slicer (via a disconnected parameter table or What-If parameter) so users pick e.g. A|B20.
- Bind your visual (table or matrix) to the BatchTree query—selecting a batch will now show it plus all downstream batches (XD|B80 → ED|C57 → CV|D90) at any depth.
If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.- Unpivot your material and batch columns into a parent-child relations table using:
Hi jps_HHH
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!Best Regards,
Community Support Team _ C Srikanth.Hi jps_HHH
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!Best Regards,
Community Support Team _ C Srikanth.Hi jps_HHH
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.
