I'm a noob and need help with a Power Query project. I have a merged query from two different tables: first table contains a bill of materials including Item Number and Usage Quantity; second table contains a weekly sales plan. I want to multiply all of the weekly sales columns by the Usage Quantity.
My attempt at a solution was:
= Table.TransformColumns(#"Removed Columns", {}, each _ * {"Usage Quantity"})
What I believe is happening is all of the columns, including Item Number and Usage Quantity, are being multiplied by Usage Quantity and it is resulting in errors.
| Item Number | Usage Quantity | 10/16/2023 | 10/23/2023 | .... |
|---|---|---|---|---|
| ABC123 | 1 | 10 | 20 | |
| XYZ456 | 0.5 | 5 | 7 |
Try this to multiply all fields by the Usage Quantity column
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Names = List.RemoveFirstN(Table.ColumnNames(#"Source"),2), // skip first two columns from the transform
Transform = Table.FromRecords(Table.TransformRows(Source, (x)=>Record.TransformFields(x, List.Transform( Names, (y) => { y, each _ * x[Usage Quantity] } ) ) ))
in Transform