Sharepoint Folder; Missing Folders/Files

[ptmuldoon]

I'm still very new with Power BI and Power Query in general, and having some issues in trying to access files via SharePoint Online (we use office 365)

I can enter the URL for our sharepoint site and sign on with my Org Credentials.  But after I can't seem to filter or find most of the files on our SharePoint site that I do have rights and permissions too.

When i access our sharepoint site via web browser, I can navigate between folders and see and open pretty much any file, etc.   But in Power Query, I do not see pretty much any of those files.  I'm not sure if I need to go back our our company's IT department yet to check permissions or not.

I also have a mapped drive to our SharePoint files, and can also navigate and see them that way.  And that did work by loading a file in power query that way.  But I can't rely on the mapped drives alway's being there (drive letters change).

Is there something I'm possibly missing in trying to locate certain folders/files via Sharepoint Folder?

Thanks,

PT

[lbendlin · Accepted solution]

Which connector did you use, Sharepoint Folder or Sharepoint List ?

By the way there is another connector that you can try - SharePoint.Contents - PowerQuery M | Microsoft Docs  - it gives raw access to all content.

Also set the API level 14 if you still have issues (15 is sometimes iffy)

[edhans · Accepted solution]

@ptmuldoon The default SharePoint.Files connection only lists the first 5,000 files, so it might not find everything. In the Source line for the SharePoint connection change SharePoint.Files to SharePoint.Contents (it is case sensitive) in the formula bar (make sure that is visible in the View tab of Power Query - check the "Formula Bar" button) then drill down the folder structure. The first place you probably need to go is to Share Documents, then you will start to see files and folders that make sense.

I generally prefer SharePoint.Contents as it is faster, but it does have one drawback. When you are combining files, you can only combine files in one folder vs SharePoint.Files which will combine whatever files you filter down to. But SharePoint.Contents doesn't have the file listing limitation .Files does.

[edhans · Accepted solution]

@ptmuldoon The default SharePoint.Files connection only lists the first 5,000 files, so it might not find everything. In the Source line for the SharePoint connection change SharePoint.Files to SharePoint.Contents (it is case sensitive) in the formula bar (make sure that is visible in the View tab of Power Query - check the "Formula Bar" button) then drill down the folder structure. The first place you probably need to go is to Share Documents, then you will start to see files and folders that make sense.

I generally prefer SharePoint.Contents as it is faster, but it does have one drawback. When you are combining files, you can only combine files in one folder vs SharePoint.Files which will combine whatever files you filter down to. But SharePoint.Contents doesn't have the file listing limitation .Files does.

[Nicolas678]

Hi there!

Thanks for all these tips!

I'm having the same issues as the original poster. I tried to follow Your solution.

I was able to change SharePoint.Files to SharePoint.Contents at the source line. For the next step You said this: "then drill down the folder structure. The first place you probably need to go is to Share Documents, then you will start to see files and folders that make sense."

How exactly can i drill down? Could you maybe elaborate on this? Whenever i change from ".Files" to ".Contents" the amount of rows goes from 1892 rows to only 11. All the contents i need to access seem to be invisible.

Thank You very much for Your time!

Best regards
Nicolas

[Anonymous]

Best solution ever!

[ptmuldoon · Accepted solution]

Thanks guy.  Both of you pointed me in the right direction and with SharePoint.Contents was able to drill down and locate the file(s).

I'm sure I'll be back with some more questions though 🙂

[lbendlin · Accepted solution]

Which connector did you use, Sharepoint Folder or Sharepoint List ?

By the way there is another connector that you can try - SharePoint.Contents - PowerQuery M | Microsoft Docs  - it gives raw access to all content.

Also set the API level 14 if you still have issues (15 is sometimes iffy)