30 Advanced Excel Tips for 2026

Excel Champs / Power Query / Power Query: Functions and Formulas
By Puneet Gogia (Microsoft MVP)

Power Query: Functions and Formulas

Go to Add Column → Custom Column, name the new column and type the formula after the =, for example =[Jan] + [Feb]. Double-click a name in Available columns to insert it in square brackets, then click OK.

Power Query functions follow a Category.Name pattern: =Date.EndOfMonth([Date]), =Date.Year([Date]), =Text.Upper([Name]). They are case-sensitive, so date.endofmonth returns an Expression.Error saying the name wasn’t recognised.

Excel worksheet functions don’t carry over. IF becomes =if [Sales] >= 1000 then "High" else "Low" in lowercase, and text joins use & with Text.From for numbers. To list every function available, create a blank query (Data → Get Data → From Other Sources → Blank Query) and enter = #shared.

To edit a formula later, click the gear icon next to Added Custom under Applied Steps, or turn on View → Formula Bar and edit the step’s code directly. Home → Advanced Editor shows the whole query.

A blank cell comes through as null, and null plus a number is null, so one empty Feb cell leaves that row’s total blank. =List.Sum({[Jan], [Feb]}) ignores nulls. The custom column is also typed Any (ABC123), so set its data type before you load, and refresh with Data → Refresh All when the source changes.

In Power Query, you can use functions and formulas almost as you use them in a worksheet. Power Query has a long list of functions, and you can write formulas using different functions or calculation operators.

And in this tutorial, we will understand functions and formulas in detail.

Writing Your First Formula in Power Query

In Power Query, unlike Excel, you need to add a new column to enter a formula. You can’t enter it in a single cell, as a formula always works on the whole column. Let’s understand it with an example. Below we have some values in columns A and B.

formula-in-power-query

And now, insert a new column with the total of both columns. To get this, you can use the below steps:

  1. First, load the data into the Power Query editor (Data Tab > Get Data > From Other Sources > From Table/Range).
    load-data-into-power-query
  2. In the editor, you need to go to the Add Column Tab and click the Custom Column button to open the dialog box.
    custom-column-button
  3. From here, in the dialog box, you must enter the formula you want to use. The equal to sign is already there, so you don’t need to enter it.
    enter-the-formula-in-dialog-box
  4. Next, you need to write the formula:
    • Click on the Jan in the available columns to enter it in the formula.
    • Enter the plus (+) sign.
    • Click on the Feb in the available columns to enter it in the formula.
    • Also, type a name for the column.
    write-formula-to-enter-with-new-custom-column
  5. In the end, click OK to enter the formula with a new custom column.

The moment you click OK, it adds a new column with the sum of columns A and B (Jan and Feb).

new-column-with-the-formula-results

In the end, click the Close & Load button from the Home tab to load the data back to a new worksheet in Excel.

click-close-and-load

And once you click on it, it will close the Power Query editor and load the data by inserting a new worksheet.

loads-the-data-by-inserting-new-worksheet

Power Query doesn’t update in real time, but when you enter new values into the source data and refresh the table that you have loaded back from the Power Query editor (Data > Refresh All), you get all the new values in that table. And the calculation for the new values is also done when you refresh.

Editing a Formula

Once you write a formula in the Power Query, you can edit it.

  • First, select one of the cells from the new table and get to the “Query” tab from the ribbon.
  • And then, click on the edit button.
edit-a-formula-in-power-query

Once you click the edit button, it opens the power query editor.

power-query-editor-opened

From there, you need to go to the right side of the window where you have the “Applied Steps”.

And then click the gear icon next to “Added Custom” to open the custom column dialog box where you initially entered the formula.

click-on-added-custom

And from the dialog box, you can edit and change the formula you wrote.

edit-or-change-the-existing-formula

Using formulas in Power Query is like formulas in Excel; you only need to know the right function or the operator to use.

In the next part of this tutorial, we will learn to use a function to get a specific result with Power Query.

Using Functions in Power Query

Like Excel, Power Query has a long list of functions that you can use for specific calculations.

And ahead in this tutorial, we will learn to use a function to understand how to insert it using a new custom column, and here is the list of functions from Microsoft that you can refer to when you search for the function you need.

In the below example, we have a list of dates, and now we need to get the last date of the month to which these dates belong. For instance, if a date is from the month (May), we need to get the last date of May.

use-functions-in-power-query

And for this, we can use the Date.EndOfMonth function. In this function, we have only one argument to define: dateTime.

Once you load your data into the Power Query editor, you need to go to the Add Column tab and click on the Custom Column button to open the custom column dialog box.

open-custom-dialog-box

In the dialog box, enter the function’s name, Date.EndOfMonth. Instantly, Intellisense will show a list of related functions when you start typing the function’s name.

in-the-dialog-box-enter-function-name

You can enter the full name of the function or click on the name you have in the list. After entering the function’s name, you need to enter an opening parenthesis, and it will show you the details of the arguments you need to specify.

enter-starting-parenthesis-after-the-formula-name

As I have mentioned earlier, we need to specify the argument dateTime, which can be a date or a date and time value. And we have a list of dates in the data loaded to Power Query.

So, you need to enter the column’s name in the function’s argument. And then close the function with the closing parenthesis.

column-name-in-function-argument

Before you click OK, add a name for the new column.

add-name-of-new-column

In the end, click OK to insert the column. And once you click OK, it will insert a new column with the last Date of the month according to the date that you have in the corresponding column.

new-column-with-last-date-of-the-month

This column also has a time along with the date (dates from Excel load into Power Query as Date/Time), so you need to change the data type of the column to Date. Click the “Data Type” drop down and select “Date” for this.

from-data-type-select-date

This will change the format of the column into the Date. And you can see we have the last Date of the month according to the month you have in the first column.

column-format-changed-into-date

Using Functions for Calculations

In the above example, we have used a simple function to make you understand the steps to use any function in Power Query.

Once you know which function to use for the calculation you need, you can insert a custom column and insert the function and then specify the arguments that it requires.

One important thing: unlike Excel, function names in Power Query are case-sensitive. Date.EndOfMonth works, but date.endofmonth gives an error saying the name wasn’t recognized. The same goes for column names in square brackets.

Operators You Can Use in Power Query Formulas

  • Math: + (add), – (subtract), * (multiply) and / (divide), like [Jan] + [Feb].
  • Join text: & joins text values, like [First Name] & ” ” & [Last Name]. To join a number with text, convert it first with Text.From.
  • Compare: =, <>, >, <, >= and <=, which return true or false.
  • Logic: and, or and not, written in lowercase, like [Sales] > 100 and [Region] = “East”.

For a condition, Power Query uses if then else instead of the IF function. For example, the following custom column formula returns “High” when the total is more than 500:

if [Total] > 500 then "High" else "Low"

Frequently Asked Questions

Can I use Excel functions like VLOOKUP in Power Query?

No. Power Query has its own language (M) with its own functions. For a lookup, you merge two queries instead, as shown in the VLOOKUP in Power Query tutorial.

Why does my custom column show Error?

Most of the time, the data types don’t match, like adding a number to a text value, or a cell in the column is null. Click on the Error cell to see the message, and check the data type of each column you use in the formula.

Where can I see the whole formula of a query?

Turn on the formula bar (View > Formula Bar) to see the formula of each step, or open Home > Advanced Editor to see the full M code of the query.