Here’s a summary of the updates Microsoft released for Power BI Desktop this month. Full details on this months updates can be found on the Power BI blog.
The feature we’ve picked out as the highlight for this month is Data Profiling in Query Editor. Watch the below video to see this feature in action, the video will automatically start and end in the relevant places.
This feature consists of two sub features; Column Quality and Column Distribution.
Column Quality provides us with an indication of how good the data is that we’re working with in each column. Percentages are shown for values in the column that are Valid, Empty or in Error. This information can either be displayed as a narrow multi coloured stacked bar or as a small card. In the example below we can see there’s a potentially quite serious issue with the ‘R&D Hours’ column as 98% of values in that column are Empty (so null values). Notice the narrow multi coloured stacked bar for this column is green to start with but soon turns to a dark grey.
On hovering the mouse over the narrow multi coloured stacked bar we see record counts as well as percentages for Valid, Error and Empty values. We also have the option at this point of quickly removing Empty/Error values – but do bear in mind doing this removes the entire row containing Empty/Error values in that column, so may end up removing rows you don’t want to.
Column Distribution allows us to see the distribution of unique values in a column. This is shown in the examples above as a column chart with green columns that read left to right. This sub-feature will be interesting from many perspectives including Master Data management. You may for example see that part way through the column we start seeing a number of new unique values appearing. This may highlight the introduction of some new Master Data values (new Products for example). This may genuinely be as a result of some new Product launches, or perhaps it may highlight a problem such as someone selecting an old redundant Product or someone mis-spelling Products on placing orders.
One thing we have noticed is that the Column Distribution will only work with columns that have been assigned a Data Type on the Transform ribbon in the Query Editor. Any column set as Data Type ‘Any’ will not display a Column Distribution chart.
We’ve also noticed that the Data Profiling (both Column Quality & Distribution) doesn’t update when you use Column Filtering in Query Editor. In the below example I’ve filtered out all Null values from the ‘R&D Hours’ column, but notice the Column Quality sub-feature is still showing 98% of values are Empty.
As always there were many great features announced this month, and some will be more useful to some than others. And with that in mind, if you have any thoughts on this then we’d love to hear from you.
As always do feel free to post any questions you have below.