Forum Discussion
Import JIRA Custom Fields using Powerbi's JIRA Connector
All,
Solution is to modify the "Expanded Fields" step of the import query to include the additional fields.
Here is my modified query step as an example:
= 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", "customfield_10505", "customfield_10303", "customfield_10305", "customfield_10507", "customfield_10504", "customfield_10300", "customfield_10219", "customfield_10217"},
{"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", "Directed_to", "Escalated_to", "IT_System", "Organisation_type", "Source", "Time_to_allocated", "Time_to_close_after_Res", "Time_to_Res"})
You do this by by adding in the Jira field details you want to include i.e. "customfield_10217" at the end of the first set of parentheses and give it a more useful name at the end of the second set of parentheses i.e. "Time_to_Res". Do this in pairs for each additional custom field (or Standard field) you want to include in the query. To find the custom field ID use this guide: https://confluence.atlassian.com/jirakb/how-to-find-id-for-custom-field-s-744522503.html
Hope this helps.
Gavin
Advocate IIAll,
Solution is to modify the "Expanded Fields" step of the import query to include the additional fields.
Here is my modified query step as an example:
= 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", "customfield_10505", "customfield_10303", "customfield_10305", "customfield_10507", "customfield_10504", "customfield_10300", "customfield_10219", "customfield_10217"},
{"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", "Directed_to", "Escalated_to", "IT_System", "Organisation_type", "Source", "Time_to_allocated", "Time_to_close_after_Res", "Time_to_Res"})
You do this by by adding in the Jira field details you want to include i.e. "customfield_10217" at the end of the first set of parentheses and give it a more useful name at the end of the second set of parentheses i.e. "Time_to_Res". Do this in pairs for each additional custom field (or Standard field) you want to include in the query. To find the custom field ID use this guide: https://confluence.atlassian.com/jirakb/how-to-find-id-for-custom-field-s-744522503.html
Hope this helps.
Gavin
Thanks Gavin,
This was very helpful. I added the "developer" field in both places as you illustrated. However, when the query finished refreshing the developer column is blank.
In the Expanded fields step other similar fields such as assignee, creator and reporter appear as a record that can be expanded.
However, the developer field does not have this.
Do you have advice for how to bring in developer in such a way that we can get the user's name?
Sincerely, Jodi
- doyleg_OEH
Not sure but two things I would check are:
- Is there data in that field in your Jira instance. I would build a filter in Jira to check that there is data in that field for the issues you are looking at in Power BI
- Have you got the right custom field ID. Pretty easy to get this messed up, particulalrly if you are doing a lot of them together
If not one of these two causes then I am no help.
Good luck.
G
After following your guide on obtaining the field ID, it appears the problem was the second one. Once the custom field ID was corrected, the query editor showed the new column and I was able to expand the record.
Thank you for your help.
Jodi