Import all list under many Sharepoint Project sites - list title "Issuelog"

[ElizabethTachji]

Hi,

I would like to import all list under many project sites that fall under a sharepoint site.

I would like to import all list in a sharepoint site but there is a twist.  Within the sharepoint site ASPYITProject, there are many project sites.  The project site (1459) has a lists IssueLog.  Can I import all list that fall under sharepoint Site including those list that reside in the project List.

I am wanting to import in one go, That is all list with a title "Issuelog" in the many project sites that fall under a sharepoint site. 

Here is a document I found on setting up project sites in Sharepoint to give you an idea of the setup.

https://support.microsoft.com/en-us/office/get-started-with-a-project-site-8b68274b-622d-4a99-9245-1...

Hierarchy is

Sharepoint URL

Sharepoint Site

Project Site

List (Issue Log)

Thank you advance helping. 

@Elizabeth Tachjian

[v-jingzhang]

Hi @ElizabethTachji 

When you connect to the Sharepoint list, enter the root Sharepoint site URL in the Site URL and select an IssueLog list in Navigation window. 

Then in Power Query Editor, delete Navigation step, in Source step you will see all lists under this Sharepoint site. You can filter the Title column to keep only IssueLog lists. Then expand the Items column to get all data in a table.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[ElizabethTachji]

Hi Jing,

I cannot see the project sites using the method you suggested.  Project sites are a level below site.  (like a subsite)

[mahoneypat]

You should be able to use the function provided with this video and use it with a table of site url and list names to pass into each it for each.

Get SharePoint List Data with Power BI ... Fast - YouTube

Pat

[ElizabethTachji]

Hi Pat,

I dont understand how I capture the new subsites.  Can I place the new subsites in excel then import the excel and somehow the code picks up the subsite.

Thanks inadvance.

[mahoneypat]

Yes.  You could use the REST API to get all the subsites dynamically, but if there are not many and they are constant, you can make an Excel table with the URL, site/subsite, and list name (maybe constant) in three columns, and then pass those three columns as paramaters into the function.

Pat

[ElizabethTachji]

Hi Pat,  Can you be specific on how I can capture the subsites dynamically without a custom function.  In the example below, I call each subsite and then append.  Is there a way of dynamically bring in all subsites in one go.  That is I dont need to list each subsite like I have below.  thank you for your patience and help.

let

    Source = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1", [Implementation=null, ApiVersion=15]),

    Source1 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/58", [Implementation=null, ApiVersion=15]),

    Source2 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/84", [Implementation=null, ApiVersion=15]),     

    Source3 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/241", [Implementation=null, ApiVersion=15]),

    Source4 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/474", [Implementation=null, ApiVersion=15]),  

    Source5 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/908", [Implementation=null, ApiVersion=15]),      

    Source6 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1102", [Implementation=null, ApiVersion=15]),

    Source7 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1248", [Implementation=null, ApiVersion=15]),

     Source8 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1276", [Implementation=null, ApiVersion=15]),

     Source9 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1320", [Implementation=null, ApiVersion=15]),

     Source10 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1369", [Implementation=null, ApiVersion=15]),

     Source11 = SharePoint.Tables("https://CompanyName.sharepoint.com/sites/SiteName/1394", [Implementation=null, ApiVersion=15]),

    Final = Table.Combine({Source, Source1,Source2,Source3,Source4,Source5,Source6,Source7,Source8, Source9,Source10,Source11 }),

    #"Filtered Rows" = Table.SelectRows(Final, each ([Title] = "CHANGE")),

[ElizabethTachji]

Hi Pat

I am trying not to list all subsites in the code below 

sitename ="<your site>", // if a subsite use "Site/SubSite"

 Is there a wild card I can use in M or power query to import all subsites tables under a sharepoint site  

Cheers Elizabeth

[ElizabethTachji]

I will give it a go over the next couple of days and get back to you.