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.
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.
- 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.
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.