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. 
  • Compose the link of your Excel File
    Basically the link that is used from Power BI consist of two parts:
    • the tenant part, something like:
    • the folder structure and the file name
    • combine 1 and 2: [1]/documents/[2] and you have the link to your Excel file that can be used from Power BI
  • Connect Power BI desktop to your Excel file 
  • Publish your Power BI desktop file to the service
  • Adjust the refresh schedule using oAuth2