Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Flo_Rian_23
New Member

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

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!!!

 

1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

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

jgeddes_0-1716490529801.png

Unpivot

jgeddes_1-1716490551236.png

Replace Values 

 

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

 

jgeddes_2-1716490599543.png

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")

 

jgeddes_3-1716490656238.png

Hope this helps.

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

3 REPLIES 3
slorin
Super User
Super User

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
Super User
Super User

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

jgeddes_0-1716490529801.png

Unpivot

jgeddes_1-1716490551236.png

Replace Values 

 

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

 

jgeddes_2-1716490599543.png

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")

 

jgeddes_3-1716490656238.png

Hope this helps.

 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Thank you, that worked like a charm!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors