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
