Connecting to Excel Files: SharePoint Folder vs Local Connection (C:) with On-Premise Gateway

[Anonymous]

Does anybody have any recommendations for the best way to import Excel Files to Power BI Desktop?

Currently, my PBIX file is connected to about 25 Excel files that are locally stored on the C: drive of my dedicated Azure Virtual Machine with an On-premise gateway in place. These Excel files are also synced to my OneDrive account and my PBIX file is fairly slow (roughly 45 mins to refresh all data sources).

A Power BI support agent recommended I switch these to a single connection to the SharePoint folder that these are stored in, citing that connection to the cloud would be faster than connection to my VM, but this article by Matt Allington said that the connection between Power BI and SharePoint is terrible.

Does anybody have any experience or recommendations for which would result in faster speed?

See below for an example of the Source Code as seen from the Advanced Editor.

Local Connection with On-Premise Gateway

let

Source = Excel.Workbook(File.Contents("C:\Users\xxxxxxxx\OneDrive - xxxxxxxxxxx\PowerBI - Documents\Sourcefiles\Customers.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

SharePoint Connection

let

Source = SharePoint.Files("https://xxxxxxxx-my.sharepoint.com/personal/xxxxx_xxxxxxx_onmicrosoft_com", [ApiVersion = 15]),
#"Customers xlsx_https://xxxxxx-my sharepoint com/personal/xxxxxx_xxxxxxx_onmicrosoft_com/Documents/PowerBI - Documents/Sourcefiles/" = Source{[Name="Customers.xlsx",#"Folder Path"="https://xxxxxxx-my.sharepoint.com/personal/xxxxxxxxx_xxxxxxx_onmicrosoft_com/Documents/PowerBI - Documents/Sourcefiles/"]}[Content],
#"Imported Excel" = Excel.Workbook(#"Customers xlsx_https://xxxxxxxx-my sharepoint com/personal/xxxxxx_xxxxxxx_onmicrosoft_com/Documents/PowerBI - Documents/Sourcefiles/"),
Sheet1_Sheet = #"Imported Excel"{[Item="Sheet1",Kind="Sheet"]}[Data],

[Gamdalf]

Are you able to store the information in a dataflow instead, as that can handle the brunt of the refresh burden that you mentioned doesn't matter

After which you can query that to the dashboard - should be significantly faster!

[v-lionel-msft]

Hi @Anonymous ,

Or you can consider Matt’s suggestion to store the data source files on the server where the gateway is located? 

But don't put the gateway and data source files in the VM, this does not help much to improve performance.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

I actually already have the data source files on the server where the gateway is located, which is on the VM. I have dedicated the VM for this project, but the PBIX file is still quite slow.

[parry2k]

@Anonymous I would use Sharepoint even if it is slow if files are not changing frequently and refresh can happen in the background. It eliminates the management of Data Gateway and removes that one layer from the equation. Maybe during development, you can use an on-prem excel file (faster to do that way) and once development is done, switch to Sharepoint, you can easily achieve this by adding parameters to check if you are in development mode or in the production mode.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

I don't mind having the Gateway, as it is on the dedicated VM and it's already set up, but do you think it may slow down the process at all?

I think you may be right with your suggestion that on-premise excel files are faster for development while SharePoint is faster for post-development.... but we have been in development for many months and this client continues to want more and more features, so it seems we may never be done with development haha.

[selimovd]

Hey @Anonymous ,

I agree with Matt, the performance to Sharepoint is not good.

How fast do the files change? And how many times per day do you want them to be loaded?

If you just refresh a few times per day, I would store them in Sharepoint and load them with dataflows. Like this you don't really mind if it's slow, because it's loaded anyway in the Power BI Service. And the connection to the loaded table of the data flow is super fast. And you won't need the VM with gateway as everything is in the cloud.

See also the documentation:

Introduction to dataflows and self-service data prep - Power BI | Microsoft Docs

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Files are changing very frequently. Most of the 25 update 12 times per day each during working hours.

I also have them set to refresh 8 times per day, per the maximum on a Pro account.

I'm not too concerned with the refresh speed, my main concern is the speed of the PBIX file when I try to edit queries, but it seems like a local connection is the fastest option for this, correct?