Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Coyote34
Frequent Visitor

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

1 ACCEPTED SOLUTION
Coyote34
Frequent Visitor

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"

 

View solution in original post

2 REPLIES 2
Coyote34
Frequent Visitor

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"

 

v-jialongy-msft
Community Support
Community Support

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.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors