The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to Solution.
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
Hi.
Thanks for sharing this query. It does work. Do you have an updated query to keep Names/emails instead of "@1234" references?
thanks.
Thank you !
Has anyone figured out how to clean up mentions in the comments?