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 Right Function With ‘M’ Using Power Query
For instance, if we want to use the 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
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 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