Forum Discussion

Coyote34's avatar
Coyote34
Frequent Visitor
2 years ago
Solved

Jira API in Data Flow error:

Hi there!   I am trying to create a dataflow to load Jira data using follow script   let BaseUrl = "https://********.atlassian.net/rest/api/3/search?jql=project in ('ABC') AND created >= '...
Need Help
  • Coyote34's avatar
    Coyote34
    2 years ago

    HI, guys. 

     

    I wanna share the solution after a lot ogf resource.

     

    let
  request = (startAt) => 
  let
    get = Json.Document(
      Web.Contents(
          "https://yourcompny.atlassian.net",
          [
            Headers=
              [Authorization="Basic ********************************************** ="],
              RelativePath = "/rest/api/3/search?" & "jql=project in ('ABC') AND created >= '2024-01-01' AND created < '2025-01-01'" & "&startAt=" & Text.From(startAt) & "&fields=*all&maxResults=" & Text.From(maxResults)
          ]
      )
    )
  in
    get,
  results = request(1),
  #"Converter em tabela" = Table.FromValue(results),
  #"Transformar colunas" = Table.TransformColumnTypes(#"Converter em tabela", {{"Value", type text}}),
  #"Substituir erros" = Table.ReplaceErrorValues(#"Transformar colunas", {{"Value", null}}),
  #"Linhas filtradas" = Table.SelectRows(#"Substituir erros", each ([Name] = "total")),
  #"Tipo de coluna alterado" = Table.TransformColumnTypes(#"Linhas filtradas", {{"Value", Int64.Type}}),
  #"Coluna dividida" = Table.TransformColumns(#"Tipo de coluna alterado", {{"Value", each _ / 100, type number}}),
  #"Arredondado para cima" = Table.TransformColumns(#"Coluna dividida", {{"Value", each Number.RoundUp(_), Int64.Type}}),
  Personalizar = List.Generate(() => 1, each _ <= #"Arredondado para cima"{0}[Value], each _ +1, each request((maxResults * (_ - 1)) + 1)),
  #"Convertido em tabela" = Table.FromList(Personalizar, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Convertido em tabela"

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Coyote34 

 

The error message you're encountering in Power BI, "Unable to save the data flow. One or more tables reference a dynamic data source," typically occurs when the Power BI service encounters a dynamic M query that it cannot process. This often happens when the query includes dynamic elements like a function call to generate a part of the query.

 

Here's what you can do to troubleshoot and potentially fix this issue:

 

Static Data Source References: Power BI Dataflows prefer static data source references. Your script dynamically constructs URLs and makes HTTP requests. This might be flagged as dynamic by Power BI Service. You need to ensure that the base URL and any other data source references are as static as possible.The following documents have specific instructions：

Dataflows Limitations, restrictions and supported connectors and features - Power BI | Microsoft Learn

 

Limit Use of Custom Functions: Your script uses a custom function GetJson () to retrieve data. Custom functions can sometimes be interpreted as dynamic sources by Power BI Service. You could try to inline the function logic within the main query to see if that resolves the issue.

 

Alternative Approaches: If you continue to face issues, consider alternative approaches like importing the data into Power BI Desktop and then publishing the PBIX file to the Power BI Service.

 

 

Best Regards,

Jayleny

 

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