How To Add Leading Zeros In Power Bi

Question: How to add leading zeros in Power BI?

Answer: To add leading zeros in Power BI, you can use the “Format” option in the query editor or the “Calculated Column” feature. Here’s how you can do it:

Using the Query Editor:

1. Open Power BI Desktop and load your dataset.
2. Select the column you want to add leading zeros to.
3. Go to the “Transform” tab and click on “Format”.
4. In the “Format” dialog box, select “Custom” from the category dropdown.
5. In the “Type” field, enter the required number of zeros followed by the format code for your column type. For example, if you want to add 3 leading zeros to a text column, enter “000{0}”.
6. Click “OK” to apply the formatting.

Using Calculated Columns:

1. Open Power BI Desktop and load your dataset.
2. Right-click on the table name and select “New Column”.
3. In the formula bar, enter the formula to add leading zeros. For example, if you want to add 3 leading zeros to a numeric column named “NumberColumn”, use the following formula: LeadingZeros = CONCATENATE(REPT("0", 3 - LEN([NumberColumn])), [NumberColumn]).
4. Press Enter to apply the formula.

Example:

Let’s say you have a dataset with a numeric column named “ID” containing values 1, 15, and 250. To add 2 leading zeros to this column using the calculated column method, follow these steps:

1. Create a new column named “FormattedID”.
2. In the formula bar, enter the following formula: FormattedID = CONCATENATE(REPT("0", 2 - LEN([ID])), [ID]).
3. Press Enter to apply the formula.

After applying the formula, the “FormattedID” column would contain values 001, 015, and 250.