Power BI Intermediate Level: 30 - Importing Files From OneDrive, Teams, or SharePoint

Table of Contents?|?Power BI Report File

Short summary: You can import files stored in OneDrive, Teams, or SharePoint directly using Power BI's web connector. If you import local files from your computer and publish your report online, app.powerbi.com cannot access your local data and thus cannot perform scheduled refreshes. You can easily solve this issue by synching your files with OneDrive and import the synched web versions of your files instead.

Microsoft 365 (previously Office 365) is very widespread among business users so chances are high that you are already using its tools such as Excel, OneDrive, Teams, and/or SharePoint. The latter three tools can be used for storing and sharing files online. They are tightly interlinked:

• SharePoint is the original offering for storing and sharing files online, mostly used by businesses for collaboration.
• OneDrive is an app for also storing and sharing files online. It uses SharePoint under the hood but is easier to use.
• Teams is a collaboration software for chatting, video calls, file storage and sharing, and more. Teams also uses SharePoint under the food for file storage and sharing.

Importing Files From SharePoint, OneDrive, or Teams

You can directly import data from these three services into Power BI and it works in the same way in each case because they are all based on SharePoint. The first thing we need to do is to get the web link to the file in SharePoint. This is not one of the links you get when sharing a file. Instead, a quick way to get the link we need is to open a file from SharePoint / OneDrive / Teams in the Desktop Excel app (not in the browser). Below is an example for opening an Excel file from Teams in the Excel Desktop app:

In Excel, click on the File ribbon, then Info and then click Copy path (not "Copy local path")?.

Next, in Power Bi click on Get data and then select Web.

Insert the link which we copied from Excel into the prompt of the Web connector in Power BI but delete the last part from the web link that says “&web=1”. The link looks like this, note that the data is stored in SharePoint under the hood even though we are using Teams: https://SharePointName.sharepoint.com/sites/TeamsName/Freigegebene%20Dokumente/General/Folders/Filename.xlsx

Then you will be asked to sign in. Sign into your Organizational account (your Microsoft account), you can leave the “Select which level […]” at the default top level setting. For some SharePoint versions, the sign in through the organizational account is not available, in that case you should instead choose Windows and “Use my current credentials”. Finally, click on Connect.

From that point onward, you can use the Power Query editor as normal. The difference now is that the source is a file on the web rather than a local file.

Avoiding Online Scheduled Refresh Issues with Local Files

Let me demonstrate an issue that you get when importing local files, like an Excel file from your laptop. It works fine when doing it in Power BI Desktop but let's look at what happens when you publish your report online.

When trying to set up online scheduled refresh for our report on app.powerbi.com, we get an error message:

Why is it not working online, even though it is working locally on Power BI Desktop? The reason is that app.powerbi.com, being based online, cannot access files on your laptop.

The difficult solution would be to install a Data Gateway app on your laptop which basically forwards the files from your laptop to app.powerbi.com, however this is quite a hassle and requires your laptop to be turned on and connected during scheduled refreshes.

There is a much easier solution that many Power BI users don’t know about: You can synch the file with OneDrive and then import the web version of the file and that way scheduled refresh will work just fine. It's the same procedure as in the above section:

• Open your local file in Excel, go to the File ribbon, click Info, and then Copy path.
• In Power BI, use the Web connector, paste the path, but remove the last part which says “&web=1”
• Build your query in Power Query like you normally would

This is my recommended way to have the report completely web-based. Another advantage is that when co-authoring the report with other people, they too can view the queries with their own login, given they have permission to access the files on OneDrive, Teams, or SharePoint.

For this article series, I'm hosting the sample files on GitHub because it allows for anonymous public access, which SharePoint does not (anymore). So you can use this technique for other web services as well.

Changing an Existing Query from Local to Web

Let's say you have existing queries that you want to change from local to web source without having to redo the queries from scratch. For that, open your query and go to the Advanced Editor under the Home ribbon.

The query code which Power Query generated will look like this:

What you need to do is replace

File.Contents("<local-file-path>") with

Web.Contents("<SharePoint-web-link>")

, where <SharePoint-web-link> is the link which we took from Excel and where we removed the part “&web=1” from the end. Note that you need to use the function Web.Contents instead of File.Contents and you need to put the web link inside the parentheses in double quotes.

Now when you publish the report and reenter the credentials online, you can set up scheduled refresh without any issue.

I know many people who struggled with that topic, including me when I started with Power BI a long time ago, so I hope this article proves useful to you.

Next up, let's learn some intermediate data transformation concepts.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article:?Transforming Columns into Rows - UNPIVOT