How to use Text.PadStart in Power BI?

In this article we will what Text.PadStart is, the syntax and show you how to use it.

Text.PadStart is a Power Query function that returns a text value padded to length count by inserting spaces at the start of the text value text. An optional character character can be used to specify the character used for padding. The default pad character is a space.

Text.PadStart Syntax

Text.PadStart has the below syntax. Please note that the ‘character’ parameter is optional.

Text.PadStart(text as nullable text, count as number, optional character as nullable text)

How to use Text.PadStart

We now know what Text.PadStart is and its syntax, but how do we use it?

Open the Power Query editor in Power BI and go to ‘Add Column’ > ‘Custom Column’ and use the above syntax to create a column with the Text.PadStart.

I have used the below formula on my dataset.

Text.PadStart([Month], 10)

Do you notice in the newly created column that all values are 10 characters long? This is because Text.PadStart has added enough blank spaces to the beginning on the string.

The default character is a blank space, but with the ‘character’ parameter being optional, we can also add a character to replace the blank space.

Modify your custom column and add a character after the ‘count’ parameter. I have added a hyphen to my formula, which now looks like the below.

Text.PadStart([Month], 10, "-")

After updating my custom column, I now have the below showing.

You can now see that hyphens are showing instead of blank spaces.

To learn more, please visit Learn DAX