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.
This is pretty much a last effort to try and get this working. The M Power Query code below passes a URL to the sub routine, which then queries the PWA site to get List info. There are 93 Projects and subsites, but it just times out. I've experimented with removing rows from the top of the list and then the bottom. Seems it hangs if there are any more than 63 rows?? It doesn't make any sense. Any help / ideas on this would be greatly appreciated because I've tried everything and have now run out fo road!
Thanks
Alan
Queries:
GetAllCosts:
let
Source = OData.Feed("https://xxxxxxxxxxx.sharepoint.com/sites/pwa/_api/ProjectData/Projects?$filter=ProjectWorkspaceInternalUrl ne null&$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl"),
#"Added Custom" = Table.AddColumn(Source, "siteURLFix", each let Source = [ProjectWorkspaceInternalUrl], uriPartsRecord = Uri.Parts(Source), result = uriPartsRecord[Scheme] & "://" & uriPartsRecord[Host] & uriPartsRecord[Path] in result),
#"AllListData" = Table.AddColumn(#"Added Custom", "Costs", each GetList([siteURLFix], "Costs")),
#"Removed Errors" = Table.RemoveRowsWithErrors(AllListData)
in
#"Removed Errors"
GetList:
= (siteURL,listname) =>
let
Source = SharePoint.Tables(siteURL),
#"MyListData" = Source{[Name=listname]}[Content]
in
#"MyListData"
Applied Steps:
AddedCustom:
AllListData:
hangs roughly at this amount of date
Removed Errors:
hangs roughly at this amount of date
Have tried changing all of the settings, even disabling them all and it does nothing:
The permission for the data source connection is set as follows:
Hello @ercwebdev
try to change this code
let
Source = OData.Feed("https://xxxxxxxxxxx.sharepoint.com/sites/pwa/_api/ProjectData/Projects?$filter=ProjectWorkspaceInternalUrl ne null&$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl"),
#"Added Custom" = Table.AddColumn(Source, "siteURLFix", each let Source = [ProjectWorkspaceInternalUrl], uriPartsRecord = Uri.Parts(Source), result = uriPartsRecord[Scheme] & "://" & uriPartsRecord[Host] & uriPartsRecord[Path] in result),
#"AllListData" = Table.AddColumn(#"Added Custom", "Costs", each GetList([siteURLFix], "Costs")),
#"Removed Errors" = Table.RemoveRowsWithErrors(AllListData)
in
#"Removed Errors"
into this one
let
Source = Table.Buffer(OData.Feed("https://xxxxxxxxxxx.sharepoint.com/sites/pwa/_api/ProjectData/Projects?$filter=ProjectWorkspaceInternalUrl ne null&$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl")),
#"Added Custom" = Table.AddColumn(Source, "siteURLFix", each let Source = [ProjectWorkspaceInternalUrl], uriPartsRecord = Uri.Parts(Source), result = uriPartsRecord[Scheme] & "://" & uriPartsRecord[Host] & uriPartsRecord[Path] in result),
#"AllListData" = Table.Buffer(Table.AddColumn(#"Added Custom", "Costs", each GetList([siteURLFix], "Costs"))),
#"Removed Errors" = Table.RemoveRowsWithErrors(AllListData)
in
#"Removed Errors"
Hope this helps
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Jimmy
Hi Jimmy,
Thanks for your reply. Yes I had tried Table.Buffer already (forgot to mention this) but it didn't yield any results.
So still looking for an answer!
Thanks
Alan
Hello Alan -
Have you found a solution to your problem? It sounds identical to what I'm experiencing.
Any help will be greatly appreciated as I've spent countless hours trying to figure this out.
Thanks!
Jeff
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.