Excel has so many formulas but only a few of them can be used in M. For instance, Excel’s FIND formula to find a specific character in the text will not support in M. Here, I’ll tell you to step by step, how to replicate the FIND Function in M.

step by step, how to replicate the FIND Function in M.

Previously in Power QueryHow to Keep Duplicate Records Using Power Query?

Follow given below steps: 

STEP1: Insert Table

 first you need to convert your data into Excel Table so for that select your data and press the shortcut Ctrl + T or go to Insert > Table. It will easily turn your data into an Excel Table. 

How to Replicate Excel's FIND Function with M in Power Query? 1

STEP2: Open Power Query

If you are using Excel 2016:

Go to Data > From Table (Under Get & Transform)

Remove Columns Using Power Query

Or if you are using Excel 2013 & 2010:

Power Query > Excel Data > From Table

STEP3: Add Column

After following them you will see a Power Query Editor on your screen. I want to find the position of the letter O of the Brand Clients, so here I will select the Brand Client’s column.

How to Replicate Excel's FIND Function with M in Power Query? 2
Now follow this: Go to Add Column > Add Custom Column

STEP4: Creating an M Expression

Now I need to replicate the FIND function in Excel by creating a simple M expression. 

Type ‘FIND’ in the New column name text box. 

Type in: Text.PositionOf( in the Custom column formula. 

Choose Brand Clients from the Available columns and select Insert.

Enter “o”) + 1 and finish the formula. 

Now I have built the following formula:

Text.PositionOf([Brand Clients], “o”) + 1 (Dont copy this, write by yourself)

So, here is the breakdown of this formula:

  • Text.PositionOf formula: I have used this formula to find the letter “o” in the CHANNEL PARTNERS column.
  • PositionOf formula starts counting at 0 so, I have added 1 to it because the FIND formula in Excel starts counting at 1 as the 1st character. 

So, remove the difference and replicate the FIND formula here I added 1 to this formula.

Now, Click OK to confirm the formula.

How to Replicate Excel's FIND Function with M in Power Query? 3

Here you will see the changes take place. 

For example, in “Nike Sports LLC” the first “o” encountered is the 8th character in this text string.

How to Replicate Excel's FIND Function with M in Power Query? 4

At last to open a new worksheet in your Excel workbook, Click Close & Load from the Home tab and this will open with the updated table.

How to Replicate Excel's FIND Function with M in Power Query? 5

There you find the ‘O’ letter by using M formula in Power Query to replicate FIND Function.

How to Replicate Excel's FIND Function with M in Power Query? 6