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

[rkaczano]

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

 

[rkaczano]

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

 

[jdellasala]

I created a 10 Row, 200 Column table using RANDARRAY between 1 and 9.

=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
26	Multiplication
27	11,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
28	7,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
29	10,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
30	1,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
31	2,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
32	43,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
33	571,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
34	961,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
35	32,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
36	235,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:

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

 

[rkaczano]

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

**********************************

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"

 

[JGordon11]

Assuming Column196 is the last column in the table

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

 

[rkaczano]

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