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

 

 

let
    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"}, {"components.name"}),
    #"Expanded fixVersions" = Table.ExpandListColumn(#"Expanded components1", "fixVersions"),
    #"Expanded fixVersions1" = Table.ExpandRecordColumn(#"Expanded fixVersions", "fixVersions", {"name"}, {"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.name", "issuetype.subtask"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded issuetype",{{"issuetype.name", "issuetype"}, {"issuetype.subtask", "isSubtask"}}),
    #"Expanded project" = Table.ExpandRecordColumn(#"Renamed Columns", "project", {"id", "key", "name"}, {"project.id", "project.key", "project.name"}),
    #"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.name", "assignee.emailAddress", "assignee.displayName", "assignee.active"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded assignee",{{"assignee.displayName", "assignee"}, {"resolutiondate", "resolution date"}}),
    #"Expanded status" = Table.ExpandRecordColumn(#"Renamed Columns2", "status", {"name", "statusCategory"}, {"status.name", "status.statusCategory"}),
    #"Expanded status.statusCategory" = Table.ExpandRecordColumn(#"Expanded status", "status.statusCategory", {"colorName", "name"}, {"status.statusCategory.colorName", "status.statusCategory.name"}),
    #"Removed Columns4" = Table.RemoveColumns(#"Expanded status.statusCategory",{"subtasks"}),
    #"Expanded reporter" = Table.ExpandRecordColumn(#"Removed Columns4", "reporter", {"name", "emailAddress", "displayName", "active"}, {"reporter.name", "reporter.emailAddress", "reporter.displayName", "reporter.active"}),
    #"Expanded aggregateprogress" = Table.ExpandRecordColumn(#"Expanded reporter", "aggregateprogress", {"progress", "total"}, {"aggregateprogress.progress", "aggregateprogress.total"}),
    #"Expanded progress" = Table.ExpandRecordColumn(#"Expanded aggregateprogress", "progress", {"progress", "total"}, {"progress.progress", "progress.total"}),
    #"Expanded votes" = Table.ExpandRecordColumn(#"Expanded progress", "votes", {"votes"}, {"votes.votes"}),
    #"Expanded parent" = Table.ExpandRecordColumn(#"Expanded votes", "parent", {"id"}, {"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"}})
in
    #"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.

 

let
    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"}})
in
    #"Renamed Columns3"

 

Moderator
Posts: 3,106
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"}),



Thanks,
Lydia Zhang

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

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

@Lydia

 

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?

 

Andrea

 

 

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

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

@Lydia

 

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.

 

Thanks,

Haree

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.

Frequent Visitor
Posts: 3
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.

 

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

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

@KarthikKumar

 

Story points are available under the guise of "Customfield_10117"

Frequent Visitor
Posts: 9
Registered: ‎04-09-2017

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

Hi all,

 

I use JIRA content pack for Power BI as well and some custom fields are not uploaded automatically. So I guess I need to add them myself in Advanced Editor.

 

BUT how do I know which customfield NUMBER I should add?  For example, if I want to add custom field EPIC LINK, how can I identify which number it is (e.g. customfield_10900  OR customfield_10911 OR customfield_103423  OR something else) ?

 

Can you please help me to find the place where these custom_fields NUMBERS are visible?

 

Frequent Visitor
Posts: 3
Registered: ‎01-30-2017

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

You will find that from the custom fields settings in Jira Service Desk.