Frequent Visitor
Posts: 3
Registered: ‎10-21-2016

Adding custom Jira fields to JIRA content pack for Power BI

[ Edited ]



Request: I need guidance on how to modify the query for JIRA content pack for Power BI using Advanced Editor.  My goal is to add in some custom fields.


Why: We use Epics to join together other Jira Issue types. However, the template treats all issues as the same.


Description of the Dashboard I want to build: 

  • Top: A slicer object where open epics are listed (Issue Type = Epic, Status = open)
  • When an open epic is selected, show below
    • Card with count of total number of issues in the Epic (Issue Type = bug, story, chore)
    • A donut chart showing the status of the issues within that epic
    • A gannt chart showing expected Dev and QA completion timeline


Fields I need added:

  1. Epic Link customfield_10900
  2. Epic Name customfield_10901
  3. Dev Start customfield_12404
  4. Dev End customfield_12400
  5. QA start customfield_12401
  6. QA end customfield_12402
  7. Percent Dev Complete customfield_12405
  8. Percent QA Complete customfield_12406

Here is the query for the Jira Conent Pack to which I need to add custom fields:



    Source = FetchPages("", 500),
    #"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"expand", "id", "self", "key", "fields"}, {"expand", "id", "self", "key", "fields"}),
    #"Expanded fields" = 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"}, {"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"}),
    #"Expanded components" = Table.ExpandListColumn(#"Expanded fields", "components"),
    #"Expanded components1" = Table.ExpandRecordColumn(#"Expanded components", "components", {"name"}, {""}),
    #"Expanded fixVersions" = Table.ExpandListColumn(#"Expanded components1", "fixVersions"),
    #"Expanded fixVersions1" = Table.ExpandRecordColumn(#"Expanded fixVersions", "fixVersions", {"name"}, {""}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded fixVersions1",{"expand"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each [id] <> null and [id] <> ""),
    #"Expanded issuetype" = Table.ExpandRecordColumn(#"Filtered Rows", "issuetype", {"name", "subtask"}, {"", "issuetype.subtask"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded issuetype",{{"", "issuetype"}, {"issuetype.subtask", "isSubtask"}}),
    #"Expanded project" = Table.ExpandRecordColumn(#"Renamed Columns", "project", {"id", "key", "name"}, {"", "project.key", ""}),
    #"Expanded watches" = Table.ExpandRecordColumn(#"Expanded project", "watches", {"isWatching"}, {"isWatching"}),
    #"Expanded priority" = Table.ExpandRecordColumn(#"Expanded watches", "priority", {"name"}, {"name"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded priority",{{"name", "priority"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"customfield_10102"}),
    #"Expanded labels" = Table.ExpandListColumn(#"Removed Columns2", "labels"),
    #"Removed Columns3" = Table.RemoveColumns(#"Expanded labels",{"labels", "versions", "issuelinks"}),
    #"Expanded assignee" = Table.ExpandRecordColumn(#"Removed Columns3", "assignee", {"name", "emailAddress", "displayName", "active"}, {"", "assignee.emailAddress", "assignee.displayName", ""}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded assignee",{{"assignee.displayName", "assignee"}, {"resolutiondate", "resolution date"}}),
    #"Expanded status" = Table.ExpandRecordColumn(#"Renamed Columns2", "status", {"name", "statusCategory"}, {"", "status.statusCategory"}),
    #"Expanded status.statusCategory" = Table.ExpandRecordColumn(#"Expanded status", "status.statusCategory", {"colorName", "name"}, {"status.statusCategory.colorName", ""}),
    #"Removed Columns4" = Table.RemoveColumns(#"Expanded status.statusCategory",{"subtasks"}),
    #"Expanded reporter" = Table.ExpandRecordColumn(#"Removed Columns4", "reporter", {"name", "emailAddress", "displayName", "active"}, {"", "reporter.emailAddress", "reporter.displayName", ""}),
    #"Expanded aggregateprogress" = Table.ExpandRecordColumn(#"Expanded reporter", "aggregateprogress", {"progress", "total"}, {"aggregateprogress.progress", ""}),
    #"Expanded progress" = Table.ExpandRecordColumn(#"Expanded aggregateprogress", "progress", {"progress", "total"}, {"progress.progress", ""}),
    #"Expanded votes" = Table.ExpandRecordColumn(#"Expanded progress", "votes", {"votes"}, {"votes.votes"}),
    #"Expanded parent" = Table.ExpandRecordColumn(#"Expanded votes", "parent", {"id"}, {""}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded parent",{{"resolution date", type datetimezone}, {"created", type datetimezone}, {"updated", type datetimezone}}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([resolution date]), type date),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Date",{{"Date", "Resolution Day"}}),
    #"Inserted End of Week" = Table.AddColumn(#"Renamed Columns3", "EndOfWeek", each Date.EndOfWeek([Resolution Day]), type date),
    #"Renamed Columns4" = Table.RenameColumns(#"Inserted End of Week",{{"EndOfWeek", "Resolution Week"}}),
    #"Inserted Date1" = Table.AddColumn(#"Renamed Columns4", "Date", each DateTime.Date([created]), type date),
    #"Renamed Columns5" = Table.RenameColumns(#"Inserted Date1",{{"Date", "Created Day"}}),
    #"Inserted End of Week1" = Table.AddColumn(#"Renamed Columns5", "EndOfWeek", each Date.EndOfWeek([Created Day]), type date),
    #"Renamed Columns6" = Table.RenameColumns(#"Inserted End of Week1",{{"EndOfWeek", "Created Week"}})
    #"Renamed Columns6"




