Power Query - import multiple files from SharePoint

[Factotum]

I've been all over the web trying to solve this one with no luck. I'm very new to Power Query, so please forgive any ignorance on my part. I'm the admin for a Sharepoint site with several libraries that contain hundreds of Excel files in each. All of the Excel files are the exact same format/layout.

I'm trying to import the data from the last tab in each spreadsheet and combine them all into one table. I think I'm taking the right steps (Get Data --> From SharePoint Folder), but I always end up with an error "Access to the resource is forbidden". I know I've got the right username and password in the Permissions, and I've tried every other possible combination just to make sure.

Anyone know what I'm missing? I'd have pulled out all my hair by now if I wasn't already bald!

 

[Matt Allington]

Have you tried clearing your credentials from Power Query and trying again? They are under data source settings. Also check that you are using the main URL for Sharepoint, not the link to the library https://exceleratorbi.com.au/consolidate-multiple-excel-files-in-sharepoint-using-powerquery/

 

[Factotum]

Thanks for the suggestion Matt. Unfortunately I've already tried clearing my credentials numerous times. My organization recently changed its email and website domain, but the SharePoint I'm trying to link to is still on the old domain. I've wondered if that could be causing problems as I try to enter my credentials, but I've tried using old and new credentials. I had been referring to a site very similar to the one you suggested, but not as detailed. When I have a few minutes, I'll follow the steps in your link and see if that solves my problem. Thanks again!

 

[Factotum]

Matt, I know it's been a while since this post, but life kinda got in the way. The link you shared appears to be the ticket - I've finally been able to connect to my SharePoint library and got the data from a couple dozen files all into one table in Excel! I won't pretend to know exactly how it works, I'm just thrilled that it does work.

I'm embarrassed to admit that I spent over an hour trying to figure this out on my own, and then considered posting the question to this forum.... only to remember that I had already asked the question! I deserve a major face-palm for that one!

Thanks a million - this might literally save me a couple of solid weeks worth of work over the next few months!