Replicating Excel’s Find Function With ‘M’ in Power Query

In the above example, you learned how to concatenate the columns using the M language. Unfortunately, not all of the Excel’s formulas can be used in M query.

Replicating Excel’s Find Function With ‘M’ Using Power Query

For instance, if we want to use Excel’s FIND function to find a specific character in the text, it is not supported in M. Let us see how to replicate the FIND Function using M!

Step 1: Select the Name Column

First, load the data table in Power Query and select the Name column as shown in the below picture. This is because we want to find out the position of the letter ‘E’ in this column.

Step 2: Add Custom Column

Add a Custom Column to write a query in M language for finding the position of letter ‘E’. To use a Custom Column feature, go to-

Add Column  >  Custom Column

[xyz-ihs snippet=”Business-Analyst-Course”]

Step 3: Find Position of Text using M Language

First, name the column as we have named it as ‘Position’ in this case. Then write the M query as given below-

=Text.PositionOf([Name],”E”)+1

Where,

Text.PositionOf formula is used to find the letter “E” in the Name column

We have added 1 to the query because the position of a formula starts counting at 0.

Click on OK after you write the query.

Step 4: Close & Apply

Close & Apply the Power Query changes after you get the new column added showing the position of letter E in each row as given below.

You can know more :

• Get The Length of Strings Using ‘M’ in Excel
• Use Excel’s Right Function With ‘M’ To Extract Right Side Data
• Top 5 ways to Concatenate Datausing ‘M’ in Power Query
• Best Ways to Add Column From Examples in Power Query