Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hierarchy is
Sharepoint URL
Sharepoint Site
Project Site
List (Issue Log)
Thank you advance helping.
@Elizabeth Tachjian
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.
Hi Jing,
I cannot see the project sites using the method you suggested. Project sites are a level below site. (like a subsite)
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
To learn more about Power BI, follow me on Twitter or subscribe 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.
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
To learn more about Power BI, follow me on Twitter or subscribe 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")),
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
I will give it a go over the next couple of days and get back to you.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.