Power BI and SharePoint: terrible together

Until recently, you hadn't spent a lot of time building Power BI reporting solutions that SharePoint Online as a data source (This article refers specifically to SharePoint Online, but I'm sure the experience is the same with SharePoint on-premises.) For the last month I have been working with some clients using SharePoint for storing source files in document libraries. At first glance, it seems like a great idea to leverage SharePoint as a storage location for CSV and Excel files.

• Everyone has easy access to the files for editing and storage.
• SharePoint manages version control, check in, check out, etc.
• SharePoint can facilitate shared editing of files
• You can create a Power BI report that will update online without the need to install a gateway.

Unfortunately, despite the benefits, the experience is not great. The performance of Power BI with SharePoint as a data source is just terrible. Ultimately, the issues come down to performance in 2 areas.

Problem 1: update speed

First of all, there is the update performance. SharePoint has an OData API that is not very powerful (actually, the same is true for most OData APIs). When you try to update in SharePoint, you will find that the update will be slow. That doesn't mean it's not workable, because the update happens in the background, after all. In my hands-on and testing experience, SharePoint upgrade performance drops rapidly as soon as you create a series of subfolders in a document library. In other words, if you have your files in the main folder of your document library, the performance is not that bad, but as soon as you create a few subfolder branches, the performance drops quickly. A Power BI workbook with multiple sources seems to take 4-5 times longer to update compared to the same workbook pointing to a file server.

Problem 2: Speed of editing / maintaining queries

This second problem is much more important than the first. When you have many SharePoint connections in your PBIX file, the query editing experience is unfortunate. Power Query has a few tricks to improve the experience, such as keeping a cached copy of the data for preview purposes. Despite this, I have found that editing queries connected to SharePoint is so slow it's literally impractical compared to working with local files on your PC.

Actually, there is a setting in Power Query that you can modify that can improve the situation a bit.

Go to File Options and Settings Options

Go to current file data upload (1 below) and disable background data (2 below).

It's definitely worth doing, but in my experience, it didn't solve the SharePoint performance issue. I wasted so much time trying to build queries with SharePoint connections that I just had to find another way.

Synchronize local files instead

The best alternative I could think of is syncing the SharePoint files to a file server using OneDrive.

1. Use OneDrive to sync a copy of your SharePoint files to a file server that can be accessed through a gateway. This can be the same server used for your gateway or a file server on the network.
2. OneDrive maintains a local copy on the file server in sync with SharePoint.
3. Upload files from local PC instead of SharePoint.

This is the best option but it comes with some new problems.

• One of the benefits of storing your files in SharePoint Online is that you don't need a gateway. If you take this approach, you may need to organize a gateway installation.
• If you replicate the files on the gateway machine, you will probably need to have a different file location on your PC for development and then change the file path when you deploy it.
• Third, there is an issue (if not a bug) where you cannot connect Power Query to the root folder of a synced OneDrive folder from SharePoint.

for example, if you sync SharePoint with your PC as

C: Users R Marketing R Marketing Pty Ltd R Marketing Pty Ltd Team Site - Documents

you can connect Power Query to
C: Users R Marketing R Marketing Pty Ltd R Marketing Pty Ltd Team Site - Documents Subfolder

but you can't connect to
C: Users R Marketing R Marketing Pty Ltd R Marketing Pty Ltd Team Site - Documents

Just keep that in mind: I wasted an hour or so trying to figure out why it didn't work.

Also note: if the files are to be stored on a file server, you must use the full network path to the files, not the mapped drive version. For example something like \ file server department folder name instead of n: department folder name

There's another way

There is another way to solve the SharePoint problem. That's a broader topic and I'll cover it next week. For now, I just wanted to assure you that "it's not just you."