Forum Discussion
Solved
Jira Integration with Power BI cannot do auto Refresh (dynamic data source)
Hello Community, I'm facing an issue with auto refresh for Power BI dashboard integrated with JIRA SD Cloud. Below the error after using REST API 3: This dataset includes a dynamic data sou...
Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1
I think the problem is here:
url = jiraDomain & "/rest/api/3/search/jql" & (if nextPageToken = null then "" else "?nextPageToken=" & nextPageToken)
You can't assemble the URL dynamically and use scheduled refresh
But you can use maxResults and startAt as part of a 'query' record with Web.Contents, and save the JQL query in Jira and reference it in the url
Web.Contents(jiraDomain & "/rest/api/3/search/jql?jql=filter%20%3D%2014372",[Query = [maxResults= Text.From(pageSize), startAt = Text.From(skipRows)]])
There is (or used to be) a Power BI content pack for Jira that had some functions for paging out the Jira data
GenerateByPage
(getNextPage as function, optional tableType as type) as table =>
let
listOfPages = List.Generate(
() => getNextPage(null),
(lastPage) => lastPage <> null,
(lastPage) => getNextPage(lastPage)
),
tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
firstRow = tableOfPages{0}?,
keys = if tableType = null then Table.ColumnNames(firstRow[Column1])
else Record.FieldNames(Type.RecordFields(Type.TableRow(tableType))),
appliedType = if tableType = null then Value.Type(firstRow[Column1]) else tableType
in
if tableType = null and firstRow = null then
Table.FromRows({})
else
Value.ReplaceType(Table.ExpandTableColumn(tableOfPages, "Column1", keys), appliedType)
FetchPage
let
FetchPage = (url as text, pageSize as number, skipRows as number) as table =>
let
//Here is where you run the code that will return a single page
contents = Web.Contents(URL&"/rest/api/2/search?filter=-4",[Query = [maxResults= Text.From(pageSize), startAt = Text.From(skipRows)]]),
json = Json.Document(contents),
Value = json[issues],
table = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
table meta [skipRows = skipRows + pageSize, total = 500]
in
FetchPage
FetchPages
let
FetchPages = (url as text, pageSize as number) =>
let
Source = GenerateByPage(
(previous) =>
let
skipRows = if previous = null then 0 else Value.Metadata(previous)[skipRows],
totalItems = if previous = null then 0 else Value.Metadata(previous)[total],
table = if previous = null or Table.RowCount(previous) = pageSize then
FetchPage(url, pageSize, skipRows)
else null
in table,
type table [Column1])
in
Source
in
FetchPages
Thnaks for your comment. Rest API 2 has been suspended by Atlassian, unfortunately didnt work.