How to Extract Right Side Data form Excel? If we want to use Excel’s RIGHT Function, it is not possible as it is not supported in M. But we can use the RIGHT Function in M in other way using Power Query and the Text. End formula!
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)
Use Excel’s Right Function With M To Extract Right Side Data
Let’s see how to use RIGHT Function to extract right-side data from our data table.
Step 1: Select the Column for Extracting Right Side Data
After loading the data in Power Query, select the ‘ProductKey’ column for applying custom Column formula as shown in below picture.
Step 2: Add Custom Column to Extracting Right Side Data
Now, add the Custom Column by performing below step-
Add Column > Custom Column
Step 3: Extracting Right Side Data using M Language
To extract the right-side data we have to create a simple M expression.
In the New column name text box, we have to write the new column name as we have shown in the below picture. Now, you can write the M query as given below:
In the Custom column formula, type the function Text.End(
From the Available columns select the PoductKey column and Insert
Then wind up the formula by entering ‘, 3)’
The formula used is given below:
Text.End([PoductKey],3)
Where,
- End function is used to get the last X characters of the product key column
- We place in 3, to specify that we want the last 3 characters (right side characters) as shown below.
Click OK to confirm.
Step 4: Close & Apply
Close and apply the changes after extracting right-side data as shown in the below picture.
Data Analytics Tricks in Power BI – Guidebook
You too can analyze data like a Data Scientist. No coding needed. No statistics needed.
Analyze & Visualize data using Power BI. (23 tricks in one book)