Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 t...
Need Help
  • doyleg_OEH's avatar
    doyleg_OEH
    8 years ago

    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's avatar
MaxItaly
Icon for Helper III rankHelper III
8 years ago

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's avatar
doyleg_OEH
Icon for Advocate II rankAdvocate II
8 years ago

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