Forum Discussion
Connecting Jira to Power Bi - Missing Comments
Hi there, we manage to connect to Jira using Jira_PowerBI_ContentPack.pbit. It works great and we can see all the issues and information. However, we are not able to see the comments on issues. Where is this located and how i can change the power query below:
let
Source = FetchPages("", 100),
#"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"expand", "id", "self", "key", "fields","comment"}, {"expand", "id", "self", "key", "fields","comment"}),
#"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"
Appreciate your help. Thanks!
Hi again Anonymous
- I have created some queries to query Comments for all Issues.
- I did tweak the existing queries in the template a little and renamed the table GetIssues to Issues.
- I adjusted the Web.Contents calls so that the URL is just the base atlassian.net URL, so Power Query views this as a single data source requiring credentials for all API endpoints.
- I have tested that this refreshes successfully in the Power BI service (Basic credentials using email address and API key).
- I have attached both the PBIT and a PBIX with my data to illustrate how it turns out (just dummy data from a test Atlassian account).
- The tricky part of comments is that each coment is returned in a nested form. You can see an example of the JSON if you go to: https://<XXX>.atlassian.net/rest/api/3/issue/<IssueID>/comment/<CommentID>
For one of my Issues, these are the comments as seen on the Issue page:
which translates to this in a sample visual:
Hope this helps! 🙂
PS. The queries related to the comments are shown below:
FetchCommentsPage (based on FetchPage)
let FetchCommentsPage = (issueID as text, pageSize as number, skipRows as number) as table => let //Here is where you run the code that will return a single page contents = Web.Contents( URL, [ RelativePath = "rest/api/3/issue/" & issueID & "/comment", Query = [maxResults= Text.From(pageSize), startAt = Text.From(skipRows)] ]), json = Json.Document(contents), Value = json[comments], table = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in table meta [skipRows = skipRows + pageSize, total = 500] in FetchCommentsPageFetchCommentsPages (based on FetchPages)
let FetchCommentsPages = (issueID as text, pageSize as number) => let Source = GenerateByPage( (previous) => let skipRows = if previous = null then 0 else Value.Metadata(previous)[skipRows], totalItems = if previous = null then 0 else Value.Metadata(previous)[total], table = if previous = null or Table.RowCount(previous) = pageSize then FetchCommentsPage(issueID, pageSize, skipRows) else null in table, type table [Column1]) in Source in FetchCommentsPagesExtractCommentTextList (returns a list of the text items for a given comment)
Note that this function just extracts text without any formatting information.
// Function to extract "text" properties from nested "content" lists within "body" let GetTextFromBodyContent = (comment as record) as list => let // Check if "body" and "content" fields exist bodyContent = try comment[body][content] otherwise {}, // Recursive function to traverse "content" lists and find "text" fields TraverseContent = (contentList as list) as list => List.Combine( List.Transform( contentList, (item) => let // Collect "text" if present textList = if Record.HasFields(item, "text") // retrieve text property then {item[text]} // extract text property from attrs property if type = "emoji" else if Record.HasFields(item, "type") and item[type] = "emoji" then {item[attrs][text]} else {}, // If "content" field exists and is a list, recurse into it nestedContent = if Record.HasFields(item, "content") then @TraverseContent(item[content]) else {} in List.Combine({textList, nestedContent}) ) ) in TraverseContent(bodyContent) in GetTextFromBodyContentComments
Produces the final table containing one row for each comment/issue combination:
let Source = Issues, #"Removed Other Columns" = Table.SelectColumns(Source,{"id"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"id", "Issue ID"}}), #"Removed Duplicates" = Table.Distinct(#"Renamed Columns"), #"Invoked FetchCommentsPages" = Table.AddColumn(#"Removed Duplicates", "Comments", each FetchCommentsPages([Issue ID], 100)), #"Expanded Comments" = Table.ExpandTableColumn(#"Invoked FetchCommentsPages", "Comments", {"Column1"}, {"Comments"}), #"Remove Nulls" = Table.SelectRows(#"Expanded Comments", each [Comments] <> null), #"Added Custom" = Table.AddColumn(#"Remove Nulls", "Comment", each ExtractCommentTextList([Comments])), #"Expanded Comments1" = Table.ExpandRecordColumn(#"Added Custom", "Comments", {"id", "author", "created", "updated"}, {"Comment ID", "Comment Author", "Comment Created", "Comment Updated"}), #"Expanded Comment Author" = Table.ExpandRecordColumn(#"Expanded Comments1", "Comment Author", {"displayName"}, {"Comment Author Display Name"}), #"Combine Comment Text" = Table.TransformColumns(#"Expanded Comment Author",{{"Comment", Combiner.CombineTextByDelimiter("#(lf)"), type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Combine Comment Text",{{"Comment ID", type text}, {"Comment Author Display Name", type text}, {"Comment Created", type datetimezone}, {"Comment Updated", type datetimezone}}) in #"Changed Type"
8 Replies
- OwenAuger
Super User
Hi Anonymous
I have experimented a little with the JIRA REST API and a version of the Jira_PowerBI_ContentPack.pbit template.
The API endpoint used in the version of the template I have is:
This returns Issues and various attributes, but not comments (as far as I can tell).
For comments, it appears that you need to use this endpoint:
/rest/api/3/issue/{issueIdOrKey}/comment
Given that there can be multiple comments per Issue, I would suggest creating a separate function to query comments for a particular Issue, and load the Comments to a separate fact table, with a relationship to the Issues table.
Would you mind sharing the PBIT file you are currently using? (OneDrive or Google Drive shared link would be fine). I can then create a suggested query to load comments that fits with your existing queries.
You can export a new PBIT from your working PBIX if you have made changes to the original query setup that you want to share.
Regards
- OwenAuger
Hi again Anonymous
- I have created some queries to query Comments for all Issues.
- I did tweak the existing queries in the template a little and renamed the table GetIssues to Issues.
- I adjusted the Web.Contents calls so that the URL is just the base atlassian.net URL, so Power Query views this as a single data source requiring credentials for all API endpoints.
- I have tested that this refreshes successfully in the Power BI service (Basic credentials using email address and API key).
- I have attached both the PBIT and a PBIX with my data to illustrate how it turns out (just dummy data from a test Atlassian account).
- The tricky part of comments is that each coment is returned in a nested form. You can see an example of the JSON if you go to: https://<XXX>.atlassian.net/rest/api/3/issue/<IssueID>/comment/<CommentID>
For one of my Issues, these are the comments as seen on the Issue page:
which translates to this in a sample visual:
Hope this helps! 🙂
PS. The queries related to the comments are shown below:
FetchCommentsPage (based on FetchPage)
let FetchCommentsPage = (issueID as text, pageSize as number, skipRows as number) as table => let //Here is where you run the code that will return a single page contents = Web.Contents( URL, [ RelativePath = "rest/api/3/issue/" & issueID & "/comment", Query = [maxResults= Text.From(pageSize), startAt = Text.From(skipRows)] ]), json = Json.Document(contents), Value = json[comments], table = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in table meta [skipRows = skipRows + pageSize, total = 500] in FetchCommentsPageFetchCommentsPages (based on FetchPages)
let FetchCommentsPages = (issueID as text, pageSize as number) => let Source = GenerateByPage( (previous) => let skipRows = if previous = null then 0 else Value.Metadata(previous)[skipRows], totalItems = if previous = null then 0 else Value.Metadata(previous)[total], table = if previous = null or Table.RowCount(previous) = pageSize then FetchCommentsPage(issueID, pageSize, skipRows) else null in table, type table [Column1]) in Source in FetchCommentsPagesExtractCommentTextList (returns a list of the text items for a given comment)
Note that this function just extracts text without any formatting information.
// Function to extract "text" properties from nested "content" lists within "body" let GetTextFromBodyContent = (comment as record) as list => let // Check if "body" and "content" fields exist bodyContent = try comment[body][content] otherwise {}, // Recursive function to traverse "content" lists and find "text" fields TraverseContent = (contentList as list) as list => List.Combine( List.Transform( contentList, (item) => let // Collect "text" if present textList = if Record.HasFields(item, "text") // retrieve text property then {item[text]} // extract text property from attrs property if type = "emoji" else if Record.HasFields(item, "type") and item[type] = "emoji" then {item[attrs][text]} else {}, // If "content" field exists and is a list, recurse into it nestedContent = if Record.HasFields(item, "content") then @TraverseContent(item[content]) else {} in List.Combine({textList, nestedContent}) ) ) in TraverseContent(bodyContent) in GetTextFromBodyContentComments
Produces the final table containing one row for each comment/issue combination:
let Source = Issues, #"Removed Other Columns" = Table.SelectColumns(Source,{"id"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"id", "Issue ID"}}), #"Removed Duplicates" = Table.Distinct(#"Renamed Columns"), #"Invoked FetchCommentsPages" = Table.AddColumn(#"Removed Duplicates", "Comments", each FetchCommentsPages([Issue ID], 100)), #"Expanded Comments" = Table.ExpandTableColumn(#"Invoked FetchCommentsPages", "Comments", {"Column1"}, {"Comments"}), #"Remove Nulls" = Table.SelectRows(#"Expanded Comments", each [Comments] <> null), #"Added Custom" = Table.AddColumn(#"Remove Nulls", "Comment", each ExtractCommentTextList([Comments])), #"Expanded Comments1" = Table.ExpandRecordColumn(#"Added Custom", "Comments", {"id", "author", "created", "updated"}, {"Comment ID", "Comment Author", "Comment Created", "Comment Updated"}), #"Expanded Comment Author" = Table.ExpandRecordColumn(#"Expanded Comments1", "Comment Author", {"displayName"}, {"Comment Author Display Name"}), #"Combine Comment Text" = Table.TransformColumns(#"Expanded Comment Author",{{"Comment", Combiner.CombineTextByDelimiter("#(lf)"), type text}}), #"Changed Type" = Table.TransformColumnTypes(#"Combine Comment Text",{{"Comment ID", type text}, {"Comment Author Display Name", type text}, {"Comment Created", type datetimezone}, {"Comment Updated", type datetimezone}}) in #"Changed Type"
v-kpoloju-msftCommunity Support
Hi Anonymous,
Thanks for reaching out to the Microsoft Fabric Community Forum.
Thank you, OwenAuger for your valuable input regarding the issue. I agree with the solution posted by the super user.After thoroughly reviewing the details you provided, here are a few alternative workarounds that may help resolve the issue. Please follow these steps:
- Create a new Power Query function that takes an issueIdOrKey as an argument and fetches comments using: “/rest/api/3/issue/{issueIdOrKey}/comment”. Apply this function to the issue table to fetch comments for each issue and store them in a separate table.
- After fetching comments in a separate query, use Power BI’s Merge Queries to link comments to the issue table. This will keep the data model clean and structured.
- Alternatively, modify the existing API call to include comments in the response by adding comment to the fields parameter. However, note that this workaround is limited by JIRA’s API, which paginates comments separately.
Please go through the below following links for more information:
Jira - Connectors | Microsoft Learn
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.Best Regards.
hi OwenAuger, you are the master! thank you so much and i am able to see all the comments. the comments section are all in text as you mentioned. is it possible to preserve some of the tables in the comments? Example below. I will then try to clean and compile those little tables found in the comments. Appreciate your help.
SERVICE INCIDENT REPORT
Channel/s
SEA Today
Date / Time of Interruption
16/Feb/25 2000 UTC (17/Feb/25 0400 SGT)
Date / Time of Restoration
17/Feb/25 0209 UTC (17/Feb/25 1009 SGT)
Impacted Location
Headend
Effect of Incident
Loss of signal on backup feed
Channel/s
HITS NOW
Date / Time of Interruption
28/Aug/23 1413 UTC (28/Aug/23 2213 SGT)
Date / Time of Restoration
28/Aug/23 1608 UTC (29/Aug/23 0008 SGT)
Impacted Location
No stream on backup path
CP Response/Resolution
Re-routed using another recaster.
- OwenAuger
Hi Anonymous
Glad to have been of some help so far 🙂
Regarding tables, that's a little more complicated but I will take a look at it.
The function I set up originally ends up concatenating all table cells left-to-right, top-to-bottom with line breaks.
How does it sound if we set things up so that you get a nested table in Power Query which you can then process as you see fit?
I'll try that in the meantime and get back to you 🙂