This post is also available in:
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.
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.
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!
