Do you have the same problem as "I understand Excel's VLOOKUP and IF functions, but I don't know how to write them in Power Query?"

Power Query is a data conversion tool that is standard in Excel 2016 and later, and as of March 2026, it has become indispensable for improving the efficiency of Excel operations. However, many people are confused at first because the way they are written is completely different from the Excel functions they are used to.

In this article, we have summarized in an easy-to-understand manner How the 10 functions commonly used in Excel can be written in Power Query with a correspondence table and specific examples. If you have started using Power Query but don't know how to convert functions, please bookmark this page.

What is Power Query's "M language" anyway?

The programming language that runs behind Power Query is the M language (official name: Power Query Formula Language). Normally, you don't have to worry about it because you can use it with just the GUI (mouse operation), but if you want to add a "custom column" or perform a slightly complicated conversion, you will need to write the M language directly.

Roughly speaking, think of Excel's formula bar as a place to write Excel functions, and Power Query's formula bar as a place to write M language.

M language has some rules that are different from Excel functions.

  • Case sensitive: Text.Start is OK but text.start is an error
  • Index starts at 0: Excel counts the first character as "1", but in M language it counts as "0"
  • Function names in English: "Category.Action" format, such as Text.Start (equivalent to LEFT), Text.End (equivalent to RIGHT)

If you just remember these three things, your resistance to M language will be greatly reduced.

Excel function → Power Query (M language) conversion quick reference table

First, let's quickly check the list. Compatible with Excel (Microsoft 365 / Excel 2021 or later) and Power Query as of March 2026.

Excel functionPower Query (M language)Note
IFif condition then value 1 else value 2Write all in lower case!
VLOOKUP"Merge query" function (left outer join)Alternative to GUI operation instead of function
LEFTText.Start(string, number of characters)Extract N characters from the beginning
RIGHTText.End(string, number of characters)Extract N characters from the end
MIDText.Middle(string, starting position, number of characters)Note that the starting position starts at 0!
LENText.Length(string)Returns number of characters
TRIMText.Trim(string)Remove leading and trailing spaces
CONCATENATE / &String 1 & String 2 or Text.Combine& operator can be used as is
UPPER / LOWERText.Upper(string) / Text.Lower(string)Uppercase/lowercase conversion
SUBSTITUTEText.Replace(string, old, new)Replace string

If you paste this table next to your desk, it will be convenient for you to quickly check it when using Power Query.

Detailed explanation of three particularly confusing functions

1. IF function → if then else

The IF function in Excel is one of the most used functions. Power Query can be used in almost the same way, but the writing rules are slightly different.

For Excel:

=IF(A2>=100, "Pass", "Fail")

For Power Query (custom column formulas):

if [score] >= 100 then "Pass" else "Fail"

There are three points.

  • if, then, and else are written in all lowercase (an error occurs if uppercase)
  • Do not enclose in parentheses (syntax is different from Excel's IF())
  • Enclose column names in square brackets like [column name]

When nesting, write as if condition 1 then value 1 else if condition 2 then value 2 else value 3. Many people find it easier to read than Excel's IF(IF()).

2. VLOOKUP → Merge queries

This is the most confusing point. There is no VLOOKUP equivalent in Power Query. Instead, use a feature called "Merge Queries".

In short, it is an operation that joins two tables on a common key column. It's the same concept as database JOIN.

Steps:

  1. In the Power Query editor, load the two tables you want to join
  2. Click "Home" → "Merge Queries" on the menu bar
  3. Choose the tables you want to join and select each common key column by clicking on it
  4. For the join type, select "Left outer (all rows from the first table and matching rows from the second table)" — this is the behavior closest to VLOOKUP
  5. Click the expand button (⇔ icon) for the combined table columns to expand only the columns you need

According to Microsoft's official document, there are six types of joins in total, but if you use it for VLOOKUP, you can just choose "left outer".

The big difference with VLOOKUP is that Once you set the merge, even if the original data is updated, it will be automatically reflected. Unlike VLOOKUP, there is no need to enter formulas into hundreds of rows of cells, so the file is also lighter.

3. MID → Text.Middle (0-starting trap)

Excel's MID function specifies "from which character to which character to take", but Power Query's Text.Middle starts at 0. A lot of people make this mistake.

For Excel:

=MID(A2, 3, 4) → Take 4 characters from 3rd character

For Power Query:

Text.Middle([column name], 2, 4) → Take 4 characters from the 3rd character (specify "2" as it starts with 0)

In other words, remember that in Power Query, you specify the starting position in Excel minus 1.

By the way, there is a similar function called Text.Range, but if there are not enough characters, an error will occur. Text.Middle is less prone to errors, so Text.Middle is recommended for beginners.

Steps to add custom columns in Power Query

For those who say, "I understand the quick reference table, but where should I actually put the formulas?", I will introduce the basic steps for adding a custom column.

  1. Select the data range in Excel and click the Data tab → From Table or Range to open the Power Query Editor
  2. Click “Add Column” tab → “Custom Column” in the menu
  3. Enter the M language formula in the "Custom column formula" field (e.g. if [Sales] >= 10000 then "Achieved" else "Not achieved")
  4. Enter column name and click OK
  5. Check the result in preview and reflect it in the Excel sheet by selecting "Home" → "Close and Load"

Once you learn this flow, you can immediately try out the functions in the quick reference table.

Which should I use: Excel functions or Power Query?

You may be wondering, "Should I replace everything with Power Query?" The answer is case by case.

When this happensRecommended
One-time calculations/small aggregationsExcel functions
Process data in the same format every monthPower Query
VLOOKUP has hundreds to thousands of rowsPower Query (light processing)
Combine multiple CSV/Excel filesPower Query selection
Sheets maintained by othersExcel functions (even people who don't know Power Query can use them)

In short, it is best to use Power Query for repetitive tasks and Excel functions for one-off calculations.

FAQ

Which version of Excel can I use Power Query with?

It is included as standard in Excel 2016 and later. Excel 2010/2013 requires installation as a free add-in. If you use Microsoft 365 (formerly Office 365), you can always use the latest version of Power Query (as of March 2026).

The formula written in Power Query results in an error. What should I check?

First, please make sure that the uppercase and lowercase letters are correct. The M language is case sensitive, so you need to write Text.Start instead of text.start. Next, make sure that the column name is enclosed in square brackets [].

How can I make it "blank if not found" like VLOOKUP?

If you use a left outer join with Merge Query, unmatched rows are automatically null (blank). If you want to replace null with another value, you can right-click the column and select "Replace Value" to convert null to any value.

Can I share queries created with Power Query with others?

Yes, the query is saved in the Excel file, so you can use it as is by sharing the file. However, if the data source path (file storage location) is different, an error may occur, so we recommend using the shared folder path.

References