Schedule Refresh Power BI Service Issue

[sfeki]

Hi Community,

I'm facing the below error when trying to schedule refresh on Power BI service : 

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

I'm using this script :

let
Source = Json.Document(Web.Contents(JIRA_URL & "/rest/api/2/search?jql=" & QUERY)),
#"Converted to Table" = Record.ToTable(Source),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues", type any}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"total"}),
#"total" = #"Removed Other Columns"{0}[total],
#"startAt List" = List.Generate(()=>0, each _ < #"total", each _ +100), #"Converted to Table1" = Table.FromList(#"startAt List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "startAt"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each JIRA_URL & "/rest/api/2/search?maxResults=100&jql=" & QUERY & "&startAt=" & Text.From([startAt])), data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_))),
#"Converted to TableQuery" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

Where JIRA_URL is the company URL and QUERY is the JQL.

Any idea how this should be modified please ? 

Kind Regards,

Skandar

@ibarrau 

[v-zhangti]

Hi, @sfeki 

Please refer to the following links in the hopes of helping you.

Solved: Error while schedule refresh - getting error " Thi... - Microsoft Power BI Community

Solved: This dataset includes a dynamic data source. Since... - Microsoft Power BI Community

Solved: Help: Dataset includes a dynamic data source error - Microsoft Power BI Community

Best Regards,

Community Support Team _Charlotte

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

[sfeki]

Hi @ibarrau, 

Many thanks for your comment.

Unfortunately even after applying the suggested change I'm still unable to schedule the refresh.

I can see in the script that the parameters are being called in the highlighted line as well, do I need to proceed with any changes for that part as well ?

Thanks a lot.

[ibarrau]

You need to stick the practica on every opportunity. The highlighted line is an error because you are trying to avoid the practice sending the url all over again. You can read more about "Custom Functions". You just need a custom function that will handle the Web.Contents receiving the parameters you need instead of concatenating a big string. You can learn how here:
https://www.sqlservercentral.com/articles/create-custom-functions-in-power-query

I hope that helps,

[ibarrau]

Hi sfeki! This usually happen because power bi has trouble deducing a single source for the url. Web.Contents has many parameters to help us get close to a url that make easy for power bi to understand it. The idea is to use the arguments RelativePath or Query. For example at your code:

Web.Contents(
    JIRA_URL,
    [
        RelativePath = "/rest/api/2/search",
        Query = [ jql = QUERY ]
    ]
)

That way the main jira url will be interpreted as source for PowerBi and the other changes as conditions but not dynamic source.

You can read more about this at: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

I hope that helps,