fbpx
[email protected] +1 203-349-9909 / +91-8080042523

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.

BEFORE FINDING CHARACTER IN TEXT

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

ADD CUSTOM COLUMN

BECOME A BUSINESS INTELLIGENCE ANALYST COURSE
• Power BI • Excel to Alteryx • SQL Programming Language • SAS Programming • Tableau • KNIME • many more

 

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.

FIND POSITION OF TEXT USING M LANGUAGE

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.

AFTER FINDING CHARACTER IN TEXT

You can know more :