Correctly connecting to a file on SharePoint in Power BI Desktop to avoid refresh errors on Power BI Service

I have seen this issue and helped people fix it over so many years now, that it's time to write an article I can point people to in the future. Here is the issue in a nutshell:

You have a file (maybe Excel or CSV) on SharePoint that you import into Power BI Desktop. That works fine, but when you publish it to the Power BI Service, you can't refresh because of an authentication error.

Sounds familiar? Then read on for the cause of this and the solution!

The cause

I typically see two causes of this error:

1. Local connection to file
2. Connect using the web connector

Let's investigate how these two connection types work and why they fail when applied to files on SharePoint.

Local connection to file

Often people will have SharePoint document libraries synched to their local machine, so when importing an Excel file into Power BI they hit the 'Excel' button in Power BI and navigate to the file.

This will work fine in Power BI Desktop, but as soon as you publish your work to the Power BI Service it will make automatically refreshing the data impossible. It works locally because you have a local copy of the file, and Power BI Desktop uses your user to grab that file.

Once on the Power BI Service, it won't have access to files on your local machine. You can check if you have any connections to local files from 'Data source settings':

If you see a path similar to what's highlighted in yellow typically containing "C:" you have a local connection to a file.

Keeping that local connection will result in the following error when trying to refresh in the Power BI Service:

But when you go to then set the credentials, that setting is greyed out:

Even if you can install a personal gateway and keep your machine turned on (which I'd strongly recommend against doing!), a much simpler way is outlined further down.

Connect using the web connector

This approach is sometimes found around the internet, but I'll still recommend against it as it introduces overhead and potential security risks.

When connecting using the web connector, you will paste the direct link to the file on SharePoint and see the following in 'Data source settings':

This will work fine in the Power BI Desktop but cause issues on the Power BI Service:

This time, it's possible to edit the credentials, but you still get an error message when selecting the OAuth2 authentication method. There are other methods available, but they are not recommended and a better approach can be found below.

The fix

The obvious answer here is of cause to use the SharePoint folder connector:

But that isn't without its quirks, so let's take those!

First, you'll be asked for the URL, but if you provide the full URL to the file, like when using the web connector, this error is thrown at you:

The reason is that you need to connect to the SharePoint Document Library root path. It can be found on the top level of the file structure and will follow this logic:

https://[company_name].sharepoint.com/sites/[sharepoint_site_name]/

Once you connect, you will see a weird view like this after hitting 'Transform':

This is because the SharePoint folder structure is completely gone, and Power BI will only show you the first 1000 files on the SharePoint document library.

If you have less than 1000 files, you can go and search for the file in the 'Name' column and open it up by clicking on the 'Binary' value. This will have Power BI open then file, in this case Excel and you can transform it as needed.

But if you have more than 1000 files, you must filter below 1000 files before the search on 'Name' is guaranteed to find your file. Usually, I use the 'Date created' column for this as that value will not change. You can also remove the filter on 'Date created' once all transformations are done to increase data refresh speed.

When using the proper connector, you can set the credentials using OAuth2 on the Power BI Service and enable automatic refresh:

Switching from web or local to SharePoint connector

If you have found this article, chances are high that you already have your files connected using the web or local connectors and want to switch to the SharePoint connector, thereby not having to set up measures, relationships, security, etc. again. Power BI doesn't have any easy way of changing the data source connector, but here are two methods that will work:

1. Recreate the full query using SharePoint connector and overwrite the old query (low risk)
2. Recreate only the source step and copy it into the existing query (high risk)

WARNING: Make a copy of your PBIX before going further, as you are potentially breaking your Power BI query and there is no CTRL+Z in Power Query

Let's look at the two methods in detail.

Recreate the full query using SharePoint connector and overwrite the old query (low risk)

This approach works well if you have just a few non-complex data transformations in the query and you want to take a low-risk approach. Eg. I have just these transformations in the original local query:

Then you recreate the transformation steps in a new query using the SharePoint connector. The query now looks like this:

As you can see the source part (lines 1 to 4) is very different, but the applied transformation steps after are the same. Now copy over the code using the 'Advanced Editor' and the source connector is changed in the original query while keeping the transformations, measures, relationships, etc. intact! Once you have the code copied over, you can disable or delete the new query that you copied from.

Recreate only the source step and copy it into the existing query (high risk)

This method is high risk, as each transformation step in the M language calls back to the previous step, therefore it's easy to break the code. This method is best used if you have many and/or very complex transformations (eg. merge or append) that you don't want to recreate manually.

Like before make a new query using the SharePoint connector, but this time stop after the initial file load. The code will look like this:

The last step above is loading the table from the sheet so that no transformations are done, it's important that you don't do any transformation, as transformation names must be unique which can easily cause errors.

Now you must copy over only the code before the blue 'in' statement into the original query, overwriting the source part:

The part marked in green must be overwritten, but remember to keep the comma at the end (highlighted in yellow). The merged code will look like this:

Pay attention to the highlighted code on lines 5 and 6, as those need to match before the code will work because of transformation steps each calling the preceding one.

With that in place, you have switched from a local or web connector to the proper SharePoint one, while still using the original query thus keeping measures, relationships, security, etc. intact in your Power BI semantic model!