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"
Helpful resources
Power BI Monthly Update - April 2026
Check out the April 2026 Power BI update to learn about new features.
Data Days 2026 coming soon!
Sign up to receive a private message when registration opens and key events begin.
New to Fabric Survey
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
Power BI DataViz World Championships - June 2026
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.
