Hi Cheryl,

SharePoint uses the web connector, not a folder connector, therefore you don't need a dynamic path as such, you just need to ensure that any users who are refreshing the query have permission to the file/folder on SharePoint.

Mynda

Thank you - I did realize that I wouldn't need a dynamic path if I could connect via Sharepoint, but I can't seem to get to the sharepoint folder I need.  I can access Sharepoint, but discovered that the url needs to navigate to only the root directory of the sharepoint site.  When I try to enter more than the root folder in the sharepoint url, that gives me an error.  I find that accessing via sharepoint and then trying to drill down to the folder or file in a lengthy folder structure with many folder levels is a bit difficult.  Some of my folders are more than 4-5 layers deep (not my design, but that of others I share files with).  

Is there any recommendation to speed up the load when navigating down several layers, or perhaps help with how to paste the actual folder path in without having to start at the root of the sharepoint site?

If I don't go through Sharepoint - my query source is unuseable by others: because it is mapped to my synced files C:UsersmynameSWMITECHKRESA - EMC Early Middle College Admin Files - Documents19-2019-20 EnrollmentKVCC Lists

But if I try and use sharepoint as my source, it is extremely difficult to navigate to the folder I want to find in the transform step of the query, given the very long folderpath names, that disappear from the transform window.

This picture shows the transform window where you can see the initial folder path in the M Code, but the column with the folder paths that I want to filter to, are off the screen due to their length.  Deleting columns still doesn't allow me to widen the column so that I can see the file names, so I have to enter a filter, that then tries to find the folder I am seeking.

Thank you - hopefully this makes sense.

Hi Cheryl,

I have also found this with SharePoint. I'm not aware of an easier way to enter the file path rather than the SharePoint root directory. My recommendation is to create a new folder for your Power Query data that's higher up the folder hierarchy so it's easier to find.

Mynda

Thank you.  I tried to use the parameter for dynamic file path pointing to my synced file, but got an error message there as well.  Sadly, this won't work, but most of our files are now stored in the cloud, so my powerquery files are no longer easy to share.

Hi Cheryl,

You have to use the Sharepoint root directory to make the connection, which makes things a bit unwieldy unfortunately.

You can get a list of all files from all subfolders off the root using Sharepoint.Files.  If you can't filter the folder you want from Folder Path column inside the PQ Editor, just filter something so that you create the necessary step and M code.

You can then go into the Advanced Editor and replace the path you just filtered with the correct path that you want. 

This query contains the necessary steps.

let
Source = SharePoint.Files("https://swmitech.sharepoint.com/", [ApiVersion = 15]),
#"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://swmitech.sharepoint.com/Early Middle College Admin Files - Documents/20-21/20-21 Enrollment/KVCC Enrollments and Grades"))
in
#"Filtered Rows"

When this runs you should be left with a list of files in that folder.  You can right click 'Binary' in the Content column beside the file you want and Drill Down to see the file contents.

Regards

Phil

Hello Philip,

Thanks for your response.  Using the M code you provided resulted in an empty table.  When I changed the ApiVersion to 14, I did get bit further, but still had an error

DataSource.Error: SharePoint: Request failed: The remote server returned an error: (500) Internal Server Error. (An error occurred while processing this request.)
Details:
DataSourceKind=SharePoint
DataSourcePath= https://swmitech.sharepoint.com/_vti_bin/ListData.svc/SolutionGallery
SPRequestGuid=a2636f9f-90bb-b000-60c9-f0bf3b94e978
Url= https://swmitech.sharepoint.com/_vti_bin/ListData.svc/SolutionGallery

The folder path column with your initial source M Code fewer choices to filter on, but still resulted in way too many files.  I think the best solution is going to be to do as Mynda suggested and keep my source files in a folder that is near the top of the hierarchy, or create a much simpler folder structure in the data source site.  This will mean that I likely need to create a new Sharepoint site to keep my data files in, that is not cluttered with so many other users' files, but it did seem to work much better.  Thanks for all the suggestions.

Hi Cheryl,

So using the exact query I provided results in no files found? If the path was correct that would indicate there are no files in the folder?

Did you try loading the results from getting all files from the root of your site, then confirming the path to the file(s) you want?  This query lists all files in the root and all files in all subfolders off the root.

let
Source = SharePoint.Files("https://swmitech.sharepoint.com/", [ApiVersion = 15])
in
Source

Once you've found the exact path, plugging it into the query should give you a list of files in that folder.

If you are getting a lot of files listed, can't you just filter out the ones you don't need?

I presume your intention is to get data from these files, not just to get a listing?

Regards

Phil

Yes I pasted the query m code you provided, and attached is what I got as a result. (Empty Table), the next step of that query is to filter to the folder, but I don't have results to filter. 

However, also attached is a pic of the content of the folder I am pointing to. (Folder Contents) 

This query did work.  I think part of my challenge was understanding what the pathname was to the sharepoint folder.  I wasn't including "sites" in the path, and that was needed to get to the right level in the folder  Then there was the challenge of filtering -to the specific folder, so "contains" was simpler as a filter so as not to have to type the entire long pathname (which changes when filtering in the query) in the filtering step.

let
Source = SharePoint.Files("https://swmitech.sharepoint.com/sites/EMCEarlyMiddleCollegeAdminFiles/", [ApiVersion = 15]),
#"Filtered Rows1" = Table.SelectRows(Source, each Text.Contains([Folder Path], "20-21/20-21 Enrollment/KVCC Enrollments and Grades"))
in
#"Filtered Rows1"

I am seeing this question a lot in the PowerBI community as well.  Understanding the difference in pathnames from a local or network drive to a Sharepoint data source causes lots of confusion, it appears.  It did for me.  For now I can use the code above as my starting source, and edit my filter to contain, the final folder I am looking for.  

Thank you!