Forum Discussion

AhmadAbbadi's avatar
AhmadAbbadi
Frequent Visitor
8 years ago

Jira Power BI Content Pack custom fields didn't appear in Data Source (Header Name & Value)!

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

null

null

null

null

null

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 :)