Extract Details on sites, subsites and document library from SharePoint

[Pranav195]

Hi everyone,

I am pretty new here. I was having a bit of trouble with loading data to power BI. I want to actually get a list of sites, subsites, and document libraries from sharepoint in powerBI. I wanted to segreagate the documents based on the sites and subsites actually. I tried different ways to connect to sharepoint to extract the sites and subsites from sharepoint but had no luck whatsoever.

Alternatively, I also do have an excel extract with the column url of the sharepoint library. I tried extracting the sites, subsites and document library from this as well using delimeters but it dosen't seperate properly.

ex1: https://sharepoint.com/sites/Microsoft/BoardMeetings/Shared Documents/Registers/

Here using the split function I had actually divided the data to extract the:

Site: Board Meeting

Sub site: Shared Documents

Document library: Registers

But the trouble arisies when the url column contains more subsites/less subsites

ex2:- https://sharepoint.com/sites/Microsoft/BoardMeetings/Registers/

Here:

Site: Board Meetings

Document library: Registers

ex3:- https://sharepoint.com/sites/Microsoft/BoardMeetings/Shared Documents/Registers/Document

Here there are:

Site: Board Meeting

Sub site: Shared Documents

Subsite2: Registers

Document library: Document

Based on the provided examples: When there are multiple cases of subsites, it is harder to split using delimeter in powerquery as the documnet library could be placed as a subsite by power query.

Can anyone of the experts here, help me on this.

1) Extract sites and subsites from SharePoint

2) Extract Sites, Subsites and Document library from URL (excel)

Any help would be appreciated.

[halfglassdarkly]

Have you tried using the SharePoint Folder connector rather than the general web connector?

https://learn.microsoft.com/en-us/power-query/connectors/sharepoint-folder

I've only been using it to to list documents and paths on a sub-site but I'd have thought you could run at root level for the whole site if you use the right url.

I think you'll still have the same delimitation issue if you get the data with Power Query though.

if I understand your requirements you should be able to use a combination of the TextBeforeDelimiter, TextAfterDelimiter, and TextBetweenDelimiter functions in Power Query to get things structured how you want them. You can set it to scan from the nth or nth last delimiter in your text string. See https://learn.microsoft.com/en-us/powerquery-m/text-functions#transformations