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.

[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...

[lbendlin]

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

[pankajnamekar25]

Hello   @helen_wrensted1 

Go 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.

 

 

If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.

Thanks,

Connect with me on:
LinkedIn |
Data With Pankaj - YouTube

@helen_wrensted1