Jira API in Data Flow error:

[Coyote34]

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 >= '2024-01-01' AND created < '2025-01-01'",
  JiraIDPerPage = 100,
  Credentials = () =>
      let
        Fonte = "*********" & "*******",
        Bytes = Text.ToBinary(Fonte),
        Texto = Binary.ToText(Bytes, BinaryEncoding.Base64)
      in
        Texto,
  GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url,[Headers=[Authorization="Basic " & Credentials()]]),
            Json    = Json.Document(RawData)
        in  Json,
  GetJiraIDCount = () =>
        let Url   = BaseUrl & "&maxResults=0",
            Json  = GetJson(Url),
            Count = Json[#"total"]
        in  Count,
  GetPage = (Index) =>
        let Skip  = "&startAt=" & Text.From(Index * JiraIDPerPage),
            Top   = "&fields=*all&maxResults=" & Text.From(JiraIDPerPage),
            Url   = BaseUrl & Skip & Top,
            Json  = GetJson(Url),
            Value = Json[#"issues"]
        in  Value,
  JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
  PageCount = Number.RoundUp(JiraIDCount / JiraIDPerPage),
  PageIndices = { 0 .. PageCount - 1 },
  Pages = List.Transform(PageIndices, each GetPage(_)),
  JiraID = List.Union(Pages),
  #"Convertido em tabela" = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  #"Expandido Column1" = Table.ExpandRecordColumn(#"Convertido em tabela", "Column1", {"key", "fields"}, {"key", "fields"}),
  #"Expandido fields" = Table.ExpandRecordColumn(#"Expandido Column1", "fields", {"created", "resolutiondate", "summary", "timeestimate", "timeoriginalestimate", "timespent"}, {"created", "resolutiondate", "summary", "timeestimate", "timeoriginalestimate", "timespent"}),
    #"Tipo Alterado" = Table.TransformColumnTypes(#"Expandido fields",{{"resolutiondate", type text}, {"key", type text}, {"created", type text}, {"summary", type text}, {"timeestimate", type text}, {"timeoriginalestimate", type text}, {"timespent", type text}})
in
    #"Tipo Alterado"

But, when I try to save power service change my script and a receive the error: Unable to save the data flow. One or more tables reference a dynamic data source.

Any suggestions?

Thanks in advance.

[Coyote34 · Accepted solution]

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]

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

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