BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- New features in the September 2019 update of Power BI
- Custom number formats (this blog)
- Themes are easier to create (and there are more of them)
- More things can be conditionally formatted
- Other new features in this update
- Features waiting in preview as of September 2019
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?
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:
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:
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:
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:
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?
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:
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:
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|
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?