John White on 07 Oct 2020 13:09:41
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.