Forum Discussion
Is it possible to replace a row with multiple other rows from an other sheet?
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 |
4 Replies
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.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 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
