A summary of the changes introduced in the September 2019 update to Power BI
Part two of a six-part series of blogs

This month sees the welcome (if belated) introduction of Excel-style custom number formats, as well as more (and easier-to-create) themes and a host of other minor improvements.

  1. New features in the September 2019 update of Power BI
  2. Custom number formats (this blog)
  3. Themes are easier to create (and there are more of them)
  4. More things can be conditionally formatted
  5. Other new features in this update
  6. Features waiting in preview as of September 2019

For a cumulative list of all of the updates to Power BI Desktop in the last few years see this blog, or have a look at the Power BI courses that we run.

Posted by Andy Brown on 20 September 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Custom number formats

A philosophical question: is it sometimes acceptable to look a gift horse in the mouth? 

The problem

For years now I've been whingeing during courses about number formatting in Power BI.  Specifically, I want to be able to create and apply custom formats, like in Excel:

Formatting profit / loss

I want profits to appear normally, but losses to appear in parentheses - so the second number in the selected column should appear as ($17,897,621).

For some time now you've been able to do this to some extent on the Modeling tab:

Modelling tab formatting

You've been able to set the decimal points and the currency symbol in the ribbon above for a good while now.

There's also been an even more restricted set of options in the Field formatting formatting card for a while now:

Format tools

You can specify that an individual column of a table should have a different number format - well, ish.

 

But what I've wanted to do for ages is to be able to apply Excel-style number formatting:

Custom number formats in Excel

This screen shot has probably lost me half my readers, but for the cognoscenti this is the way to set a custom number format in Excel.

 

And the good news is that Microsoft have now supplied this feature.  So why aren't I totally happy?

The solution

To me the obvious solution  would have been to adapt the two existing property areas shown above, but instead Microsoft have added a section to the Properties pane  in Model view:

Model view

You can choose to set a Custom number format by selecting a column and changing its properties.

After choosing Custom, you can type in the formatting code that you want to use:

Custom format code

These 3 codes are provided as defaults. The one shown selected will show positive numbers normally, and negative numbers in parentheses.

 

For those who don't know their custom format strings in Excel, here are a couple of other formats that you could try for dates and times:

Formatting code What it might show
ddd dd mmmm yyyy Thu 19 September 2019
hh:mm:ss 16:02:35

Strangely, you can't use HH in capital letters to use the 24-hour clock and hh in lower case to use the 12-hour clock (unlike in Excel, and most other Microsoft applications).

But the options are a bit hidden away for my liking!  As I say ... is it ever acceptable to look a gift horse in the mouth?

This blog has 0 threads Add post