Power BITutorials

Use Excel’s Right Function With M To Extract Right Side Data

1 Mins read

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!

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.

BEFORE EXTRACTING RIGHT SIDE DATA USING M LANGUAGE

Step 2: Add Custom Column to Extracting Right Side Data

Now, add the Custom Column by performing below step-

Add Column > Custom Column

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

EXTRACT THE RIGHT SIDE DATA USING M LANGUAGE

Step 4: Close & Apply

Close and apply the changes after extracting right-side data as shown in the below picture.

AFTER EXTRACTING RIGHT SIDE DATA USING M LANGUAGE
Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…