Text.PadStart

What is the Text.PadStart Function?

The Text.PadStart function is a built-in function in Power Query that is used to add leading characters to a text string to make it a specified length. It takes three arguments:

– Text: The text string to which leading characters will be added.

– Length: The desired length of the resulting text string.

– Character: The character to be used for padding.

The Text.PadStart function is particularly useful when working with data sets where values need to be standardized. For example, if you have a data set that includes product codes that are all supposed to be six characters long, but some are only four characters long, you can use the Text.PadStart function to add leading zeros to the shorter codes.

The M Code Behind Text.PadStart

The M code behind the Text.PadStart function is relatively simple. Here is an example of what the code might look like:

The code takes the three arguments passed to the function and uses them to add padding to the text string. The first line of the code concatenates the padding character with the text string, creating a new string that is one character longer than the original. The next line counts the number of characters in the new string, which determines whether padding needs to be added or not. If the length of the new string is greater than or equal to the desired length, the function returns the original text string. If not, the Text.PadStart function is used to add the necessary padding characters to the beginning of the string to make it the desired length.

Examples of Using Text.PadStart

The Text.PadStart function can be used in a variety of scenarios. Here are some examples:

Adding Leading Zeros to Product Codes

As mentioned earlier, the Text.PadStart function can be used to add leading zeros to product codes that are shorter than the desired length. Let’s say you have a data set with product codes that are supposed to be six characters long. Some of the product codes are only four characters long. You can use the Text.PadStart function to add two leading zeros to these codes like this:

This code adds a new column to the MyData table called Padded Product Code. The Text.PadStart function is used to pad the Product Code column with leading zeros to make it six characters long.

Padding Strings with Spaces

In some cases, you may want to pad a string with spaces instead of zeros. For example, you may want to create a report where the column headers are all the same length. You can use the Text.PadStart function to add spaces to the beginning of the shorter column headers like this:

This code adds a new column to the MyData table called Padded Column Header. The Text.PadStart function is used to pad the Column Header column with spaces to make it 15 characters long.

Combining Multiple Columns into a Fixed-Length String

Sometimes you may want to combine multiple columns into a single string that is a fixed length. For example, you may want to create a unique identifier for each row in your data set that is based on multiple columns. You can use the Text.PadStart function to ensure that the resulting string is always the same length like this:

This code adds a new column to the MyData table called Unique ID. The Text.PadStart function is used to pad the concatenation of Column 1, Column 2, and Column 3 with leading zeros to make it 10 characters long.

The Text.PadStart function is a powerful tool in Power Query that can be used to standardize and transform data. By understanding the M code behind the function, you can better understand how it works and how to use it effectively. The examples provided in this article are just a few of the many ways that the Text.PadStart function can be used to enhance your data analysis.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us