In a recent release of Power BI, I was really excited to see that the team had created the ability to pin an Excel workbook to a Power BI Dashboard. I have been waiting for this and it was high on my list of things I missed most versus SharePoint Enterprise. However when I tested it out this week, I was actually pretty disappointed.
First, how to use this feature
To use this new feature you must have OneDrive for Business – this wont work with OneDrive Personal (not yet anyway).
Go to the cloud version of Power BI in a browser and log into your account. Then click Get Data as shown below.
Select Get Files (shown below), and when prompted, select import from OneDrive for Business.
Once you see your OneDrive for Business files, select the file you want to connect to Power BI and click Connect (as shown below).
This is where you get a choice of how you connect (different from OneDrive Personal). If you want to view your Excel spreadsheet reports, Pivot tables and charts in Power PI, then you should take the second option as shown below.
You will then see the workbook in the Reports list on the left – with an Excel Icon indicating you can view the actual workbook.
When you select the report (1 below), you can then see and interact with your spreadsheet in the main window (2 below), and there is the all important “Pin” button (3 below).
You can then highlight any range on the workbook and pin it to a Dashboard by clicking the Pin button. Once you do this, you can see that same range on a dashboard – like shown below.
But there are a few significant problems
Firstly this is a static snapshot of the workbook. If you click on the slicer, all that happens is that you are taken directly to the Excel spreadsheet via hyperlink. Once you arrive you are presented the entire Excel Workbook – as if you were in Excel Online.
Secondly if you make any changes to the workbook before you click “Pin”, such as click on the slicer to select a new value, you can no longer pin the workbook. It only allows you to Pin the workbook if no interaction or changes have been made since the page was loaded.
Finally the performance was very poor during my testing. I uploaded a small 4MB Adventure Works test workbook, and the pages were very slow to load and refresh – certainly nothing I would put into production.
Microsoft, Please Give Me a Web Browsing Experience
What I really yearn for is a “Web Browsing Experience”. This is a concept that I have talked about many times, and covered in my PASS BA Conference presentation about SharePoint in April 2015 (you can watch that presentation here on my YouTube Channel). I strongly believe that when it comes to Cloud Based BI Reporting, we do not need Excel Online. What we need is a clean Web Browsing experience where users can navigate and browse the dashboard without thinking about the process and definitely not thinking about Excel. We should allow report readers to just consume the information – like they would if they were at any other well designed website. So if they see a slicer (like above), then they should be able to click on the slicer and interact with it. I never want the standard behaviour to be “Launch Excel Online” when in a Reporting Dashboard. That option may add value in some instances, but it should definitely not be the main behaviour for reports and dashboards.
So please Microsoft, take a look at the way we can currently configure SharePoint Enterprise with controlled pages and Web Parts (Here is a link to a sample SharePoint Site that demonstrates that behaviour). Allow us to link any range from our workbooks into reports and dashboards, and make those ranges fully interactive windows back to the core workbook without having to Hyperlink back. Maybe that is on the plan already – maybe not. Either way, this is back at the top of my list of things I would like to see in Power BI.
Please vote to have this improved here https://ideas.powerbi.com/forums/265200-power-bi/suggestions/11139741-interactive-pinned-excecl-ranges-in-reports