Forum Discussion
Import JIRA Custom Fields using Powerbi's JIRA Connector
I used the Jira connector provided by Powerbi web app. It only imports 20 29 custom fields. Is this a limitation of the PowerBi App?
Ok Guys, So i downloaded the PowerBI JIRA Template file from the below link
I entered the url and authentication information and waited for it to download the data from my jira url. As expected, it only showed 29 custom fields. So i went into edit query and noticed that there is a step named expand fields defined for the GetIssues Table. When i edited that step, i noticed that only certain custom fields were selected, athough i could see additional custom fields as well. it seems that if all values for a given column are null, the expand field ignores it. Or the expand field step is configured in such a way that it only selects 29 customfields. I suspect this where the problem lies and this is the reason most of us only see 29 custom fields.
Anyways, it had an option to expand the search to more rows, but it could only scan 1000 rows. If we can scan more than 1000 rows, probably it will find that certain customfield dont always have null values. I did some reseacrch on the internet and found that there is a jira-config.properties file that resides on the JIRA Application folder on the server . This file has a configurable parameter called jira.search.views.default.max whose value is set to 1000. Any application that uses the rest/api/2/search url to pull issues can import not more than 1000 issues. If you look at the fetchpage function in the edit data window, you will notice that Powerbi uses the rest/api/2/search url. Now i dont have access the our JIRA server and therefore, i cannot test this theory. If anyone has access to the server, can they please confirm this?
The rest/api/2/search also accept queries. So i am going to modify the function to pull only issues that are of type epic (because i am interested in custom field that are mapped to epic issues only) and see if it imports all custom field. [Update] So i was able to modify the function to include my jql query and have been able to import all costum fields related to those issues in the desktop version. Note that my query return around 550 results so the above case is yet to be verified.
Any other feedback or finding will be highly appreciated
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
19 Replies
Hi Anonymous,
>>Can you count how many fields you were able to import? I rechecked and its giving me not more than 29 fields. I have corrected the number in my question
My workbook has 50 columns, after I import to power bi service, it has more than 50 columns(these new columns are created by JIRA).
Regards,
Xiaoxin Sheng
Were you able to get the custom fields data into power bi? All mines are empty.
If so, let me know what you did.
Thanks
doyleg_OEHAdvocate II
Same issue. Only 29 Custom fields being imported from an on-premise JIRA instance.
YanivHelper I
Is someone in Microsoft going to handle it??
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_OEH
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?
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/
antonstorozhukHelper V
Hi Anonymous
For the Custom Fields export you might want to try out our app - Power BI Connector for Jira: https://marketplace.atlassian.com/apps/1221150/power-bi-connector-for-jira?hosting=cloud&tab=overvie... it's available for Jira Cloud / Server / Data Center.
We have a handy User/Admin Guide https://aserve.atlassian.net/wiki/spaces/PBCFJC/overview and fast/responsive support team at [email protected]. Please let us know if you need any assistance.
Cheers!
Anton
===
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi, I'm trying to connect my jira project using JIRA connector in powerBI and export and data. But there was a column with the keyvalue pairs, but when PowerBi connector tried importing this data, the values are displayed null. Can anyone kindly help me, I was so stuck for this from few days. I tried using the REST Api url, but no luck.
