cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElizabethTachji
Helper III
Helper III

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

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

Elizabeth Tachjian
8 REPLIES 8
v-jingzhang
Community Support
Community Support

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. 

061607.jpg

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.

061606.jpg

 

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

Hi Jing,

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

Elizabeth Tachjian
mahoneypat
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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.

Elizabeth Tachjian

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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")),

Elizabeth Tachjian

 

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

Elizabeth Tachjian

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

Elizabeth Tachjian

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors