Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Connectivity: connecting to Excel file: Sharepoint folder vs Web

Hi, 

 

I am working on a dsahboard that uses an Excel file stored on sharepoint as a database, and I was wondering about any experiences that would lead you to favor using the Sharepoint Folder connector or rather the Web connector in sharepoint.

To me it seems like web is sometimes a bit faster, but are there any large differences between the two that create advanages/disadvantages for either of them ?

 

Best, 

Julius

8 REPLIES 8
KNP
Super User
Super User

Hi Julius,

 

Did you come to any conclusions regarding web vs sharepoint?

I'm wondering the same thing, only potential negative I can see with the Web.Contents method is when it comes to credentials on the service, e.g. SharePoint you set credentials once, Web for each call.

I'm keen to hear your thoughts.


image

 

 

 

 

 

 

 

Regards,

Kim

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Do you expect to load only a single Excel file from Share Point?

If not, I would start with the Share Point Folder connector, apply a filter and load the specific files.

If you have only one file and want a quick solution, keep using the Share Point Folder connector, and filter the folder to get the specific file, then apply drill down to import the Binary of the Excel file. Power Query will automatically identify that this is an Excel and will enable you to do the transformations on the Excel if needed.

 

If you have some time and would like to do it more efficiently, I would use the Web Connector and get the file URL following these steps (I published it in detail in Chapter 8 of my book, Collect, Combine and Transform Data Using Power Query in Excel and Power BI).

 

Step 1: Go to the Share Point folder and open the Excel file in your browser (Excel Online):

2020-02-24_11-49-52.png

Step 2: In Excel desktop, go to File tab and click on the file path. Then select Copy path to clipboard.

2020-02-24_11-53-04.png

Step 3: Now, in Power BI Desktop, select Get Data --> From Web and paste the path. Next, delete the suffix "?web=1"

2020-02-24_11-53-44.png

Hope it helps,

Gil

Hi, Apologies for bumping anold thread but I had a web.contents set-up like this which has suddenly stopped working and I cannot reinstate as it doesn't recognise it as an excel file. Do you have any idea why this may be? Has there been a change?

Anonymous
Not applicable

Hi @DataChant / Gil, 

 

I know how to use both of them, my question is rather if any of them have any benefits over the other one, so far the only difference i can really find is that going via the web connector could enable to move the file easier, but I am interested if there are any other benefits / drawback to using either of the connectors 🙂 

 

And I am using 2 files, each with one table that gets refreshed every time, and one of the files contais 3 supporting tables, but they are only imported initilally as the value do not change.

 

Best, 

Julius

 

 

Hi Julius,

 

Are the two files with the same format? If so, I would use the Folder connector to make the solution robust to append similar tabs together. IN the future, if you have three files of the same format, you would not need to make any change to keep the query refreshing.

 

The Share Point Folder option is slower because you first load folder details, and if you have many folders and files under the Share Point site, it can have an impact on the performance of the import. Nevertheless, I think that the impact is minimal compared to the sustainability you achieve with the folder approach, so I would go with the Folder approach.

Anonymous
Not applicable

Hi Gil  (@DataChant ),

 

Not quite, 

 

I am using 2 excel files where 

the first has a table filles with KPI's as well as 3 tables with suppproting data (used for calculations that do not change) and

the second one which has a number of locations and extra data to them, which is linked via a relationship in power bi, so i'm not using the combin function of the folder connector.

The quesiton is really just if the connectors have any benefit in terms of connecting to the file itself.

The only difference i have found so far is that the web connector could have the benefit that the file could be moved to another sharpoint site without havig to reconstruct the dashboard, but that does not seem likely at this point...

 

Best,

Julius

Hi @Anonymous 

Where is the data not changed, power bi desktop or power bi service?

If it is on Power BI Desktop, please check if you click on the button "refresh all" or if you click on the "close &&apply" when closing the edit queries and back to report view.

If it is on Power BI Service, when you use web connector, please add the data source under the gateway and provide the same credential used in Power BI Desktop.

https://www.axioworks.com/2018/07/how-to-publish-sharepoint-data-to-power-bi-using-the-enterprise-gateway/

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @Anonymous,

In that case, I would use the Web connector and create a parameter for the URL hostname and relative path and pass them to the Web.Contents function.

Web.Contents(
    hostname, [
        RelativePath = path & "/Exce1.xlsx"
    ]
)

Performance-wise you get a slightly better refresh time using Web.Contents.

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors