Custom Formatting Strings in Power BI

You may be familiar with Custom Format Strings in Excel, if you are not then check this Excel Custom Number Format Guide.

What you might not know is that you can also use custom number formats in Power BI, and not only can you format columns of data, these custom formats can be applied to measures too.

Download PBIX File and Workbook

 

Quick Refresh - What's a Custom Number Format?

A Custom Number Format is where you can tell Power BI (or Excel) how you want a number displayed. The underlying data does not change, just the way you see it on screen.

A perfect example is a date. In Excel dates are represented as serial numbers. The number 44553 is Excel's serial number for 23 Dec 2021.

If I enter this date into a sheet and then click on the Number Format drop down, you can see in the list of pre-defined formats, that the (serial) number for this date is 44553, and you can also see I can choose from two date formats.

At the bottom of the list I can also click on More number formats to bring up the Number Format Dialog. You can also see this by using the shortcut CTRL+1 (see more Excel Keyboard Shortcuts)

I can choose from several pre-defined formats for my date, or I can click on Custom, and enter my own format.

If I want to just see the month and year displayed as Dec 21 I can enter the format string mmm yy

In Power BI you can do the same formatting of dates and numbers. The only thing that does not work in Power BI is applying color to the value, for example by using [Red] to make it red.

If you want to change the color of the font in Power BI visuals, use Conditional Formatting.

Custom Formatting in Power BI

Starting with a table of data like this in Excel

I import this into Power BI Desktop and then have to do a little tidying up in Power Query.

The columns for Phone and Mobile (Cell) number are imported as text because they contain spaces. So I have to remove those spaces and convert the columns to Whole Number.

With the other columns, if they haven't been automatically set as the correct data type, it's just a case of doing this.

In the end I get this table of data in my Power BI Data Model.

Phone Numbers

Let's start with the phone numbers. The first thing to check is that they are the correct Data type

I've already set this in Power Query so I don't need to make any change, but if your phone numbers are set as Text, change them to Whole Number.

Domestic land line numbers in Australia take the format 00 0000 0000 so to display the phone numbers in this way that's exactly what you type into the Format area in the Ribbon.

If you click on the Format drop down list you are presented with the default formatting types, and it would appear that this is all you can do, but it isn't.

Just type 00 0000 0000 into the Format box and hit Enter

and the numbers are formatted in that way.

Similarly for the mobile (cell) phone numbers, which are in the format 0000 000 000 in Australia, just select the Mobile column and enter 0000 000 000 into the Format area.

Whole Numbers

There is a default option in the Format drop down list to format as Whole Number but with my column of numbers it does nothing special. I want to format things a bit differently.

As in Excel, you format numbers in Power BI using a Format String. The structure of this string is like this : Positive Format;Negative Format;Zero Format;

So if I format the Whole Number column like this 00;00;00 it means two things. Firstly that I want two digits for each number - note the last line where the value 08 has a leading zero.

The second (and more dangerous thing) is that I've formatted my negative numbers to not display as being negative.

But they definitely are still negative as you can check by creating a visual and summing the values.

Be careful though, the sum of the values is actually -60, but because I've formatted values to not display as negative, the sum appears to be positive 60. Make sure you get your negative number formats correct.

Changing the format back to 00;-00;00 makes the numbers look as expected

Now for some reason let's say you wanted to not display negative values you can do this with this format string 00; ;00;

NOTE : In Excel if you omit one of the formatting components then that component is not displayed. To not display negative values you would use 00;;00; but in Power BI you must use a space to indicate you don't want that particular format component displayed.

It follows that if you wanted to hide all values use ; ; ;

Decimal Numbers

The default Decimal Number format gives my numbers 2 decimal places. I can change the custom formatting as I did with the whole numbers, or I can adjust things like the number of decimal places using the Ribbon.

But if I want to add leading zeroes I can use this format string 00.00;-00.00;00.00

Using Symbols or Text

You can use symbols or text in formats. If you are just using a single symbol/character then precede it with a backslash. If you need to use more than one symbol/character then enclose the string of characters in double quotes.

If I wanted to use up and down arrow symbols to indicate positive and negative values these format strings are equivalent

   \▲0.00;\▼0.00;0.00

   "▲"0.00;"▼"0.00;0.00

 

Dates and DateTimes

Power BI/Power Pivot actually only has one date/time data type : DateTime. Any date columns you have are DateTime with a Time of midnight, it's just that the time component isn't displayed. Change your Date column to DateTime to see for yourself.

There are myriad default formats offered for Date/Time but should you wish to create your own, you need to use the standard representations for formatting date and time, for example mmm would give you Jan for January and yy would give 21 for the year 2021.

See this Custom Number Format Guide for more.

So if I just wanted to see the 3 letter abbreviation for the month and 2 digit year, for any date the format string would be mmm yy

Percentages

Percentages are just a decimal number but setting the data type to Percentage sets the numbers to display 2 decimal places and end with the % symbol.

As with the other number formats, you can modify percentages to display as you wish.

Currency

Currency is represented as a fixed decimal number and there is a default format in Power BI for it and by default it will use the currency symbol for your local region.

You can also choose other currency symbols from a list in the Ribbon.

As a lot of currency symbols are the same for different currencies e.g. $ for US dollars and Australian dollars, to distinguish those I can use a custom format like " $"0.00" AUD" which displays my currency values like this

Where to Apply Formatting

All of these examples have applied the formatting in the Data View, but you can also apply custom formatting in the Model View and Report View.

In Model View, select the column you want to format. Under Properties, open the Formatting section and select Custom from the Format dropdown, then enter your custom formatting string.

In the Report View. In the Fields pane, click on the column you want to format then enter the custom string into the Formatting section of the Ribbon.