Create new table

[helen_wrensted1]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[helen_wrensted1]

Hi @v-pnaroju-msft 

I am still working on it 🙂 So far it is not solved, but I think I'll get there soon....

Thanks

[v-pnaroju-msft]

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.

[ryan_mayu]

@helen_wrensted1 

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)

[helen_wrensted1]

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.

[ryan_mayu]

@helen_wrensted1 

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.

[helen_wrensted1]

Hi @ryan_mayu 

Can you open enclosed file?

BOM test 

In the simple table, you can see main number 5663 includes another BOM 5664, details in second table. In the first table I would like the details from table 2 to show instead of the row with sub number 5664.

Thanks a lot in advance.

[ryan_mayu]

@helen_wrensted1 

I can only see one table in your pbix file, the second table you mean is table visual?

maybe you can create an expected column at then end

[helen_wrensted1]

@ryan_mayu 

Sorry, I wasn't very clear. I was referring to the tables in Report view - there is only one data table.

[ryan_mayu]

This is the output that used the DAX I proviced

If this is not correct. Then what's the expected output? Please also provide the corresponding logics

[helen_wrensted1]

For Main number 5663, I would like below output: 

The Assemply row should be deleted as I have all the details in the blue rows instead. And the blue rows should have Main number 5663 instead of 5664. Does it make any sense?

[ryan_mayu]

@helen_wrensted1 

the first one row does not display.

in the visual filter, unselect assembly.

for the 2nd question. why replace last 6 rows to 5664. The rest rows may have the same name ,sub num ,etc. What's the logic to pick out these 6 rows? could you pls clarify?

[helen_wrensted1]

@ryan_mayu 

My wish is to have a full list (and costs) of main number 5663.

5663 contains raw materials and also a bill of material 5664. I would like to see the components of the BOM for 5664, so I have a list of all raw materials included in 5663 and they should som up to 3.814,81

[ryan_mayu]

@helen_wrensted1 

you can create a column

Column =

var _num=maxx(FILTER('BOM details new','BOM details new'[Sub number]=EARLIER('BOM details new'[Main number])),'BOM details new'[Main number])

return if(ISBLANK(_num),'BOM details new'[Main number],_num)

select 5663 in the column that we created and unselect assembly

pls see the attachment below

[helen_wrensted1]

@ryan_mayu 

Thanks a lot for your effort - it is highly appreciated. 

It works great for number 5663, but BOM 5664 is also included in 5661 and 5662. In order to allocate the 5664 expenses to all relevant main numbers, I think it will be necessary to create a new table with added rows...

[ryan_mayu]

@helen_wrensted1 

Now I totally understand your requirement.

You can do the data transfer and create the new table in Power query

pls see the attachment below

[lbendlin]

This is worth reading: Parent-Child Hierarchies with multiple parents in Power BI with Power Query

[helen_wrensted1]

@lbendlin 

Thanks a lot - very useful!