Additional context

Previously, I've been using the API to set up a unique query for each epic. This is time intensive and not ideal. The output was a gannt style chart that showed when we expect to start and end dev work and QA. A sample query is below.


    Source = Json.Document(Web.Contents("SOURCE REDACTED")),
    fields = Source[fields],
    #"Converted to Table" = Record.ToTable(fields),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table"),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"customfield_12404", "customfield_10901", "customfield_12400", "customfield_12402", "customfield_12401", "description", "customfield_12405", "customfield_12406"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"customfield_12404", "Dev Start"}, {"customfield_10901", "Epic Name"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Epic Name", "Dev Start", "customfield_12400", "customfield_12402", "customfield_12401"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"customfield_12400", "Dev End"}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Epic Name", "Dev Start", "Dev End", "customfield_12405", "customfield_12401", "customfield_12402", "customfield_12406", "description"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Reordered Columns1",{{"customfield_12401", "QA Start"}, {"customfield_12402", "QA End"}, {"customfield_12405", "Percent Dev Complete"}, {"customfield_12406", "Percent QA Complete"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Dev Start", type text}, {"Dev End", type text}, {"Percent Dev Complete", type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"Dev Start", "Dev End", "Percent Dev Complete"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Dev"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Merged Columns",{{"QA Start", type text}, {"QA End", type text}, {"Percent QA Complete", type text}}),
    #"Merged Columns1" = Table.CombineColumns(#"Changed Type3",{"QA Start", "QA End", "Percent QA Complete"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"QA"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Epic Name", "description"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns","Value",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Value.1", "Value.2", "Value.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type date}, {"Value.2", type date}, {"Value.3", type number}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Changed Type",{{"Value.1", "Start"}, {"Value.2", "End"}, {"Attribute", "Resource"}, {"Value.3", "Percent Complete"}})
    #"Renamed Columns3"


Posts: 2,202
Registered: ‎03-10-2016

Re: Adding custom Jira fields to JIRA content pack for Power BI

[ Edited ]

Hi @aganier,

I am not familiar with JIRA. It seems that you use this Power BI Desktop template file that resembles the content pack, right? If so, have you tried to add custom fields in the #"Expanded fields" part of your query in Advanced Editor?

#"Expanded fields" = 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"}, {"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_12404", "customfield_10901", "customfield_12400", "customfield_12402", "customfield_12401", "description", "customfield_12405", "customfield_12406"}),

Lydia Zhang

Frequent Visitor
Posts: 3
Registered: ‎10-21-2016

Re: Adding custom Jira fields to JIRA content pack for Power BI



Thank you for your reply.  You are correct I am using the template file you linked. 


When I replace (line 4) with the sample you provided I get the following warning: Expression.Error: The import Expanded fields matches no exports. Did you miss a module reference?





New Member
Posts: 1
Registered: ‎10-28-2016

Re: Adding custom Jira fields to JIRA content pack for Power BI



I am looking for story points as one of the column in the content pack. How do I get that from our JIRA instance?

Frequent Visitor
Posts: 4
Registered: ‎12-30-2016

Re: Adding custom Jira fields to JIRA content pack for Power BI

Hi Karthik,


Did you manage to resolve the issue and find any solution as I am having a similar issue where my custom field column values are not populating for the Power BI template? Any help would be highly appreciated.




Frequent Visitor
Posts: 3
Registered: ‎10-21-2016

Re: Adding custom Jira fields to JIRA content pack for Power BI

@hareeharan  I've not identified a resolution but will eagarly post if/when one is identified.

Posts: 1
Registered: ‎01-30-2017

Re: Adding custom Jira fields to JIRA content pack for Power BI

I think I have te same issue. When you say you cant see the values of our custom fields do you see anything at all in those fields?


I am seeing values of "[Record]" in there.


Frequent Visitor
Posts: 3
Registered: ‎03-20-2017

Re: Adding custom Jira fields to JIRA content pack for Power BI



Story points are available under the guise of "Customfield_10117"