This post is also available in: German

Hits: 25723

In this short article, I’ll show you how to use Power Query and Power BI to populate values with leading zeros. The original post from 3 March seems to be deleted, so I publish here again a short version.

starting position

In the picture below you can see that the values in the “Region ID” column have a different length. The goal is that all values should be exactly 7 digits long. Missing values must be added at the beginning of the text.

44/5000 Values of different lengths
Values of different lengths

 

 

 

 

 

 

 

 

The goal is to realize in a simple way. In Power Query and Power BI we need the command Text.PadStart. If you enter the command =#shared in an empty query, you can find the command description.

The description states that a text value should be padded to a predefined length with a character to be specified at the beginning of the text. So in our example: [RegionCode], 7, “0”.

 

Create a new column using the following code, which you can enter directly in the editor:

The result looks as desired.

Führende Null - Resultat

Ending Zeros

If the text should be padded with zeros at the end, then use
Text.PadEnd instead of Text.PadStart. The rest stays the same.

 

Did you like the post? Then leave a comment or “like” it. Thank you.
You can find more articles about Power Query in the
Power Query Online Buch.

[yasr_visitor_multiset setid=2]

Happy Querying!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: