Forum Discussion
Jira Integration with Power BI cannot do auto Refresh (dynamic data source)
Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1
Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1
Thanks lbendlin , I used RelativePath but didnt work for auto refresh. Can you share suggest or code solution using the code above. Might didnt use relative path correctly. Thanks again!
Please show your work. What have you tried and where are you stuck?
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
letFetchPage = (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
FetchPageFetchPageslet
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
FetchPagesThnaks for your comment. Rest API 2 has been suspended by Atlassian, unfortunately didnt work.
