Jira Integration with Power BI cannot do auto Refresh (dynamic data source)

[Power2BI]

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 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.

Below the used code: fnGetAllJiraIssuesWithAllFields: (jiraDomain as text, optional nextPageToken as text) => let // Build the request URL, adding the nextPageToken parameter if it exists url = jiraDomain & "/rest/api/3/search/jql" & (if nextPageToken = null then "" else "?nextPageToken=" & nextPageToken), // Fetch the current page of data Source = Web.Contents(url, [Query=[jql="project=ProjectName", fields="*all", maxResults="1000"]]), // Convert the response to JSON Json = Json.Document(Source), // Get the issues from the current page Issues = Json[issues], // Recursively fetch the next page if a nextPageToken exists NextPageIssues = if Record.HasFields(Json, "nextPageToken") then @@fnGetAllJiraIssuesWithAllFields(jiraDomain, Json[nextPageToken] else {}, // Combine the issues from the current page and all subsequent pages AllIssues = Issues & NextPageIssues in AllIssues GetIssues: let JiraDomain = "https://SERVERNAME.atlassian.net", // Replace with your Jira domain Source = fnGetAllJiraIssuesWithAllFields(JiraDomain, null), TableFromList = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(TableFromList, "Column1", {"expand", "id", "self", "key", "fields"}, {"Column1.expand", "Column1.id", "Column1.self", "Column1.key", "Column1.fields"}) in #"Expanded Column1"

[Power2BI]

Thanks Both,

Issue has been resloved by using relative path as below:

let
// Define the function to accept an optional nextPageToken.
fnGetAllJiraIssuesWithAllFields = (optional nextPageToken as text) as list =>
let
// Construct the query parameters record dynamically.
// We only add the nextPageToken field if it is not null.
QueryOptions = [
jql = "project=PROJECTNAME",
fields = "*all",
maxResults = "1000"
] & (if nextPageToken <> null then [nextPageToken = nextPageToken] else []),

// Call the Web.Contents function with the dynamically built query options.
Source = Web.Contents(
"https://SERVERNAME.atlassian.net",
[
RelativePath = "/rest/api/3/search/jql",
Query = QueryOptions
]
),

// Process the JSON response.
Json = Json.Document(Source),
Issues = Json[issues],

// Check if there is a next page token.
// Record.HasFields is a safe way to check for the field's existence.
NextPageToken = if Record.HasFields(Json, "nextPageToken") then Json[nextPageToken] else null,

// Perform the recursion if a next page token exists.
NextPageIssues = if NextPageToken <> null then
@fnGetAllJiraIssuesWithAllFields(NextPageToken)
else
{},

// Combine the results.
AllIssues = Issues & NextPageIssues
in
AllIssues
in
fnGetAllJiraIssuesWithAllFields

[v-sdhruv]

Hi @Power2BI ,
Just wanted to check if you got a chance to review the suggestions provided and whether that helped you resolve your query?
Thank you @Darryl_Rosin  for your input to the query.

[lbendlin · Accepted solution]

Please follow the documentation. Use RelativePath and Query parameters. https://learn.microsoft.com/en-us/powerquery-m/web-contents#example-1

[Power2BI]

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!

[Darryl_Rosin]

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

[Power2BI]

Thnaks for your comment. Rest API 2 has been suspended by Atlassian, unfortunately didnt work.

[lbendlin]

Please show your work. What have you tried and where are you stuck?