Pinterest Pixel

Replicating Excel’s RIGHT Function with M in Power Query

Bryan
Power Query lets you perform a series of steps to transform your Excel data.
There are times when we want to do things that are not built into the user interface.

This is possible with Power Query's programming language, which is M.

Unfortunately, not all of Excel's formulas can be used in M.

For example, if we want to use the Excel RIGHT Function, it is not supported in M.

But I will show you how we can replicate the RIGHT Function Power Query in M and the Text.End formula!

Key Takeaways:

  • Power Query’s M language allows for data transformation in Excel and can replicate functions not directly available in the M language, such as the Excel RIGHT function. The RIGHT function is simulated using the ‘Text.End’ function in M, which extracts a specified number of characters from the end of a text value.
  • The step-by-step tutorial guides the user to add a custom column in Power Query Editor where the last character of a specific column (e.g., the ‘SALES QTR’ column) is extracted by creating an M expression. This process involves selecting the data, turning it into an Excel Table, using Power Query to add a custom column, and then implementing the formula: ‘Text.End([ColumnName], num_chars)’.

If you want to learn how to get started with M, click here.

In this example, you can see there is a column I mentioning quarter. You want to extract the last character from the text. QTR1 mentioned in cell I2, you want to extract the number 1.

You can use the Text.End function in Excel PowerQuery to returns the number of characters from the end of a text value.

Replicating Excel's RIGHT Function with M in Power Query

 

Syntax of Text.End Function

The syntax of Text.End Function is:

=TEXT.END(text, num_chars)

where:

  • text – the cell from which you want to extract characters
  • num_chars – number of characters you want to extract

Follow the step-by-step tutorial on Replicating Excel RIGHT Function with M in Power Query and download this Excel workbook to practice along:

download excel workbookReplicating-Excels-RIGHT-Function-with-M.xlsx

Let’s go through the steps in detail:

STEP 1: Select your data and turn it into an Excel Table by pressing the shortcut Ctrl + T or by going to Insert > Table

Replicating Excel's RIGHT Function with M in Power Query

 

STEP 2: Go to Data > Get & Transform > From Table (Excel 2016) or Power Query > Excel Data > From Table (Excel 2013 & 2010)

Excel 2016:
 Merge Columns Using Power Query

Excel 2013 & 2010:

from table

 

STEP 3: This will open up the Power Query Editor.

Select the SALES QTR column.

Go to Add Column > Add Custom Column

Replicating Excel's RIGHT Function with M in Power Query

 

STEP 4: We want to get the last character of the SALES QTR cell values.  For example: 1, 2, 3 or 4

So, let us create a simple M expression to replicate the Power Query RIGHT function

In the New column name text box, type SALES QTR (Shortened)

In the Custom column formula, type in: Text.End(

From the Available columns choose SALES QTR and Insert

Then finish off the formula by entering, 1) 

We now have built the following formula:

Text.End([SALES QTR], 1)

 

So let us quickly break down what we just did:

  • We are using the Text.End formula to get the last X characters of the SALES QTR column
  • We place in 1, to specify that we want the last 1 character.

 

Click OK to confirm.

Replicating Excel's RIGHT Function with M in Power Query

Now you will see your changes take place.

Replicating Excel's RIGHT Function with M in Power Query

 

STEP 5: Click Close & Load from the Home tab and this will open up a brand new worksheet in your Excel workbook with the updated values.

Replicating Excel's RIGHT Function with M in Power Query

 

Congratulations! You have used a M formula for replicating the RIGHT function in Power Query!

Replicating Excel's RIGHT Function with M in Power Query

Just like Text.End can be used to replicate the Excel Right function,

 

Enhancing Efficiency in Power Query with RIGHT Equivalents

Best Practices for Readable and Maintainable M Code

To craft M code that stands the test of time and collaboration in Power Query, one should prioritize readability and maintenance. Start by assigning intuitive names to your steps; consider ‘ExtractLastName’ far more informative than ‘Step2’. This small effort can lead to a more narrative-style code, allowing anyone who reads it to follow along seamlessly.

Sprinkle your code with comments generously, as they narrate the ‘why’ which is just as crucial as the ‘what’ part of your code. It’s like leaving breadcrumbs for the next person to follow your logic with less guesswork.

Top tip: Keep your M code modular. Break down transformations into bite-size steps that can easily be altered or debugged. Imagine assembling a picture with puzzle pieces instead of trying to change a section of a large tapestry.

