Request: I need guidance on how to modify the query for JIRA content pack for Power BI using Advanced Editor. My goal is to add in some custom fields.
Why: We use Epics to join together other Jira Issue types. However, the template treats all issues as the same.
Description of the Dashboard I want to build:
- Top: A slicer object where open epics are listed (Issue Type = Epic, Status = open)
- When an open epic is selected, show below
- Card with count of total number of issues in the Epic (Issue Type = bug, story, chore)
- A donut chart showing the status of the issues within that epic
- A gannt chart showing expected Dev and QA completion timeline
Fields I need added:
- Epic Link customfield_10900
- Epic Name customfield_10901
- Dev Start customfield_12404
- Dev End customfield_12400
- QA start customfield_12401
- QA end customfield_12402
- Percent Dev Complete customfield_12405
- Percent QA Complete customfield_12406
Here is the query for the Jira Conent Pack to which I need to add custom fields:
let
Source = FetchPages("", 500),
#"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"expand", "id", "self", "key", "fields"}, {"expand", "id", "self", "key", "fields"}),
#"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"issuetype", "timespent", "project", "fixVersions", "customfield_10110", "customfield_10111", "aggregatetimespent", "resolution", "customfield_10112", "customfield_10113", "customfield_10114", "customfield_10104", "customfield_10105", "customfield_10106", "customfield_10107", "customfield_10108", "customfield_10109", "resolutiondate", "workratio", "lastViewed", "watches", "created", "priority", "customfield_10100", "customfield_10101", "customfield_10102", "customfield_10103", "labels", "timeestimate", "aggregatetimeoriginalestimate", "versions", "issuelinks", "assignee", "updated", "status", "components", "timeoriginalestimate", "description", "customfield_10006", "customfield_10009", "aggregatetimeestimate", "summary", "creator", "subtasks", "reporter", "customfield_10000", "aggregateprogress", "customfield_10001", "customfield_10004", "customfield_10115", "customfield_10116", "customfield_10117", "environment", "customfield_10118", "customfield_10119", "duedate", "progress", "votes", "parent", "customfield_10005", "customfield_10007", "customfield_10008", "customfield_10002", "customfield_10003"}, {"issuetype", "timespent", "project", "fixVersions", "customfield_10110", "customfield_10111", "aggregatetimespent", "resolution", "customfield_10112", "customfield_10113", "customfield_10114", "customfield_10104", "customfield_10105", "customfield_10106", "customfield_10107", "customfield_10108", "customfield_10109", "resolutiondate", "workratio", "lastViewed", "watches", "created", "priority", "customfield_10100", "customfield_10101", "customfield_10102", "customfield_10103", "labels", "timeestimate", "aggregatetimeoriginalestimate", "versions", "issuelinks", "assignee", "updated", "status", "components", "timeoriginalestimate", "description", "customfield_10006", "customfield_10009", "aggregatetimeestimate", "summary", "creator", "subtasks", "reporter", "customfield_10000", "aggregateprogress", "customfield_10001", "customfield_10004", "customfield_10115", "customfield_10116", "customfield_10117", "environment", "customfield_10118", "customfield_10119", "duedate", "progress", "votes", "parent", "customfield_10005", "customfield_10007", "customfield_10008", "customfield_10002", "customfield_10003"}),
#"Expanded components" = Table.ExpandListColumn(#"Expanded fields", "components"),
#"Expanded components1" = Table.ExpandRecordColumn(#"Expanded components", "components", {"name"}, {"components.name"}),
#"Expanded fixVersions" = Table.ExpandListColumn(#"Expanded components1", "fixVersions"),
#"Expanded fixVersions1" = Table.ExpandRecordColumn(#"Expanded fixVersions", "fixVersions", {"name"}, {"fixVersions.name"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded fixVersions1",{"expand"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [id] <> null and [id] <> ""),
#"Expanded issuetype" = Table.ExpandRecordColumn(#"Filtered Rows", "issuetype", {"name", "subtask"}, {"issuetype.name", "issuetype.subtask"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded issuetype",{{"issuetype.name", "issuetype"}, {"issuetype.subtask", "isSubtask"}}),
#"Expanded project" = Table.ExpandRecordColumn(#"Renamed Columns", "project", {"id", "key", "name"}, {"project.id", "project.key", "project.name"}),
#"Expanded watches" = Table.ExpandRecordColumn(#"Expanded project", "watches", {"isWatching"}, {"isWatching"}),
#"Expanded priority" = Table.ExpandRecordColumn(#"Expanded watches", "priority", {"name"}, {"name"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded priority",{{"name", "priority"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"customfield_10102"}),
#"Expanded labels" = Table.ExpandListColumn(#"Removed Columns2", "labels"),
#"Removed Columns3" = Table.RemoveColumns(#"Expanded labels",{"labels", "versions", "issuelinks"}),
#"Expanded assignee" = Table.ExpandRecordColumn(#"Removed Columns3", "assignee", {"name", "emailAddress", "displayName", "active"}, {"assignee.name", "assignee.emailAddress", "assignee.displayName", "assignee.active"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded assignee",{{"assignee.displayName", "assignee"}, {"resolutiondate", "resolution date"}}),
#"Expanded status" = Table.ExpandRecordColumn(#"Renamed Columns2", "status", {"name", "statusCategory"}, {"status.name", "status.statusCategory"}),
#"Expanded status.statusCategory" = Table.ExpandRecordColumn(#"Expanded status", "status.statusCategory", {"colorName", "name"}, {"status.statusCategory.colorName", "status.statusCategory.name"}),
#"Removed Columns4" = Table.RemoveColumns(#"Expanded status.statusCategory",{"subtasks"}),
#"Expanded reporter" = Table.ExpandRecordColumn(#"Removed Columns4", "reporter", {"name", "emailAddress", "displayName", "active"}, {"reporter.name", "reporter.emailAddress", "reporter.displayName", "reporter.active"}),
#"Expanded aggregateprogress" = Table.ExpandRecordColumn(#"Expanded reporter", "aggregateprogress", {"progress", "total"}, {"aggregateprogress.progress", "aggregateprogress.total"}),
#"Expanded progress" = Table.ExpandRecordColumn(#"Expanded aggregateprogress", "progress", {"progress", "total"}, {"progress.progress", "progress.total"}),
#"Expanded votes" = Table.ExpandRecordColumn(#"Expanded progress", "votes", {"votes"}, {"votes.votes"}),
#"Expanded parent" = Table.ExpandRecordColumn(#"Expanded votes", "parent", {"id"}, {"parent.id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded parent",{{"resolution date", type datetimezone}, {"created", type datetimezone}, {"updated", type datetimezone}}),
#"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([resolution date]), type date),
#"Renamed Columns3" = Table.RenameColumns(#"Inserted Date",{{"Date", "Resolution Day"}}),
#"Inserted End of Week" = Table.AddColumn(#"Renamed Columns3", "EndOfWeek", each Date.EndOfWeek([Resolution Day]), type date),
#"Renamed Columns4" = Table.RenameColumns(#"Inserted End of Week",{{"EndOfWeek", "Resolution Week"}}),
#"Inserted Date1" = Table.AddColumn(#"Renamed Columns4", "Date", each DateTime.Date([created]), type date),
#"Renamed Columns5" = Table.RenameColumns(#"Inserted Date1",{{"Date", "Created Day"}}),
#"Inserted End of Week1" = Table.AddColumn(#"Renamed Columns5", "EndOfWeek", each Date.EndOfWeek([Created Day]), type date),
#"Renamed Columns6" = Table.RenameColumns(#"Inserted End of Week1",{{"EndOfWeek", "Created Week"}})
in
#"Renamed Columns6"
Additional context
Previously, I've been using the API to set up a unique query for each epic. This is time intensive and not ideal. The output was a gannt style chart that showed when we expect to start and end dev work and QA. A sample query is below.
let
Source = Json.Document(Web.Contents("SOURCE REDACTED")),
fields = Source[fields],
#"Converted to Table" = Record.ToTable(fields),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table"),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"customfield_12404", "customfield_10901", "customfield_12400", "customfield_12402", "customfield_12401", "description", "customfield_12405", "customfield_12406"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"customfield_12404", "Dev Start"}, {"customfield_10901", "Epic Name"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Epic Name", "Dev Start", "customfield_12400", "customfield_12402", "customfield_12401"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"customfield_12400", "Dev End"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Epic Name", "Dev Start", "Dev End", "customfield_12405", "customfield_12401", "customfield_12402", "customfield_12406", "description"}),
#"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns1",{{"customfield_12401", "QA Start"}, {"customfield_12402", "QA End"}, {"customfield_12405", "Percent Dev Complete"}, {"customfield_12406", "Percent QA Complete"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Dev Start", type text}, {"Dev End", type text}, {"Percent Dev Complete", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"Dev Start", "Dev End", "Percent Dev Complete"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Dev"),
#"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns",{{"QA Start", type text}, {"QA End", type text}, {"Percent QA Complete", type text}}),
#"Merged Columns1" = Table.CombineColumns(#"Changed Type3",{"QA Start", "QA End", "Percent QA Complete"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"QA"),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Epic Name", "description"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns","Value",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Value.1", "Value.2", "Value.3"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type date}, {"Value.2", type date}, {"Value.3", type number}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type",{{"Value.1", "Start"}, {"Value.2", "End"}, {"Attribute", "Resource"}, {"Value.3", "Percent Complete"}})
in
#"Renamed Columns3"
