How to retrieve comments from a SharePoint list into Power BI ?

[nic0dav]

Hello,

I have linked y power BI report to a sharepoint list, I can see most information in the Power BI table but I cannot find the comments, is there a way to retrieve them?

Thank you

[Thp889]

Try This It should run through your list and give you all the comments, may be able to filter them out?

let
    siteUrl = "https://yoursiteurl",
    listName = "your list name",

    // Step 1: Get all item IDs
    listApiUrl = "_api/web/lists/getbytitle('" & listName & "')/items?$select=Id",
    itemResponse = Json.Document(Web.Contents(siteUrl, [
        RelativePath = listApiUrl,
        Headers=[Accept="application/json;odata=verbose"]
    ])),
    itemData = itemResponse[d][results],
    itemTable = Table.FromRecords(itemData),
    itemIds = Table.Column(itemTable, "Id"),

    // Step 2: Define comment fetcher with RelativePath
    GetComments = (itemId as number) as table =>
        let
            commentsPath = "_api/web/lists/getbytitle('" & listName & "')/items(" & Text.From(itemId) & ")/Comments()",
            response = Json.Document(Web.Contents(
                siteUrl,
                [
                    RelativePath = commentsPath,
                    Headers = [Accept="application/json;odata=verbose"]
                ]
            )),
            commentData = response[d][results],
            commentTable = Table.FromRecords(commentData),
            withItemId = Table.AddColumn(commentTable, "ItemId", each itemId)
        in
            withItemId,

    // Step 3: Loop through item IDs
    allComments = List.Transform(itemIds, each try GetComments(_) otherwise null),
    filteredComments = List.RemoveNulls(allComments),
    combinedComments = Table.Combine(filteredComments)
in
    combinedComments

[nic0dav]

Thank you !

[amitchandak · Accepted solution]

@nic0dav , Try like

https://www.reddit.com/r/PowerBI/comments/tsxo8l/can_you_retreive_comments_from_a_sharepoint_list/

https://ganeshsanapblogs.wordpress.com/2021/01/09/all-you-need-to-know-about-commenting-in-lists/

[Tbarnet3]

Has anyone figured out how to clean up mentions in the comments?