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

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

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

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

 

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

You can learn more :