Forum Discussion

davidmaddock54's avatar
davidmaddock54
Brass Contributor
Oct 27, 2021
Solved

Power Query/Sharepoint

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 th...
BI & Data Analysis
excel
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Oct 28, 2021

    davidmaddock54 

    You may use From SharePoint Folder connector

    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.

davidmaddock54's avatar
davidmaddock54
Brass Contributor
Oct 28, 2021
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?
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 29, 2021

davidmaddock54 

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

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

  • davidmaddock54's avatar
    davidmaddock54
    Brass Contributor
    Dec 20, 2021

    SergeiBaklan Thanks again for this, couldn't seem to use expand option for that column, but removing other columns and just keeping binary and folder path columns did the job.

     

     

  • davidmaddock54's avatar
    davidmaddock54
    Brass Contributor
    Nov 01, 2021
    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.)
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 01, 2021

      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

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

      • davidmaddock54's avatar
        davidmaddock54
        Brass Contributor
        Jul 19, 2022
        Hi again folks. I think this has been answered before, but want to double check. I have had a spreadsheet working well using "from sharepoint folder" option, but now it's stopped working. I'm having trouble even getting in to see where the error lies in the process, but having just tried to recreate the spreadsheet I'm running into an issue which I think is pretty foundational.

        The url I use is at a regional level, as you can't go into further subfolders with the url. But that creates an issue because when I go to Transform the data, the list of items/folders is over 1000, and the data I want is outside of that first 1000. Is there a way around this?

Resources