Sure, you may say the combination of OneDrive / Excel is
not a data source. Personally from a certain point of view, I would call OneDrive or dopbox or any other file service a container that can hold various objects. One of my favorite objects is a
simple Excel file. Once again you are correct, Excel is not a data store that is able to solve all your Big Data problems not to mention all Data Quality issues that may arise due to the lack of
data types, but for certain tasks the combination of OneDrive and Excel has its merits. On the one hand I'm able to use Excel in OneDrive as a data sink for many Microsoft Flow triggered data
flows, or I can use the simple Survey as I do here.
In addition to that it's very easy to connect Power BI to an Excel file as a simple data source (meaning I do not want to import a Power Pivot data model into the Power BI service and I also
do not want to import Power View Reports or Excel Reports into the Power BI service). But as easy it really is, it is also somewhat obscure, for this reason here I want to show how you
connect Power BI desktop to your Excel file in OneDrive:
- Locate your Excel file in your OneDrive folder (online, not on your desktop)
By locating your Excel file I just mean note the folder structure where you have stored the file.
the link of your Excel File
Basically the link that is used from Power BI consist of two parts:
the tenant part, something like: yourname-my.sharepoint.com/personal/you_yourdomain_domainsuffix
the folder structure and the file name
combine 1 and 2: /documents/ and you have the link to your Excel file that can be used from Power BI
Connect Power BI desktop to your Excel file