Forum Discussion
Adding Leading Zeros in Power Query
How can I add leading zeros in Power Query only if a certain criteria is met. For example, I have a column where the number of digits can range from 10-13, but all of them need to be 13 digits. So if the field contains 10 digits, I would need to add 3 leading zeros. If it contained 11 digits, I would need to add 2 leading zeros, etc.
Thanks!
PowerBI123456 , then you reference a specific column in the formula
Table.AddColumn(#"Previous step", "Leading 0s", each Number.ToText([number column], Text.Repeat("0",13)))
5 Replies
Hi, PowerBI123456 , you might want to follow this pattern to format all numbers,
Number.ToText(1234567890, Text.Repeat("0",13))CNENFRNL Thanks, what if I want to refer to a specific column?
PowerBI123456 , then you reference a specific column in the formula
Table.AddColumn(#"Previous step", "Leading 0s", each Number.ToText([number column], Text.Repeat("0",13)))
If you need to keep them as number format, you can also use Custom Format Strings after you load the data.
Use custom format strings in Power BI Desktop - Power BI | Microsoft Docs
Regards,
Pat
