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.
Why you should never use Microsoft Excel to count coronavirus cases
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.
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.
One Reply to “Episode 163 – Blame Excel!”
Hi Jason. We have E3 and I need to pull the PBI dataset from the service into Excel and present it as a table, not pivot table. You said Excel’s new data types will help with that? How will that work?