Is it possible to replace a row with multiple other rows from an other sheet?

[MarcoW91]

Hi all,

I am new to Power Query and I am wondering if it is possible to create some kind of formula/code/way to be able to replace a row with multiple other rows based on a value and use a value of the old row to multiple a value of the new row.

It looks like this:

WE have a product which has subcomponents

Product 1 Subcomponent 1

Subcomponent 1 is present in an other sheet and has 10 row which have several parameters which are all required.

The aim is to have the product splitted in all the subcomponents and have an indicator of the productnumber in front of those rows of the subcomponents and multiple the amount of subcomponents necessary with the material requirements per subcomponent.

Each subcomponent can be sued in several products and each product can have several subcomponents.

I currently have a Macro based solution, but I am wondering if there is a way in Power Query?

It might look like something like this:

So it is in general showing the sub components in the same list as the products with the correct numbers as we only have the sub components per piece and therefor they need to be multiplied.

Hope below table makes it more clear.

Input
Product	Sub component	PartName	Mat use per product	Total Fixed Cost per product	Variable Cost per product	Total Costs per product
123		Labour	1	1	1	2
123	456	Mat A	3	15	30	45
123	789	Mat B	5	45	45	90
456		Mat X	1	2	2	4
456		Mat Y	2	1	3	4
456		Mat Z	3	2	5	7
789		Mat X	1	4	4	8
789		Mat Y	2	5	5	10
Output
Final Product	Product	Sub component	PartName	Mat use per product	Fixed Cost per product	Variable Cost per product	Total Costs per product
123	123		Labour	1	1	1	2
123	456		Mat X	3	6	6	12
123	456		Mat Y	6	3	9	12
123	456		Mat Z	9	6	15	21
123	789		Mat X	5	20	20	40
123	789		Mat Y	10	25	25	50

[edhans]

Probably, but we need data and expected output. See below.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[MarcoW91]

Hi,

It might look like something like this:

So it is in general showing the sub components in the same list as the products with the correct numbers as we only have the sub components per piece and therefor they need to be multiplied.

Hope below table makes it more clear.

Input
Product	Sub component	PartName	Mat use per product	Total Fixed Cost per product	Variable Cost per product	Total Costs per product
123		Labour	1	1	1	2
123	456	Mat A	3	15	30	45
123	789	Mat B	5	45	45	90
456		Mat X	1	2	2	4
456		Mat Y	2	1	3	4
456		Mat Z	3	2	5	7
789		Mat X	1	4	4	8
789		Mat Y	2	5	5	10
Output
Final Product	Product	Sub component	PartName	Mat use per product	Fixed Cost per product	Variable Cost per product	Total Costs per product
123	123		Labour	1	1	1	2
123	456		Mat X	3	6	6	12
123	456		Mat Y	6	3	9	12
123	456		Mat Z	9	6	15	21
123	789		Mat X	5	20	20	40
123	789		Mat Y	10	25	25	50

[wdx223_Daniel]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRUgBin8Sk/NIiIMMQCRspxerAFJmYmgFJ38QSBUcgDRIxNAUxDMBySArNLSyhCp2AtClEGkpYGoAVQsxSgCqLgNsGwSZY1ERC5QyhdmNTEwWVM4Jaaw5WA3EOul0mUGyBRU0kkhkgbAh0dCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Sub component" = _t, PartName = _t, #"Mat use per product" = _t, #"Total Fixed Cost per product" = _t, #"Variable Cost per product" = _t, #"Total Costs per product" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mat use per product", type number}, {"Total Fixed Cost per product", type number}, {"Variable Cost per product", type number}, {"Total Costs per product", type number}}),
    Product = Table.SelectRows(#"Changed Type",each not List.Contains(#"Changed Type"[Sub component],[Product])),
    SubComponent = Table.SelectRows(#"Changed Type",each List.Contains(#"Changed Type"[Sub component],[Product])),
    Custom1 = #table({"Final Product"}&Table.ColumnNames(Source),List.Combine(Table.ToList(Product,(x)=>if x{1}=" " then {{x{0}}&x} else List.Transform(Table.ToRows(Table.SelectRows(SubComponent,each [Product]=x{1})),(y)=>{x{0}}&List.FirstN(y,3)&List.Transform(List.Skip(y,3),each _*x{3})))))
in
    Custom1

[MarcoW91]

Thank you very much,

I have a few questions

-When I add new items e.g. It doesn't seem to show up in the end result e.g. below

5657

asdasd

1

2

2

4

-When I connect it to the table from excel I only see the subcomponents showing up replacing the old item but the items without subcomponents are missing, am I doing something wrong?

let
Source = Excel.Workbook(File.Contents("C:\Users\MyName\Downloads\Test.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Product", Int64.Type}, {"Mat use per product", type number}, {"Total Fixed Cost per product", type number}, {"Variable Cost per product", type number}, {"Total Costs per product", type number}}),
Product = Table.SelectRows(#"Changed Type",each not List.Contains(#"Changed Type"[Sub component],[Product])),
SubComponent = Table.SelectRows(#"Changed Type",each List.Contains(#"Changed Type"[Sub component],[Product])),
Custom1 = #table({"Final Product"}&Table.ColumnNames(#"Changed Type"),List.Combine(Table.ToList(Product,(x)=>if x{1}=" " then {{x{0}}&x} else List.Transform(Table.ToRows(Table.SelectRows(SubComponent,each [Product]=x{1})),(y)=>{x{0}}&List.FirstN(y,3)&List.Transform(List.Skip(y,3),each _*x{3})))))
in
Custom1