Can I add multiple columns at once with a Power Query M custom formula?

Question

As a business analyst, I need to repeatedly use cost and revenue data points to create different variants of a margin result.

I am trying to come up with a reusable piece of code that I can use and share so that everyone in my team can create their own margin analysis in power query with a few simple clicks.

Once I know how to do this, I can use the same logic on a plethora of other applications, so I'm looking forward to everyione's ideas!

I tried to write a custom function in Power Query M that would take two columns and return the results in more than one column.

The following piece of code does not work. It throws an error saying it expected an EoF Token in line 6:

(Cost as number, Revenue as number) =>
let
    margin_absolute = Revenue - Cost,
    margin_relative = Cost / Revenue
in
    margin_absolute
in
    margin_relative

Maybe I'm using the wrong approach?

Answer 1

I was able to make it work thanks to the below Chat GPT answer. I successfully tested the code.

"It looks like there are a couple of issues with your Power Query M code. The primary issue is with the structure of your let...in expression. You've closed the let...in block prematurely and attempted to return two values directly, which is not supported in the way you've structured it. Power Query M functions can only return a single value, but this value can be a record or a table, which can effectively contain multiple values (or columns).

Here's how you can modify your function to return both margin_absolute and margin_relative as a record:

(Cost as number, Revenue as number) =>
let
    margin_absolute = Revenue - Cost,
    margin_relative = if Revenue <> 0 then Cost / Revenue else null
in
    [Margin_Absolute = margin_absolute, Margin_Relative = margin_relative]

In this revised version:

• The function takes Cost and Revenue as inputs.
• It calculates margin_absolute and margin_relative.
• It returns a record containing both Margin_Absolute and Margin_Relative.
• I've added a check to prevent division by zero in the margin_relative calculation.

To use this function in your Power Query editor:

1. Create the Function: Copy the revised function into a new blank query to create the function.

2. Invoke the Function: To use this function, you'll need to call it for each row of your table. Suppose your table has columns named Cost and Revenue. You can add a custom column to call this function:

   Table.AddColumn(yourTableName, "Margin", each (Cost as number, Revenue as number) =>
   let
       margin_absolute = Revenue - Cost,
       margin_relative = if Revenue <> 0 then Cost / Revenue else null
   in
       [Margin_Absolute = margin_absolute, Margin_Relative = margin_relative], type record)
   

3. Expand the Record: After adding the custom column, you'll have a column of records. You can expand this to create two new columns, Margin_Absolute and Margin_Relative, by using the "Expand Column" feature in the Power Query editor.

This approach allows you to encapsulate the margin calculations in a single function, which can be reused across multiple queries or shared with your team."