Retrieve Excel file comment property in PowerBI

[ValeriaBreve]

Hi,

I have a few Excel files on a Sharepoint. I have added some comments to their properties:

Is there a way I can retrieve this property when importing the file in PowerBI?

Thank you very much!

Kind regards

Valeria

[ValeriaBreve]

Hi! Thank you very much for your reply! I have thought of an Excel spreadsheet, I just wanted to avoid to have another file to maintain.

I don't think it's the same comments that the article is talking about - it is referring to comments on the Sharepoint list, I have put my comments in the file details (right click on the file --> Properties --> Details Tab). I can see them only with file explorer, if I add the "Comments" field for display.

Thanks

Kind regards

Valeria

[v-kkf-msft]

Hi @ValeriaBreve ,

I understand your idea, I have made an attempt, but I can't get the comments in the excel file properties. So I had to change my mind and add the comments to the SharePoint Online list or maintain a table for the comments.

Best Regards,
Winniz

[v-kkf-msft · Accepted solution]

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.