Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Fields I need added:
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"
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?
You will find that from the custom fields settings in Jira Service Desk.
Can you please send exact guide or screenshots on how to access to custom fields settings? I have full administrator access but have no idea where custom fields settings are 😞
If it is a custom field you have created then when you go to Jira Admin -> Issues -> Custom Fields and then "view" the custom field the ID is on the end of the URL.
Just google for that "jira service desk custom field settings"
Hello Everyone!
Does anyone know what field in JIRA gives me the date an employee has charged hours on a task? (And the field that tells me how much time has imputed in that task).
Thanks!
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
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
I am looking for story points as one of the column in the content pack. How do I get that from our JIRA instance?
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
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.
@hareeharan I've not identified a resolution but will eagarly post if/when one is identified.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.