• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Custom Formatting Strings in Power BI

You are here: Home / Power Pivot / Custom Formatting Strings in Power BI
custom formatting strings in power bi
January 28, 2021 by Philip Treacy

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

Enter your email address below to download the sample files used in this post.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Power BI Desktop File.

Excel Workbook with sample data. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

 

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.

date example

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)

number format dialog

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

custom format dialog

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

custom date format

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

sample data

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.

data table in data model

Phone Numbers

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

number 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.

default format list

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

phone number format

and the numbers are formatted in that way.

formatted numbers

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.

formatted cell numbers

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.

whole numbers

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

sum whole numbers

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

correct negative numbers

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.

hide negative numbers

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.

default decimal numbers

default decimal formats

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

custom decimal formats

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

 

using symbols

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

date format

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.

other currency symbols

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

AUD currency symbol

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.

custom format in model view

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.

custom format in report view

custom formatting strings in power bi

More Power BI Posts

combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
try otherwise power query iferror

IFERROR in Power Query Using TRY OTHERWISE

Using TRY..OTHERWISE in Power Query Replicates Excel's IFERROR So You Can Trap and Manage Errors In Your Queries.
easily compare multiple tables in power query using list functions

Easily Compare Multiple Tables in Power Query

Compare tables or lists in Power Query using List Functions. This method is great when dealing with 3 or more tables or lists.
fuzzy matching in power query

Fuzzy Matching in Power Query

Use fuzzy matching to compare non-identical text strings and match them together based on how similar one string is to the other.
handling http errors in power query and power bi

Handling HTTP Errors in Power Query and Power BI

Clearly communicate issues with custom messages when dealing with web scraping or API server errors. Download sample Excel and Power BI files
extract characters from strings in power query using text select and text extract

Extract Letters, Numbers, Symbols from Strings in Power Query with Text.Select and Text.Remove

Learn a cool technique to extract or remove letters, numbers and special characters from strings. Sample workbook to download
highlighting data in power bi visuals

Highlighting Data in Power BI Visuals

Learn several techniques to highlight or label important data points in your Power BI visuals. Sample file and code to download.
shape maps in power bi

Shape Maps in Power BI

Shape maps in Power BI can be used to show the distribution of a variable across geographic regions. Learn a trick to plot discrete data too.

Converting Decimal Time to Days, Hours, Minutes, Seconds in Power BI

Convert times and durations from decimal numbers to easily understood formats like hh:mm:ss. Sample code and file to download.
sort by column in power bi

Sort-By Columns in Power BI

Create a Sort-By column to allow custom sort order in your Power BI Visuals. Download an example Power BI Desktop file

More Power Pivot Posts

CUBE Functions

Excel CUBE Functions

Excel CUBE Functions are a great alternative to GETPIVOTDATA for Power Pivot PivotTables and still work with Slicers.
Introduction to DAX

Introduction to DAX Measures

Introduction to DAX in Power BI and Excel. Where and how to write DAX measures, understanding filter context and more.

Toggle Top N with Slicers

Toggle Top N with Slicers using disconnected tables in Power Pivot. These easy measures allow users to choose their top n.
power pivot running total

Power Pivot Running Total

The defaul Power Pivot Running Total setting doesn't handle aggregated periods. This measure solves this issue.
static data tables

Static Tables in Power Query, Power Pivot and Power BI

Use static tables to store data in Power Query, Power Pivot and Power BI without needing to load data from an external source
custom formatting strings in power bi

Custom Formatting Strings in Power BI

Control how data is displayed in Power BI using your own formats. Like hiding negative or zero values, using symbols or custom number formats
GETPIVOTDATA Function for Power Pivot

GETPIVOTDATA Function for Power Pivot

The GETPIVOTDATA Function for Power Pivot references measures and so it works a little different to GETPIVOTDATA for regular PivotTables.
values_as_%_of_another_column_thumb

Show Values as Percentage of Another PivotTable Column Total

Show Values as % of Another PivotTable Column Total can't be done with regular PivotTables, however with Power Pivot we can use a DAX measure.
Change PivotTable Aggregation Methods using Excel Slicers

Change PivotChart Aggregation Methods using Excel Slicers

Change Pivot Chart Aggregation Methods using Excel Slicers and Power Pivot. Disconnected tables and the SWITCH function are the secret sauce.
PivotTable Distinct Count

Excel PivotTable Distinct Count

Excel PivotTable Distinct Count, sometimes called ‘Unique Count’ will return a count of unique items in a field. It uses the Data Model (Power Pivot).
Category: Power PivotTag: Power BI
Previous Post:Highlighting Periods in Excel Charts
Next Post:Personal Stock Portfolio Dashboardpersonal share portfolio dashboard

Reader Interactions

Comments

  1. Bundu Dumbuya

    February 5, 2021 at 3:31 am

    Hi Philip,

    How did you remove the blank spaces between the phone numbers using Power BI?

    Best regards
    Bundu

    Reply
    • Philip Treacy

      February 5, 2021 at 1:08 pm

      Hi Bundu,

      In Power Query, use Replace Values and replace a space character with an empty string. If you open the sample PBIX file you’ll see the step in PQ.

      Regards

      Phil

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x