SOLVED

Power Query/Sharepoint

Occasional Contributor

Hello all, 

 

It appears I'm heading down familiar territory, but my question doesn't seem to have been specifically answered.

 

I notice this thread, amongst others.

 

I shall be looking at this thread, with an excellent answer from @Sergei Baklan as well.

 

So, to my issue. I have been using PQ to source data from a folder on my desktop. There's a report I can run from our client database, I drag and drop it in the folder, it updates the spreadsheet to do all the formatting, cleaning and date checking I need, job done.

 

Now I want to do the same, but using sharepoint, so that anyone in the particular sharepoint space can drag the same report into a sharepoint folder and then refresh the PQ. 

 

Similar to other threads: I can only get the folder linked using "from folder" and even if you link the sharepoint to your drive so it appears under "from folder" it is then back to only working for me and not others. 

 

Using any of the sharepoint specific options under "Get Data" leads to various errors around url names.

 

I'll post more details shortly, but any help greatly appreciated. 

6 Replies
best response confirmed by davidmaddock54 (Occasional Contributor)
Solution

@davidmaddock54 

You may use From SharePoint Folder connector

image.png

using for the connection root site URL and filtering after that on desired folder. But that will be new project, From Folder connector is not mapped on SharePoint folder. At least directly.

Thanks. Yeah, that's the next step I can't work out. As you say, sharepoint folder only links to root site. Can you point me to a How to guide on that next step? Is it a matter of settings in sharepoint, user permissions, or a specific bit of coding?

@davidmaddock54 

Connecting to root one of the columns will be Folder path - filter on on folder you work with.

image.png

After filtering, expand content. Rest is similar as with From Folder connector.

Ok, got it up and running. Thank you. This solves one of the issues, but gets away from one of the main positives my original method had.

Using the regular folder method means the query works regardless of the file name. So as long as the report/columns match, and there's only one file in folder, the report will work as intended. (And if it's wrong, the errors are obvious, and you just put the right report in the folder and refresh.)

This method relies on the file name remaining identical. Not a deal breaker, but mildly annoying.

(Trying to find a solution for a lot of users with different capabilities to follow.)

@davidmaddock54 

Perhaps I misunderstood something, but you don't need filenames here as well. After you filter on SharePoint folder you may keep only Content column

image.png

After expand it you have same windows for combining files having first file as a sample

image.png

It;s entirely possible I misunderstood something. I'll have a further look and post screen grabs if I can. Thanks again.