Replicating Excel’s RIGHT Function with M in Power Query

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.

 

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:

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

 

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

Excel 2016:
 

Excel 2013 & 2010:

 

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

Select the SALES QTR column.

Go to Add Column > Add Custom Column

 

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.

Now you will see your changes take place.

 

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.

 

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

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

• Use Text.Start to replicate the LEFT function
• Use Text.Position to replicate the FIND function
• Use Text.Length to replicate the LEN 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.

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.

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.

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.