Connecting Jira to Power Bi - Missing Comments

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
    FetchCommentsPage

FetchCommentsPages (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
    FetchCommentsPages

ExtractCommentTextList (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
    GetTextFromBodyContent

Comments

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"