Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Arut
New Member

Help: Dataset includes a dynamic data source error

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"

1 ACCEPTED SOLUTION
ibarrau
Super User
Super User

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,


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

Happy to help!

LaDataWeb Blog

View solution in original post

4 REPLIES 4
v-luwang-msft
Community Support
Community Support

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
Super User
Super User

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,


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

Happy to help!

LaDataWeb Blog

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors