Recent developments in Excel and Power BI
The world may have gone into lockdown, but Excel and Power BI have run riot in the meantime. Are you aware of what’s been going on? Liam Bastick summarises some of the latest developments.
Covid-19 apps available in Power BI
I thought I might start with the elephant in the room. You cannot take a step outside your front door before the world reminds you of the Covid-19 pandemic. As healthcare providers and local governments work to slow the spread of the virus and ultimately save lives, it’s critical that these organisations are equipped with accurate information in a timely manner.
To help these organisations and individuals stay informed, several of Microsoft’s Power BI partners have created template apps tracking the Covid-19 pandemic, eg
- Covid-19 US tracking
- Covid-19 daily cases
- Covid-19 global cases.
These are now available in Power BI. After you download them, you can use them as is, personalise them or share them inside your organisation. All the following template apps connect to anonymous data sources.
Covid-19 daily cases
This Covid-19 report, which was developed by Pragmatic Works, provides information on daily cases, recoveries and death rates by country and state/province from the Center of Disease Control (CDC) and John Hopkins University.
Analyse Covid-19 global cases
With DataChant’s latest template app, you may analyse Covid-19 global cases in a daily refreshed report, which includes advanced time intelligence measures to offset all countries by the number of estimated active cases per capita since the first day that 100 cases were reported. You can also hit the play icon to see the animated progression over the last 30 days if you really want to scare (or reassure) yourself.
Coming Soon: Power BI Datasets in Excel
Last week it was announced that Microsoft is introducing two new ways to access Power BI data from within Excel:
- Excel Data Types
- through PivotTables connected directly to Power BI.
It’s been a common gripe amongst users for quite some time as to how to get Power BI datasets (back) into Excel. Analyse in Excel may be one way, but now with the click of a button, users may now discover Power BI datasets without having to go via Power BI. By using Power BI certified and promoted dataset capabilities, users now have easier access to relevant and refreshable data.
Using Data Types, you can quickly find details from featured tables in Power BI datasets within Excel’s data types gallery. Data Types gives you the flexibility to organise information in any way you want within the Excel grid. This allows you to use formulae, build reports and analyse your data in the good ol’ fashioned spreadsheet way.
You may also analyse Power BI datasets in Excel using PivotTables:
As well as Data Types, Microsoft has also added the ability for PivotTables connected to datasets stored in Power BI to be created in Excel, allowing you to continue to work in Excel. This new capability creates a live connection to the underlying dataset in Power BI, meaning updates within the source are published to the reports connected to it.
It should also be noted that Excel PivotTables also respect the Microsoft Information Protection (MIP) Sensitivity labels applied to the file when the PivotTable was created. Therefore, since Excel may now promote the adoption of certified datasets within your organisation, this can help with version control surrounding your data, when making critical decisions in your business.
These new capabilities will start rolling out to Office Insiders later this year. Watch this space.
Coming Soon: Microsoft Information Protection (MIP)
Not only available in Excel, but also in both Power BI and other Office products, Sensitivity labels from the Microsoft Information Protection framework are already available, allowing you to classify and protect your organisation's data whilst making sure productivity and collaboration are unhindered.
These labels will persist when applied from Power BI content and exported to Excel, PowerPoint and / or PDF files. However, this feature will shortly take a step further. When you connect to a Power BI dataset from Excel, that dataset's Sensitivity label will be inherited and applied to the Excel file and all associated outcomes, such as headers, footers and encryption.
This new, more rigorous integration between Excel and Power BI will provide users with more information protection in Excel’s familiar and easy to use interface, all with fewer clicks.
This update will start rolling out to Office Insiders later this year.
You might also be interested in
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...