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

[Ahmad Al-Abbadi]

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 screenshot below: 

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 as in screenshot:

My Instance: Jira SD / Cloud

 Appreciate your support :)

[Tiago Machado · Accepted solution]

Hi @Ahmad Al-Abbadi,

In order to get the custom_field "human" names, you need to perform another REST call at:

https://xxxxxx.atlassian.net/rest/api/2/issue/createmeta?expand=projects.issuetypes.fields

(where xxxx is your cloud domain)

And then rename the columns on your Power BI.

If there aren't many customfields, it is faster to hardcode the values (the above REST call takes some time to return data - specially when the number of projects you have on your cloud instance increases)

Some helpful references:

https://community.atlassian.com/t5/Answers-Developer-Questions/Custom-Field-Name-in-Jira-JSON/qaq-p/565157

https://developer.atlassian.com/cloud/jira/platform/rest/?_ga=2.106182916.649572337.1531849237-1060599815.1521556165#api-api-2-issue-createmeta-get

[Ahmad Al-Abbadi]

Thanks @Tiago Machado

Do you advise me to keep using PowerBI Content Pack and add more queries on this to retrieve the required reporting fields ? if true how can I edit the Advanced Query by adding what do you have example if I have custom field with the following details:

Custom field name : Sector

ID: 10400

Data type : Select (List)

How do I structure it in existing query? Examples;

#"Inserted End of Week1" = Table.AddColumn(#"Renamed Columns5" "EndOfWeek", each Date.EndOfWeek([Created Day]), type date),

OR

#"Expanded reporter" = Table.ExpandRecordColumn(#"Removed Columns4", "reporter", {"name", "emailAddress", "displayName", "active"}, {"reporter.name", "reporter.emailAddress", "reporter.displayName", "reporter.active"}),

How can I make it as column header name sector and all data listed below it!

Am working with single project with approx 3000 issues, I need to get all issues and all details as am exporting ALL FIELDS as CSV 

[Regional Administrator]

Hi @Tiago Machado

Thanks again for sharing script in previous post. 

I used it and find out all data retried successfully as following, each 100 issues in Record [ex. Have 3000 Issues, 30 record retrieved each has 100 issues] by calling StartAt + MaxResult in loop based on total number at start script Total value :)

- The issue now is how to export / expand all these Records which contain issues into a single table (All / 3000 issues )?!?

Thanks you genius,

[Tiago Machado]

Congratulations !! :)

I'm pleased to know you are being able to query data from JIRA.

About converting Records to data, I think you should convert the column to table, as explained here