Power Query returning Excel functions

[jmlee]

Greetings,

is it possible to place a normal Excel function into a Column in Power Query, so that it works as a function in the resulting data?

Simple Example:
As part of the data, I have piece prices and volumes from which we calculate to get the total price (price * volume = Total). Thus, I want PQ to create (among everything else) 3 Columns "price", "volume" and "total", with the raw data in the first two columns, but the formula "=[price]*[volume]" (or any excel formula) in the last column.

I have tried it, but all methods just return the function as text. Entering the cell and hitting return activates the function, but no one wants to do that for thousands of rows.

I am aware that this somewhat contradicts the purpose of PQ, but I have cases where other users copy individual lines from the data, and they want the dynamic function in case they need to test different prices or volumes.

Cheers,
Jamie

 

[alansidman]

Mcode for your issue

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Price", type number}, {"Volume", Int64.Type}}),
    #"Inserted Multiplication" = Table.AddColumn(#"Changed Type", "Multiplication", each [Price] * [Volume], type number)
in
    #"Inserted Multiplication"

 

[Ken Puls]

Hi jmlee,

It's entirely possible to create formulas in Power Query, but you have to learn the M syntax, which is quite different than Excel's formula language.

Having said that, a simple action like multiplying two columns can also be performed by button clicks so that you don't have to write any formulas at all:

• Make sure the column data types are numeric
• Click the Price column
• Hold down CTRL
• Click the Volume column
• Go to Add Column -> Standard -> Multiply
• Rename the new column to Total

Doing that will give you the exact same results as Alan posted about, but without having to write a formula at all (as Power Query does it for you.)

 

[jmlee]

Sorry, been busy. Just getting back to this. Thanks, Alan and Ken, for your responses.

Alan's code doesn't, however, solve my problem. It gives me what I am used to, namely I get a new column that contains the numerical result. I want, however, Power Query to fill the column with a regular Excel function. So, the final result in Excel would look like this (with show functions on):

Volume	Price	Cost
10	23.25	=[Volume]*[Price]
46	27.50	=[Volume]*[Price]

And, the excel function could be any excel function (=SUM(…), =SUMPRODUCT(…), etc.)

I am getting reasonably good at M-code. And, as I said, I realize that my idea runs counter to Power Query's chief purpose. In fact, I have now decided *not* to do this with the data that I originally needed it for. But, I am still curious to know if it is possible. As I wrote before, I can create a literal
... Table.AddColumn(#"Changed Type", "Multiplication", each "= [Volume] * [Price]"

…which does place the function in the Cost column, but as text, thus requiring me to enter each cell and hit enter to force the calculation.

I have searched microsoft's m-code page, but I find nothing that applies.

Any ideas?

Cheers,
Jamie

 

[Bernie]

You could you create another table that does the multiplication based on the results returned from the query - it won't auto-expand as the query results change, but the values will update correctly. There is no need to do any of that though - if you can calculate it in the query, then the results will be correct for the query results. If the source data changes, then the values that your formula uses won't be updated until the query is updated, so your formula will return incorrect results anyway.

Bernie

 

[Ken Puls]

...I want, however, Power Query to fill the column with a regular Excel function....

Sorry Jamie, when I said:

t's entirely possible to create formulas in Power Query, but you have to learn the M syntax, which is quite different than Excel's formula language.

I meant Power Query formulas, not Excel Formulas. It is not possible to create an Excel formula in Power Query and then land it to an Excel worksheet. Power query generates static values that it lands to Excel.

 

[Bob Phillips]

I have tried it, but all methods just return the function as text. Entering the cell and hitting return activates the function, but no one wants to do that for thousands of rows.

Why not add some VBA to do that F2ing for all the rows?