Multiply the column A with each of the 600 following columns, creating 600 new columns

[Flo_Rian_23]

Help please,

I have an Excel Table with 1000 prices (rows) in column A, which need to be multiplied with the quanities (per day) on the ca. 600 columns from B to XC. Since these are 600.000 multiplications, slowing Excel badly and sensitive to the smalest insertion or error, I want to switch to Query.

Is there a way of creating 600 new columns with an Excel Query at once, the first one A*B, then A*C, then A*D....  until all 600 columns are multiplied with column A?

Thanks!!!

[slorin]

Hi @Flo_Rian_23 

Power Query, one step with Table.ReplaceValue

= Table.ReplaceValue(
  Your_Source, 
  each [Price],
  null,
  (value, price, z) => value*price,
  List.Skip(Table.ColumnNames(Your_Source))
)

Excel

You copy the data from column A (up to row 1000)

You select the data from column B to column CX (also up to row 1000)

Paste Special, in value and multiplication

Stéphane

[jgeddes · Accepted solution]

You can take a copy of the imported table, unpivot the non-price columns, replace the quantity values with the quantity * price and then pivot the column back to the original format. 
A small example would look like...
Source

Unpivot

Replace Values 

Table.ReplaceValue(#"Unpivoted Other Columns",each [Value], each [Value] * [Price],Replacer.ReplaceValue,{"Value"})

Pivot Column (I changed the Value Column to number type prior to this step.)

= Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute]), "Attribute", "Value")

Hope this helps.

[Flo_Rian_23]

Thank you, that worked like a charm!