Forum Discussion
Create new table
Hi.
I have a table with bill of materials for all our products, below example of 2 products:
Looking at main number 5663, it includes raw materials and another BOM, Sub number 5664, marked assembly.
I woulk like to create a new table, where I get all the subnumbers for 5663, which means that the yellow fields should change from 5664 to 5663 and the blue cell should be left out as we include the details (yellow) instead.
How can I solve this?
Thanks a lot in advance.
Hi helen_wrensted1,
We are following up to inquire whether your issue has been resolved. If it has been resolved, we would be grateful if you could share the solution with the community, as it may help others facing similar challenges.
However, if the issue remains unresolved and you have any further queries related to it, please share either a simplified or a sanitized PBIX file. Alternatively, you may provide the data model diagram from the Relationships view, along with a small sample dataset containing 2 to 3 rows for all tables.
Please ensure the data is relevant and clearly demonstrates your issue or query in a structured format (not as an image), and that it is free from any sensitive information. Also, please include the expected outcome based on the provided data.
Thank you.
22 Replies
Hello helen_wrensted1Go to Power Query and duplicate your BOM table. Then perform a merge by matching the Sub Number with the Main Number to establish the parent child relationship. After merging, expand the table to bring in the child components. Next, replace the parent value such as 5664 with the top level parent 5663, remove the intermediate assembly rows, and finally append the results back to get the flattened BOM structure.
This is worth reading: Parent-Child Hierarchies with multiple parents in Power BI with Power Query
Thanks a lot - very useful!
you can create a column
Column =var _num=maxx(FILTER('Table','Table'[Sub number]=EARLIER('Table'[Main number])),'Table'[Main number])return if(ISBLANK(_num),'Table'[Main number],_num)Hi ryan_mayu
Thanks a lot for your suggestion.
Unfortunately, the column doensn't work as Main number 5664 is included in multiple other main numbers and not only 5663. It will probably be necessary to create a new table, where there will be more rows than the original, as the assembly rows will be replaced by the Sub number rows. In this case one row will be replaced with the 3 rows for main number 5664.
then pls update the sample data and expected output. I think you need to consider all the possible scenarios in your sample data. Otherwise, the solution we provided can solve your current issue, but can't can apply to your real data.
Thankyou, pankajnamekar25, lbendlin and ryan_mayu for your responses.
Hi helen_wrensted1,
We appreciate your inquiry through the Microsoft Fabric Community Forum.
We would like to inquire whether have you got the chance to check the solutions provided by pankajnamekar25, lbendlin and ryan_mayu to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.
Thank you.Hi helen_wrensted1,
We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.
Thank you.I am still working on it 🙂 So far it is not solved, but I think I'll get there soon....
Thanks
Hi helen_wrensted1,
Thank you for the update. Should you have any further queries, please feel free to contact the Microsoft Fabric community.Thank you.
Hi helen_wrensted1,
We are following up to inquire whether your issue has been resolved. If it has been resolved, we would be grateful if you could share the solution with the community, as it may help others facing similar challenges.
However, if the issue remains unresolved and you have any further queries related to it, please share either a simplified or a sanitized PBIX file. Alternatively, you may provide the data model diagram from the Relationships view, along with a small sample dataset containing 2 to 3 rows for all tables.
Please ensure the data is relevant and clearly demonstrates your issue or query in a structured format (not as an image), and that it is free from any sensitive information. Also, please include the expected outcome based on the provided data.
Thank you.
Hi helen_wrensted1,
We are checking in to see if your issue has been resolved. If so, we would appreciate it if you could share the solution with the community. Your input could help others facing the same problem.
If the issue is still open and you have further questions, please share a simplified or sanitized PBIX file. Alternatively, you can provide the data model diagram from the Relationships view along with a small sample dataset of 2 to 3 rows per table. Make sure the data is relevant, clearly structured (not as an image), and free from sensitive information. Also, include the expected outcome based on the sample data you provide.
Thank you.
