Forum Discussion
Ausar
2 years agoHelper I
Edit code to bring 'Labels' field from Jira as well
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 t...
BiAnalyst
2 years agoResolver I
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!