Forum Discussion
Post PartisanAdding 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
CNENFRNLCommunity Champion
Hi, PowerBI123456 , you might want to follow this pattern to format all numbers,
Number.ToText(1234567890, Text.Repeat("0",13))
PowerBI123456CNENFRNL Thanks, what if I want to refer to a specific column?
- CNENFRNL
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)))
- mahoneypat
Microsoft Employee
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
