Forum Discussion

Power2BI's avatar
Power2BI
Helper I
11 months ago
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 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"

Tips and Tricks

7 Replies

    • Power2BI's avatar
      Power2BI
      Helper I
      11 months ago

      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!

      • lbendlin's avatar
        lbendlin
        Super User
        11 months ago

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

  • Power2BI's avatar
    Power2BI
    Helper I
    11 months ago

    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's avatar
    v-sdhruv
    Community Support
    11 months ago

    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.