Unable to Enable schedule refresh for dynamic data source (Source is Application Insights)

[Anonymous]

I have implemented dynamic datasource for my power bi to enable environment wise data in Application Insights URL. But I am unable to schedule refresh for the report.

Error: This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources.

• Data source for Query1

Query: 

let AnalyticsQuery =
let Source = Json.Document(Web.Contents("https://api.applicationinsights.io/v1/apps/"& ParameterForEnvironment & "/query",
[Query=[#"query"="requests
| where timestamp >= ago(2d)
| where  url contains ""Test""
",#"x-ms-app"="AAPBI",#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" },
{
{ "string", Text.Type },
{ "int", Int32.Type },
{ "long", Int64.Type },
{ "real", Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool", Logical.Type },
{ "guid", Text.Type },
{ "dynamic", Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]),
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table
in AnalyticsQuery

[ImkeF]

Hi @Anonymous ,
yes, unfortunately OAuth is not possible without insecure hacks. You need to go via a custom connector (and subsequently an on prem-gateway then) like described here:
Chris Webb's BI Blog: Connecting To REST APIs With OAuth2 Authentication In Power Query/Power BI Chris Webb's BI Blog (crossjoin.co.uk)

[ImkeF]

Hi @Anonymous ,
yes, that's to be expected. Every bit that is variable mustn't sit in the main URL. You need to use it in the relative path or in the query parameters in the record of the call.

[ImkeF · Accepted solution]

Hi @Anonymous ,
you have to use the relative path parameter for the dynamic part like described here:
Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code Chris Webb's BI Blog (crossjoin.co.uk)

[Anonymous]

Thanks @ImkeF for the solution.

I am able to see the schedule refresh in Power BI service. But when i try to refresh, i got the below error.

Error : 

• Data source errorThe credentials provided for the Web source are invalid. Source at https://api.loganalytics.io/v1/workspaces/1232/query. The exception was raised by the IDataReader interface. Please review the error message and provider documentation for further information and corrective action. Table: TableName.

I have checked the "Data source credentials" and not able to see the "OAuth" option in "Authentication Method" and also tried with "Skip Test Connection", but got the same error.

[Anonymous]

After using the RelativePath, I got the below error 

Error : Scheduled refresh is disabled because at least one data source is missing credentials. To start the refresh again, go to this dataset's settings page and enter credentials for all data sources. Then reactivate scheduled refresh.

When I tried to validate the credentials, I got the below error:

Error: Failed to update data source credentials: Web.Contents failed to get contents from 'https://api.applicationinsights.io/v1/apps/' (404): Not Found.

"https://api.applicationinsights.io/v1/apps/"& ParameterForEnvironment & "/query"

ParameterForEnvironment ID will come from the Report which is selected by the user, without this value the static URL won't work.