Episode 163 – Blame Excel!

This is episode 163 recorded on October 09, 2020 where John & Jason talk about how not to use Excel properly (listen up UK government..) and the role of Excel in Power BI today and in the future.

Show Notes

Why you should never use Microsoft Excel to count coronavirus cases

Ideas Picks

John

Change the “file based” connectors in Power BI Destop to be container based

Today in Power BI Desktop, when the “Get Data” dialog is invoked, the first 6 connection options are based on the local file system of the user running Power BI Desktop (Excel, Text/CSV, XML, JSON, Folder and PDF). The first two are likely the most common, and if the user is pulling data from Excel, they’ll pick the Excel connector. They then need to locate the file on the local file system and build their report.

The problem is, that without a gateway, this report will not be refreshable in the service. Even if the URL to a JSON file is selected, the file is first downloaded behind the scenes and the local cache is used as a data store. The workaround is to use a web connector, the SharePoint folder, or other connector depending on the location.

I think that the behaviour of these file based connectors should be changed – they should not assume that the file is stored locally, but should prompt the user first. For example, if Excel is chosen, the user could be prompted first for the type of storage location (for example, File System, Web, or SharePoint). Once chosen, the appropriate connector could be invoked.

This has the potential to reduce a lot of confusion and frustration when the ultimate report is published.

Jason

Connect to Dataflows from Excel’s Power Query

Please allow the connection to Dataflows’ entities from Excel’s Power Query.
There are many cases when customers use Excel and not Power BI. But Dataflows can make the refresh process much faster for them.
Thanks