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
Hello Microsoft... Are you listening to this request? This is a serious pain as 29 custom fields is nothing.
When will this be resolved?
doyleg_OEHAdvocate II
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
- MaxItaly
Helper III
I tried doing it and I'm getting the new column, but it doesn't seem to get the actual values, but only "[Record]".
I thought I had to expand the column, but in the query editor I have not the possibility to expand it (by using the two diverging arrows).
I tried to add this row to the query in the end " #"DigitalProperty" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"customfield_xxxxx"},{"xxxxxxxxx"})" but it does not work.
Have you experienced something similar?- doyleg_OEH
Hi,
Just comparing your query step to mine I would suggest you change it to:
= Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"value"},{"xxxxxxxxx"})
Here is an example of one of my Expand Record query steps:
= Table.ExpandRecordColumn(#"Expanded Organisation_type", "Source", {"value"}, {"Source"})
Where:
The previous step in the applied steps list
The name you gave to the Jira custom field in the "Expand Fields" step
This is one of the Record parameters. "Value" is the actual field text from the Jira data model that appears in Jira and is the most useful (for me anyway) in Power BI
The name of the expanded field in the Power BI query, in this case it over rights the original to keep my query clean.
Hope this is of some help.
G
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
Well, it's not really Power BI's fault. It's how jira sends the fields to Power BI. If you actually create a new data source, and use the RESTful API, you can get all the fields, then just expand those custom fields to see the data and actual field names. Now, the only problem is that Jira changed their API and they will only send back 100 records.
You will need to do pagination in order to be able to get all Jira issues. Look at this: https://jira.atlassian.com/browse/JRACLOUD-67570
Also, i did this and that's how i got all my data: http://datachix.com/2014/05/22/power-query-functions-some-scenarios/
