'Refresh Preview' hangs

[ercwebdev]

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:

[Jimmy801]

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

[ercwebdev]

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

[jsreis]

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