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 Query****: ****How 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.

## STEP2: Open Power Query

If you are using Excel 2016:

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

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.

## 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.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.**PositionOf**

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

Now, Click OK to confirm the formula.

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.

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.

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

## Leave a Reply