Write your M code with someone else in mind, ensuring that clarity, simplicity, and thorough documentation are your stalwart companions on your Power Query adventures.

Comment In Query Steps In Power Query

Custom Functions for Repeated Use of RIGHT Logic

Creating a custom function in Power Query can skyrocket your productivity, especially if you find yourself frequently applying the RIGHT function logic. Imagine you have a myriad of tables where you consistently need to extract specific information from the right end of text values. Why not craft a reusable tool for that?

Here’s the recipe: devise a function once and call upon it wherever needed. You simply feed it parameters like the text you’re dissecting and the number of characters you yearn to extract—and voilà, consistent results and a codebase devoid of redundancy.

To conjure a custom function, navigate to the Advanced Editor, start with a let expression, define your parameters, pen your logic using Text.End, and conclude with an in statement. Remember to add comments to elucidate the purpose and usage of your function.

Here’s the essence of it: custom functions not only serve as a timesaver but also instill uniformity across your workbooks, making repeatable tasks a breeze.

Troubleshooting Common Errors and Solutions

Common Mistakes When Using Text.End in Power Query

When you’re cruising through data transformation in Power Query, using Text.End to replicate Excel’s RIGHT function, it’s easy to hit some turbulence if you’re not careful. A frequent misstep occurs when you try to extract more characters than the string’s actual length, which will lead to an error faster than you can say “out of range”.

Always confirm the length of the text before you attempt to slice it with Text.End. And here’s a heads-up: if you dive in without first converting your data to text format, Power Query might not understand what you’re asking it to do.

Another trap to watch out for is ignoring those pesky null or blank values that can throw a wrench into your operation. These silent gremlins can cause unexpected errors that seem to come out of nowhere.

Protect your workflow by conducting a meticulous pre-flight check: ensure data types are consistent, text lengths are verified, and nulls are addressed. Doing so ensures a smooth journey through the Power Query skies.

Replicating Excel's LEN Function with M in Power Query

Tips on Error Handling and Data Clean-up

Embrace the power of anticipation in Power Query by being proactive with error handling and data clean-up. Your first line of defense is the ‘try…otherwise…’ expression, a trusty sidekick that lets you test waters before plunging in. Set up a fallback plan for when things don’t go as expected. Think of it as adding a safety net that catches errors and keeps your workflow graceful.

Before attempting to use Text.End, engage the dynamic duo Text.Trim and Text.Clean. They’re like the dynamic clean-up crew for your data, trimming extra spaces and sweeping away hidden gremlins — those non-printable characters that can befuddle your text analysis.

Imagine going fishing. You wouldn’t want to start without cleaning your catch first, right? Similarly, cleanse your text data before filleting it with Text.End to ensure you don’t reel in any unwanted errors.

FAQ: Mastering RIGHT Function in Power Query

How Can I Ensure Accurate Replication of Excel’s RIGHT Function in Power Query?

To ensure a true-to-form replication of Excel’s RIGHT function within Power Query, closely mirror its behavior by using the Text.End function. Exercise precision—always pass the correct type of data, typically a text string, and specify the exact number of characters you need to extract from the end.

Double-check your work by comparing the Power Query output with what you would get in Excel using the RIGHT function. Keep an eye out for discrepancies, which could signal a need for data type adjustments or error handling.

Remember, it’s all about attention to detail, ensuring data are in the right format, and meticulously simulating the RIGHT function’s behavior so your data dance to the same rhythm in Power Query as they do in Excel.

Replicating Excel's RIGHT Function with M in Power Query

What Are Some Common Pitfalls When Using RIGHT Function in Power Query and How Do I Avoid Them?

Dodge the common pitfalls of replicating the RIGHT function in Power Query by keeping a few key things in mind:

  1. Watch out for data type discordance. Power Query is finicky about types, so ensure strings are converted with Text.From before trying to use Text.End.
  2. Beware of assuming fixed-length strings. In the real world, data can be inconsistent. Verify lengths using Text.Length to prevent the dreaded ‘out of range’ error.
  3. Prevent null values from causing a ruckus by incorporating a null check before proceeding with text functions.
  4. Avoid unnecessarily complex code. Keep it straightforward with a modular approach to make troubleshooting and readability far less daunting.

Evade these traps by testing with a variety of datasets and scenarios, ensuring your Power Query formulas behave impeccably under all circumstances.

Replicating Excel's LEN Function with M in Power Query

If you like this Excel tip, please share it



Replicating Excel's RIGHT Function with M in Power Query | MyExcelOnline


Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Advanced Editor In Power Query

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...