Import JIRA Custom Fields using Powerbi's JIRA Connector

[atif-hafeez]

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 

https://disq.us/url?url=https%3A%2F%2F1drv.ms%2Fu%2Fs%21Aj8CVlXNweLnh48ffj_o4dfLqyITvg%3AYF5KS-Du0OJ...

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

[Alex_Hunter]

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. 

[antonstorozhuk]

Hi @atif-hafeez 

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 support@alpha-serve.com. 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.

[nitsud1]

Hello Microsoft... Are you listening to this request? This is a serious pain as 29 custom fields is nothing. 

When will this be resolved?

[rventura]

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/

[doyleg_OEH · Accepted solution]

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

[Anonymous]

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:

1. 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
2. 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

[Anonymous]

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

[MaxItaly]

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

[Yaniv]

Is someone in Microsoft going to handle it??

[doyleg_OEH]

Same issue. Only 29 Custom fields being imported from an on-premise JIRA instance.

[v-shex-msft]

Hi @atif-hafeez,

Based on test, I can import more than 20 fields to power bi. So I think there is no limitation on power bi app.

Regards,

Xiaoxin Sheng

[rventura]

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

[atif-hafeez]

I just gave up. The issue still exists and no one from the Powerbi seems to care..

[nitsud1]

This is an issue. I have 64 custom fields and only 29 show up in power bi.

MICROSOFT, LISTEN PLEASE AND FIX IT!!!!

[atif-hafeez]

Hi @v-shex-msft

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

[v-shex-msft]

Hi @atif-hafeez,

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

[vinod]

Hi Xiaoxin Sheng,

same issue am also facing, yes am also imported only 29 fields. 

your Quetion is Right according to the count of fields.

anyone please help me on this issue.

Thanks,

Vinod