Multiply multiple Power Query columns by single column to create new table

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
I am pulling data from an external .xls file that has a table with a date column, 200 columns with individual asset production data, and a single commodity strip column.

The dates, assets and commodity strip data is all aligned based on the time-stamp in the date column.

I need to create a new Power Query table that multiples the 200x columns of prodution data by the commodity strip data (i.e. multiply 200x column data by 1x commodity column data).

How do you do this?

Thanks
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
And to be clear, I have the original query from Power Query set up as a connection only at the moment (i.e. I am not exporting the data to Excel). So I am looking to taking the data that is in that original query result (i.e. from the Power Query table itself) and create the second Power Query table I noted above.

Thanks
 
Upvote 0
I created a 10 Row, 200 Column table using RANDARRAY between 1 and 9.
Excel Formula:
=RANDARRAY(10,200,1,9,1)
This is the result in both Power Query (just select all 200 columns and under Add Column select Multiply) and using the PRODUCT function:
Book1
GT
26Multiplication
2711,904,175,305,732,800,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
287,247,988,917,184,550,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
2910,520,173,585,179,600,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
301,020,824,492,640,860,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
312,200,659,692,056,450,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
3243,315,584,718,747,200,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
33571,899,908,260,496,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
34961,844,442,073,566,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
3532,914,000,667,678,600,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
36235,819,716,514,544,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
Sheet1

Those numbers came in as scientific notation, and formatted the numbers. Using 90 as the top value results in numbers too big for Power Query or Excel.
Here's the Power Query if you really want it:
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    InsertedMultiplication = Table.AddColumn(Source, "Multiplication", each List.Product({[Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [Col10], [Col11], [Col12], [Col13], [Col14], [Col15], [Col16], [Col17], [Col18], [Col19], [Col20], [Col21], [Col22], [Col23], [Col24], [Col25], [Col26], [Col27], [Col28], [Col29], [Col30], [Col31], [Col32], [Col33], [Col34], [Col35], [Col36], [Col37], [Col38], [Col39], [Col40], [Col41], [Col42], [Col43], [Col44], [Col45], [Col46], [Col47], [Col48], [Col49], [Col50], [Col51], [Col52], [Col53], [Col54], [Col55], [Col56], [Col57], [Col58], [Col59], [Col60], [Col61], [Col62], [Col63], [Col64], [Col65], [Col66], [Col67], [Col68], [Col69], [Col70], [Col71], [Col72], [Col73], [Col74], [Col75], [Col76], [Col77], [Col78], [Col79], [Col80], [Col81], [Col82], [Col83], [Col84], [Col85], [Col86], [Col87], [Col88], [Col89], [Col90], [Col91], [Col92], [Col93], [Col94], [Col95], [Col96], [Col97], [Col98], [Col99], [Col100], [Col101], [Col102], [Col103], [Col104], [Col105], [Col106], [Col107], [Col108], [Col109], [Col110], [Col111], [Col112], [Col113], [Col114], [Col115], [Col116], [Col117], [Col118], [Col119], [Col120], [Col121], [Col122], [Col123], [Col124], [Col125], [Col126], [Col127], [Col128], [Col129], [Col130], [Col131], [Col132], [Col133], [Col134], [Col135], [Col136], [Col137], [Col138], [Col139], [Col140], [Col141], [Col142], [Col143], [Col144], [Col145], [Col146], [Col147], [Col148], [Col149], [Col150], [Col151], [Col152], [Col153], [Col154], [Col155], [Col156], [Col157], [Col158], [Col159], [Col160], [Col161], [Col162], [Col163], [Col164], [Col165], [Col166], [Col167], [Col168], [Col169], [Col170], [Col171], [Col172], [Col173], [Col174], [Col175], [Col176], [Col177], [Col178], [Col179], [Col180], [Col181], [Col182], [Col183], [Col184], [Col185], [Col186], [Col187], [Col188], [Col189], [Col190], [Col191], [Col192], [Col193], [Col194], [Col195], [Col196], [Col197], [Col198], [Col199], [Col200]}), type number)
in
    InsertedMultiplication
 
Upvote 0
Solution
Ok thanks. So let me provide more detail.

I have a query that pulls a time-series of production data across mutlple asset into Power Query. It is composed of date time data in Column 1, production data by asset in columns 2-195, and a commodity strip in column 196. All production data and commodity data are aligned as per the date time data in column 1. The production data is type Decimal and the commodity strip is type Currency.

So in essence I am looking to multiply all the production data from columns 2-195 with the commodity strip in column 196.

I am using 2x Queries for this:
  1. My first query (Query1) pulls the production data and commodity strip from Excel into Power Query into a table as a Connection.
  2. My second query (Query2) is a "reference" to Query 1 also as a Connection and does the column multiplication to create a revenue table by asset

Below is the M Code for Query 2 with the error being generated. This results in "Error" values in all the table elements in Query2. Note the Error reference in Column 196 appears to be referring to the commodity strip column.

What am I doing wrong here?

Expression.Error: We cannot apply field access to the type Number.
Details:
Value=5.236
Key=Column196

**********************************
Power Query:
let
    Source = Query1,
    ColumnNames = Table.ColumnNames(Source),
    MultiplyCols = List.Transform(
        List.Skip(ColumnNames,1),
        each if _ <> "Column196" then {_ , each _ * [Column196]} else null),
    #"Multiplied Columns" = Table.TransformColumns (Source, MultiplyCols)
in
    #"Multiplied Columns"
 
Upvote 0
Assuming Column196 is the last column in the table

Power Query:
let
    rows = Table.TransformRows(Query1, (x)=> 
        let 
            lst = Record.ToList(x),
            m = List.Last(lst),
            lst1 = List.Range(lst, 1, List.Count(lst)-2 ),
            lst2 = List.Transform(lst1, each _*m)
        in   
            {lst{0}} & lst2 & {m}),
    Result = Table.FromRows(rows, Table.ColumnNames(Query1))
in
    Result
 
Upvote 0
Yes this works thank-you. I am going to dig into this syntax to make sure I understand what it is doing.

One last question. If I were to make another query (Query3) and produce a SUMIF table on Query2 based the date column (years) how would I do that?

Thanks
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top