Help: Dataset includes a dynamic data source error

[Arut]

Hi guys,

Would highly appreciate if someone could help rewrite the below code, so it would update automatically in power bi service. Currently I'm getting 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"

What should be changed below? Huge appreciation for any help.

let
BaseUrl = "https://xxxx.atlassian.net/rest/api/2/search?jql=project in ('xxxx')
&fields=
id,
key,
summary,
issuetype,
resolutiondate,
customfield_11105, /*Parent link*/
",

JiraIDPerPage = 100,

GetJson = (Url) =>
let
RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,

GetJiraIDCount = () =>
let Url = BaseUrl & "&maxResults=0",
Json = GetJson(Url),
Count = Json[#"total"]
in Count,

GetPage = (Index) =>
let Skip = "&startAt=" & Text.From(Index * JiraIDPerPage),
Top = "&maxResults=" & Text.From(JiraIDPerPage),
Url = BaseUrl & Skip & Top,
Json = GetJson(Url),
Value = Json[#"issues"]
in Value,

JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
PageCount = Number.RoundUp(JiraIDCount / JiraIDPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
JiraID = List.Union(Pages),
Table = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
Table

Also the second query below, in case it needs to be changed as well:

_______________________________________________________________________________________________________________________

let
Source = Json.Document(Web.Contents("https://xxxxxx.atlassian.net/rest/api/2/field")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name"}, {"Column1.id", "Column1.name"})
in
#"Expanded Column1"

[v-luwang-msft]

Hi @Arut ，

Has your problem been solved, if so, please consider Accept a correct reply as the solution to help others find it.

Best Regards

Lucien

[ibarrau · Accepted solution]

Hi. Like the documentation says if the source is dynamic you can't get it refresh on service. I have connected to Jira in the past so let me told how I did it with limitations.

First you need to split the URL with good practices for requests like this post:

https://blog.ladataweb.com.ar/post/630597294839955456/powerquery-buena-práctica-para-un-web-request

The following example will ask for issues for an specific board. You can build a custom function setting the board with parameters.

Origen = Json.Document(
    Web.Contents(
        "https://[Company].atlassian.net", 
        [RelativePath="rest/agile/1.0/board/1/issue?maxResults=1000"]
    )
),

This will handle the first issue with dynamic dataset because the source will be considered the URL that would be the same for all of the requests in the API.

The second issue is about paginating. When you paginate in Power Query you are kind of making a loop inside the request that will break the definition of the source making it dynamic. I couldn't find a solution for this to work in power bi service. That's why I'm using old requests from the API that will let me handle "maxResults". If the request contains that parameter you can set the number of results for the request avoiding the pagination. Check old versions of Jira api, they are still working.

Hope that helps,

[Digger]

@ibarrau you wrong, i found a way to do so

[Analitika]

My datasources are sql server, postgresql database, excel file, txt file, something like this. I don't get which datasource is dynamic.