Jira dashboard include a dynamic data source. Since dynamic data sources aren't refreshed

[golia]

I am getting below error when trying to refresh Jira dashboard .

Can you please help me to convert this query to static mode?

let 
    BaseUrl = "https://metrolinx.atlassian.net/rest/api/2/search?jql=project in ('BST') ",

    JiraIDPerPage = 100,
 
    GetJson = (Url) =>
        let 
            RawData = Web.Contents(Url),
            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   = "&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),
    Table       = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"id", "key", "fields"}, {"Column1.id", "Column1.key", "Column1.fields"}),
    #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"statuscategorychangedate", "fixVersions", "resolution", "lastViewed", "priority", "timeestimate", "versions", "issuelinks", "assignee", "status", "components", "creator", "subtasks", "reporter", "progress", "votes", "issuetype", "timespent", "project", "customfield_10424", "resolutiondate", "workratio", "watches", "created", "updated", "timeoriginalestimate", "description", "customfield_10130", "customfield_10010", "customfield_10011", "customfield_10012", "security", "summary", "customfield_10000", "customfield_10001", "customfield_10004", "environment", "duedate", "parent", "customfield_10035", "customfield_10027", "customfield_10028", "customfield_10029", "issuerestriction", "customfield_10023", "customfield_10017", "customfield_10015", "attachment", "customfield_10002", "customfield_10003", "comment", "worklog", "customfield_10025", "customfield_10026"}, {"statuscategorychangedate", "fixVersions", "resolution", "lastViewed", "priority", "timeestimate", "versions", "issuelinks", "assignee", "status", "components", "creator", "subtasks", "reporter", "progress", "votes", "issuetype", "timespent", "project", "customfield_10424", "resolutiondate", "workratio", "watches", "created", "updated", "timeoriginalestimate", "description", "customfield_10130", "customfield_10010", "customfield_10011", "customfield_10012", "security", "summary", "customfield_10000", "customfield_10001", "customfield_10004", "environment", "duedate", "parent", "customfield_10035", "customfield_10027", "customfield_10028", "customfield_10029", "issuerestriction", "customfield_10023", "customfield_10017", "customfield_10015", "attachment", "customfield_10002", "customfield_10003", "comment", "worklog", "customfield_10025", "customfield_10026"}),
    #"Expanded assignee1" = Table.ExpandRecordColumn(#"Expanded Column1.fields", "assignee", {"displayName"}, {"assignee.displayName"}),
    #"Expanded priority" = Table.ExpandRecordColumn(#"Expanded assignee1", "priority", {"name"}, {"priority.name"}),
    #"Expanded status" = Table.ExpandRecordColumn(#"Expanded priority", "status", {"name"}, {"status.name"}),
    #"Expanded creator" = Table.ExpandRecordColumn(#"Expanded status", "creator", {"displayName"}, {"creator.displayName"}),
    #"Expanded reporter" = Table.ExpandRecordColumn(#"Expanded creator", "reporter", {"displayName"}, {"reporter.displayName"}),
    #"Expanded progress" = Table.ExpandRecordColumn(#"Expanded reporter", "progress", {"total"}, {"total"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded progress",{"environment", "security", "watches", "workratio"}),
    #"Expanded project" = Table.ExpandRecordColumn(#"Removed Columns", "project", {"name"}, {"name"}),
    #"Expanded votes" = Table.ExpandRecordColumn(#"Expanded project", "votes", {"votes"}, {"votes.1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded votes",{"subtasks", "components", "issuelinks", "versions"}),
    #"Expanded issuetype" = Table.ExpandRecordColumn(#"Removed Columns1", "issuetype", {"name", "hierarchyLevel"}, {"issuetype.name", "issuetype.hierarchyLevel"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded issuetype",{"issuetype.hierarchyLevel"}),
    #"Expanded resolution1" = Table.ExpandRecordColumn(#"Removed Columns2", "resolution", {"description", "name"}, {"resolution.description", "resolution.name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded resolution1",{{"Column1.key", "IssueID"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","T"," ",Replacer.ReplaceText,{"statuscategorychangedate"}),
    #"Split Column by Position" = Table.SplitColumn(#"Replaced Value", "statuscategorychangedate", Splitter.SplitTextByRepeatedLengths(23), {"statuscategorychangedate.1", "statuscategorychangedate.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Position",{{"statuscategorychangedate.1", type datetime}, {"statuscategorychangedate.2", Int64.Type}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Changed Type",{"statuscategorychangedate.2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns3",{{"statuscategorychangedate.1", "statuscategorychangedate"}, {"issuetype.name", "issuetype"}}),
    #"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns1",{  "fixVersions"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns4",{{"resolution.description", "resolution"}}),
    #"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns2",{"customfield_10035", "customfield_10027", "customfield_10028", "customfield_10029", "issuerestriction"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Removed Columns5","T"," ",Replacer.ReplaceText,{"created"}),
    #"Split Column by Position1" = Table.SplitColumn(#"Replaced Value1", "created", Splitter.SplitTextByRepeatedLengths(23), {"created.1", "created.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position1",{{"created.1", type datetime}, {"created.2", Int64.Type}}),
    #"Removed Columns6" = Table.RemoveColumns(#"Changed Type1",{"created.2"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns6",{{"created.1", "createdDate"}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Renamed Columns3",{{"priority.name", "priority"}}),
    #"Renamed Columns5" = Table.RenameColumns(#"Renamed Columns4",{{"assignee.displayName", "assignee"}, {"status.name", "status"}}),
    #"Renamed Columns6" = Table.RenameColumns(#"Renamed Columns5",{{"creator.displayName", "creator"}, {"reporter.displayName", "reporter"}}),
    #"Removed Columns10" = Table.RemoveColumns(#"Renamed Columns6",{ "customfield_10000", "customfield_10001", "customfield_10002", "customfield_10003", "customfield_10004", "total", "votes.1", "customfield_10025", "customfield_10012", "customfield_10011", "customfield_10010",  "customfield_10026"}),
    #"Renamed Columns7" = Table.RenameColumns(#"Removed Columns10",{{"resolutiondate", "Completion_date"}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns7","T"," ",Replacer.ReplaceText,{"Completion_date"}),
    #"Split Column by Position2" = Table.SplitColumn(#"Replaced Value2", "Completion_date", Splitter.SplitTextByRepeatedLengths(23), {"Completion_date.1", "Completion_date.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position2",{{"Completion_date.1", type datetime}, {"Completion_date.2", Int64.Type}}),
    #"Removed Columns11" = Table.RemoveColumns(#"Changed Type2",{"Completion_date.2"}),
    #"Renamed Columns8" = Table.RenameColumns(#"Removed Columns11",{{"Completion_date.1", "Completion_date"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns8",{{"IssueID", Order.Descending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"IssueID"}, #"WorkItems History", {"IssueID"}, "WorkItems History", JoinKind.LeftOuter),
    #"Expanded WorkItems History" = Table.ExpandTableColumn(#"Merged Queries", "WorkItems History", {"IssueType", "hierarchyLevel", "Field", "From", "To", "ChangedDate2"}, {"WorkItems History.IssueType", "WorkItems History.hierarchyLevel", "WorkItems History.Field", "WorkItems History.From", "WorkItems History.To", "WorkItems History.ChangedDate2"}),
    #"Replaced Value3" = Table.ReplaceValue(#"Expanded WorkItems History","New User Story \ Change Request","User Story \ CR",Replacer.ReplaceText,{"issuetype"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Replaced Value3",{{"WorkItems History.ChangedDate2", type datetime}}),
    #"Renamed Columns9" = Table.RenameColumns(#"Changed Type3",{{"WorkItems History.ChangedDate2", "ChangedDate_correct"}}),
    #"Expanded customfield_10130" = Table.ExpandListColumn(#"Renamed Columns9", "customfield_10130"),
    #"Expanded customfield_1" = Table.ExpandRecordColumn(#"Expanded customfield_10130", "customfield_10130", {"displayName"}, {"customfield_10130.displayName"}),
    #"Renamed Columns10" = Table.RenameColumns(#"Expanded customfield_1",{{"customfield_10130.displayName", "ActionOwner"}}),
    #"Expanded customfield_10424" = Table.ExpandRecordColumn(#"Renamed Columns10", "customfield_10424", {"value"}, {"customfield_10424.value"}),
    #"Renamed Columns11" = Table.RenameColumns(#"Expanded customfield_10424",{{"customfield_10424.value", "Story Time Estimate"}}),
    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns11", {"status"}, status_threshold, {"status"}, "status_threshold", JoinKind.LeftOuter),
    #"Merged Queries2" = Table.NestedJoin(#"Merged Queries1", {"Story Time Estimate"}, #"Story Time Estimate", {"Story Time Estimate"}, "Story Time Estimate.1", JoinKind.LeftOuter),
    #"Expanded status_threshold" = Table.ExpandTableColumn(#"Merged Queries2", "status_threshold", {"status_threshold", "statusno"}, {"status_threshold.status_threshold", "status_threshold.statusno"}),
    #"Expanded Story Time Estimate.1" = Table.ExpandTableColumn(#"Expanded status_threshold", "Story Time Estimate.1", {"threshold_hour"}, {"Story Time Estimate.1.threshold_hour"}),
    #"Merged Queries3" = Table.NestedJoin(#"Expanded Story Time Estimate.1", {"IssueID"}, Label, {"IssueId"}, "Label", JoinKind.LeftOuter),
    #"Expanded Label" = Table.ExpandTableColumn(#"Merged Queries3", "Label", {"labels"}, {"labels"}),
    #"Expanded parent" = Table.ExpandRecordColumn(#"Expanded Label", "parent", {"fields"}, {"parent.fields"}),
    #"Expanded parent.fields" = Table.ExpandRecordColumn(#"Expanded parent", "parent.fields", {"summary"}, {"parent.fields.summary"}),
    #"Renamed Columns12" = Table.RenameColumns(#"Expanded parent.fields",{{"parent.fields.summary", "Epic"}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Renamed Columns12", "Epic", "Epic - Copy"),
    #"Replaced Value4" = Table.ReplaceValue(#"Duplicated Column","[OPR]","",Replacer.ReplaceText,{"Epic"}),
    #"Replaced Value5" = Table.ReplaceValue(#"Replaced Value4","[PRJ]","",Replacer.ReplaceText,{"Epic"}),
    #"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","Production","Prod",Replacer.ReplaceText,{"Epic"}),
    #"Replaced Value7" = Table.ReplaceValue(#"Replaced Value6","Development","Dev",Replacer.ReplaceText,{"Epic"}),
    #"Replaced Value8" = Table.ReplaceValue(#"Replaced Value7","Activities","",Replacer.ReplaceText,{"Epic"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value8", "Missing_label", each if [labels] = null then "Label" else ""),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Assignee_missing", each if [assignee] = null then "Assignee" else ""),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Missing_ActionOwner", each if [ActionOwner]=null then "Action Owner" else ""),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom2", "resolution_missing", each if [resolution] = null then "Resolution" else null),
    #"Added Custom4" = Table.AddColumn(#"Added Conditional Column", "Epic_missing", each if [Epic]=null then "Epic" else ""),
    #"Added Custom3" = Table.AddColumn(#"Added Custom4", "Missing", each "Missing " & [Missing_label]&" "&[Assignee_missing]&" "&[Missing_ActionOwner]&" "&[Epic_missing]),
    #"Replaced Value9" = Table.ReplaceValue(#"Added Custom3",null,"Missing Label",Replacer.ReplaceValue,{"labels"}),
    #"Replaced Value10" = Table.ReplaceValue(#"Replaced Value9","BST_Reporting","Reporting",Replacer.ReplaceText,{"labels"}),
    #"Replaced Value11" = Table.ReplaceValue(#"Replaced Value10","BST_Integration","Integration",Replacer.ReplaceText,{"labels"}),
    #"Replaced Value12" = Table.ReplaceValue(#"Replaced Value11","BST_EcoSys","EcoSys",Replacer.ReplaceText,{"labels"}),
    #"Replaced Value13" = Table.ReplaceValue(#"Replaced Value12","BST_Unifier_BP_Development","Unifier BP Dev",Replacer.ReplaceText,{"labels"})
in
    #"Replaced Value13"