Retrieve Excel file comment property in PowerBI

Hi ValeriaBreve ,

 

Rather than doing what is described in that article, I would encourage you to create a separate Excel sheet (or SharePoint list, etc.) to store your comments along with a key column that you can use to merge into your table that is refreshing. That way you comments are never lost, and you can update them as needed.

 

If you add comments to the SharePoint Online list, then please use the API to get it according to this document.

GET SharePoint list item comments using REST API 

 

The sample code in Power Query is as follows:

let
    Source = Xml.Tables(Web.Contents("https://<tenant>.sharepoint.com/sites/<site>/_api/web/lists/getbytitle('<list-name>')/items(<item-id>)/Comments()")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"updated", type datetime}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"entry"}),
    #"Expanded entry" = Table.ExpandTableColumn(#"Removed Other Columns", "entry", {"content"}, {"entry.content"}),
    #"Expanded entry.content" = Table.ExpandTableColumn(#"Expanded entry", "entry.content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}),
    #"Expanded http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" = Table.ExpandTableColumn(#"Expanded entry.content", "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", {"properties"}, {"properties"}),
    #"Expanded properties" = Table.ExpandTableColumn(#"Expanded http://schemas.microsoft.com/ado/2007/08/dataservices/metadata", "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"http://schemas.microsoft.com/ado/2007/08/dataservices"}),
    #"Expanded http://schemas.microsoft.com/ado/2007/08/dataservices" = Table.ExpandTableColumn(#"Expanded properties", "http://schemas.microsoft.com/ado/2007/08/dataservices", {"itemId", "text"}, {"itemId", "text"}),
    #"Expanded itemId" = Table.ExpandTableColumn(#"Expanded http://schemas.microsoft.com/ado/2007/08/dataservices", "itemId", {"Element:Text"}, {"Element:Text"})
in
    #"Expanded itemId"

If you create another table which saves your comments, please refer to this thread.

https://community.powerbi.com/t5/Power-Query/Excel-file-where-comments-are-linked/m-p/2090816#M61586 

 

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.