Unable to refresh Dynamic Data Sources from CRM

[Anonymous]

I have a report that is connecting to Dynamics CRM using the M query below. Works fine in the desktop but will not refresh on the service. New to M query and connecting to Dynamics CRM this way. Any assistance would be appreciated!! 

let
crmUrl = "https://xxxxxxx.api.crm9.dynamics.com/api/data/v9.2/",
Source = "reports(bbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbb)?$select=bodybinary",
odataReply = Json.Document(Web.Contents(crmUrl & Source)),
textfile = Text.FromBinary(Binary.FromText(odataReply[bodybinary])),
functionText = List.Skip(Text.Split(textfile, "///")),
functionNames = List.Transform(functionText, each Text.Trim(Text.BeforeDelimiter(_, "#(cr)#(lf)")) ),
functionEvaluate = List.Transform(functionText, each Expression.Evaluate(Text.Insert(_,0,"// "), #shared)),
Custom1 = Record.FromList(functionEvaluate, functionNames),
crmGetEntityView = Custom1[crmGetEntityView],
getView = crmGetEntityView("Purchase Request")("*MyViews*>PBI - Active Obligations")
in
getView

[Anonymous]

@Anonymous 

1. You can use the following methods to determine whether your data source supports refreshing in Power bi:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-admin-troubleshoot-unsupported-data-source-for-refresh

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data#refresh-and-dynamic-data-sources

If the display does not work, you can try to solve it through the following two links:

Setting a scheduled refresh on a Dynamic Data Source in Power BI | by dataninja | DataDrivenInvestor

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/

2. Custom functions and query parameter not support in power bi service. If your source table contains above features, you can't enable refresh features on power bi service

For normal functions(remove column, filter records, add custom column, unpivot columns...), they all works on power bi service.

For more details, please refer to the following post:

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

https://community.powerbi.com/t5/Service/Unable-to-refresh-Dynamic-Data-Sources/m-p/1867648

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous Are you getting an error in the Service or ?

[Anonymous]

Getting an error in the Service. Works fine in Desktop.

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:

• Data source for Query1