Dynamic dataset refresh error with Atlassian/JIRA API

[Anonymous]

I'm currently atempting to use the Atlassian/Jira rest api to get data into a report which works fine in desktop but when I upload to service I get the error: 

Something went wrong 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.

If I just use the following part of the API url it works fine in service: 

https://xxx.atlassian.net/rest/api/3/search?maxResults=100

But since this only get me 100 results I found online that I had to use some way to get all the rows, so it ends up like this in the advanced editor, which is where it fails due to some of the components:

let
BaseUrl = "https://xxx.atlassian.net/rest/api/3/search?",
JQL = "jql=project=DA",
Fields = "&fields=id,key,created,updated,worklog",
PageSize = 100,
    GetPage = (StartIndex as number) =>
        let
            Url = BaseUrl & JQL & Fields & "&startAt=" & Number.ToText(StartIndex) & "&maxResults=" & Number.ToText(PageSize),
            Source = Json.Document(Web.Contents(Url)),
            Issues = Source[issues] 
        in
            Issues,

    FirstPage = Json.Document(Web.Contents(BaseUrl & JQL & Fields & "&startAt=0&maxResults=1")),
    TotalIssues = FirstPage[total],
    PageNumbers = List.Numbers(0, Number.RoundUp(TotalIssues / PageSize), PageSize), 

    AllPages = List.Transform(PageNumbers, each GetPage(_)),

    CombinedData = Table.FromList(List.Combine(AllPages), Splitter.SplitByNothing(), {"Data"}),

    ExpandedData = Table.ExpandRecordColumn(CombinedData, "Data", {"id", "key", "fields"}),
    FinalTable = Table.ExpandRecordColumn(ExpandedData, "fields", {"created", "updated", "worklog"}),
    #"Expanded worklog" = Table.ExpandRecordColumn(FinalTable, "worklog", {"worklogs"}, {"worklogs"}),
    #"Expanded worklogs" = Table.ExpandListColumn(#"Expanded worklog", "worklogs"),
    #"Expanded worklogs1" = Table.ExpandRecordColumn(#"Expanded worklogs", "worklogs", {"self", "author", "updateAuthor", "created", "updated", "started", "timeSpent", "id", "issueId"}, {"self", "author", "updateAuthor", "created.1", "updated.1", "started", "timeSpent", "id.1", "issueId"}),
    #"Expanded author" = Table.ExpandRecordColumn(#"Expanded worklogs1", "author", {"displayName"}, {"displayName"}),
    #"Expanded updateAuthor" = Table.ExpandRecordColumn(#"Expanded author", "updateAuthor", {"displayName"}, {"displayName.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded updateAuthor", "Time Spent SUM", each if [timeSpent] = null then 0
else
    (if Text.Contains([timeSpent], "d") 
     then Number.From(Text.BeforeDelimiter([timeSpent], "d")) * 24 
     else 0) +
    (if Text.Contains([timeSpent], "h") 
     then 
         Number.From(
             if Text.Contains([timeSpent], "d") 
             then Text.BeforeDelimiter(Text.AfterDelimiter([timeSpent], "d"), "h")
             else Text.BeforeDelimiter([timeSpent], "h")
         ) 
     else 0) +
    (if Text.Contains([timeSpent], "m") 
     then 
         Number.From(
             if Text.Contains([timeSpent], "h") 
             then Text.BeforeDelimiter(Text.AfterDelimiter([timeSpent], "h"), "m")
             else if Text.Contains([timeSpent], "d") 
             then Text.BeforeDelimiter(Text.AfterDelimiter([timeSpent], "d"), "m")
             else Text.BeforeDelimiter([timeSpent], "m")
         ) / 60 
     else 0)),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Time Spent SUM"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Errors",{{"Time Spent SUM", type number}})
in
    #"Changed Type"

I'm assuming somewhere in the begining with total pages, first pages etc is where the issue is? 

[lbendlin · Accepted solution]

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