After configure Jira Power BI Content Pack, custom fields shown as CustumField_10400 (Custom field ID Number) and No data / values in column as shown in table below:
customfield_10110
Data appear as null! Some type of data appear as Record or List (as in screenshot above)!
Using GetIssues, How can I retrieve the data in the right name (Field Name - Header) and get the values in correct data type ???
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"
Most of default field get correct data values and header name, but some cases get data as Record or List (both Hyperlink) as in table:
customfield_10003
[List] |
[List] |
[List] |
[List] |
[List] |
My Instance: Jira SD / Cloud
Appreciate your support 🙂
