cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ercwebdev
Frequent Visitor

'Refresh Preview' hangs

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: 

2020-01-23 16_24_09-Costs Test Report 2 - Power Query Editor.png

 

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:

2020-01-23 16_22_49-Costs Test Report 2 - Power Query Editor.png

 

AddedCustom: 

2020-01-23 16_01_59-Costs Test Report 2 - Power Query Editor.png

 

AllListData:

hangs roughly at this amount of date

2020-01-23 16_08_47-New Message - Microsoft Power BI Community.png

 

Removed Errors:

hangs roughly at this amount of date

2020-01-23 16_14_49-New Message - Microsoft Power BI Community.png

 

Have tried changing all of the settings, even disabling them all and it does nothing:

2020-01-23 16_35_09-Options.png

The permission for the data source connection is set as follows:

2020-01-23 16_37_07-Edit Permissions.png

3 REPLIES 3
Jimmy801
Super User III
Super User III

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors