Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi everyone,
I finally managed to connect Jira and PowerBI thanks to this amazing Github page.
It is possible to bring all your jira issues fromm all projects and filters to PowerBI. However, the template does not include labels. Maybe someone knows what to edit in Advanced editor so it also gets labels as well? Would be nice if someone downlads pbit from this github then checks code in advanced editor and show me what to add there to bring 'Labels' field as well.
So what I want is to see a monthly percentage of FastTracks. For that, subtasks with 'FastTrack' label should be divided by the total number of subtasks. Let's say, I have 500 subtasks in total and 25 of them have 'FastTrack' label, hence 25/500*100= 5%. Unfortunately, in Jira you cannot do calculations. So I am thinking that if I bring 'Label' field to PowerBI I will be able to perform calcualtions in PowerBI.
EDIT
Here is the code:
let
Source = FetchPages("", 100),
#"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"
let
Source = FetchPages("", 100),
#"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"}, {"priority.name"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded priority",{{"priority.name", "priority"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"customfield_10102"}),
#"Expanded labels" = Table.ExpandListColumn(#"Removed Columns2", "labels"),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded labels",{{"labels", "label"}}), // Rename 'labels' to 'label' for easier handling
#"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"versions", "issuelinks"}),
#"Expanded assignee" = Table.ExpandRecordColumn(#"Removed Columns3", "assignee", {"name", "emailAddress", "displayName", "active"}, {"assignee.name", "assignee.emailAddress", "assignee.displayName", "assignee.active"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded assignee",{{"assignee.displayName", "assignee"}, {"resolutiondate", "resolution date"}}),
#"Expanded status" = Table.ExpandRecordColumn(#"Renamed Columns3", "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 Columns4" = Table.RenameColumns(#"Inserted Date",{{"Date", "Resolution Day"}}),
#"Inserted End of Week" = Table.AddColumn(#"Renamed Columns4", "EndOfWeek", each Date.EndOfWeek([Resolution Day]), type date),
#"Renamed Columns5" = Table.RenameColumns(#"Inserted End of Week",{{"EndOfWeek", "Resolution Week"}}),
#"Inserted Date1" = Table.AddColumn(#"Renamed Columns5", "Date", each DateTime.Date([created]), type date),
#"Renamed Columns6" = Table.RenameColumns(#"Inserted Date1",{{"Date", "Created Day"}}),
#"Inserted End of Week1" = Table.AddColumn(#"Renamed Columns6", "EndOfWeek", each Date.EndOfWeek([Created Day]), type date),
#"Renamed Columns7" = Table.RenameColumns(#"Inserted End of Week1",{{"EndOfWeek", "Created Week"}})
in
#"Renamed Columns7"
Try this updated code. might do the trick!
User | Count |
---|---|
23 | |
18 | |
16 | |
13 | |
12 |