Privacy levels error (I've tried several solutions of this forum, and nothing worked)

[TaylonLima]

Hey guys.

Well, basically I'm trying to make a query, let's call it "Comment" that would search for all comments from a specific list in sharepoint. After a lot of research, I found a solution here on forum. Which would be to use the query below:

let
Source = (Sharepoint_Site as text, Sharepoint_List_Name as text, Item_ID as number) => let
Source = Xml.Tables(Web.Contents(
"sharepoint_Site",
[RelativePath = Sharepoint_Site & "/_api/web/Lists/getbytitle('" & Sharepoint_List_Name & "')/items(" & Text.From(Item_ID) & ")/Comments"]
)),
entry = Source{0}[entry],
#"Removed Other Columns2" = Table.SelectColumns(entry,{"content"}),
#"Expanded content" = Table.ExpandTableColumn(#"Removed Other Columns2", "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"content"}),
#"Expanded content1" = Table.ExpandTableColumn(#"Expanded content", "content", {"properties"}, {"properties"}),
#"Expanded properties" = Table.ExpandTableColumn(#"Expanded content1", "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"properties"})
in
#"Expanded properties"
in
Source

It worked perfectly, however, as you noticed, this way I could only bring 1 ID at a time, so I created a new query with all IDs, and invoked the function to search for everything, let's call this query "All Comments":

let
Source = {1,2,3},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "ID"}}),
#"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "Comment", each Comment("SiteName", "ListName", [ID])),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Invoked Custom Function", {"Comment"}),
#"Expanded Comment" = Table.ExpandTableColumn(#"Removed Errors", "Comment", {"properties"}, {"Comment.properties"}),
#"Expanded Comment.properties" = Table.ExpandTableColumn(#"Expanded Comment", "Comment.properties", {"author", "createdDate", "id", "isLikedByUser", "isReply", "itemId", "likeCount", "listId", "mentions", "modifiedDate", "parentId", "replyCount", "text"}, {"Comment.properties.author", "Comment.properties.createdDate", "Comment.properties.id", "Comment.properties.isLikedByUser", "Comment.properties.isReply", "Comment.properties.itemId", "Comment.properties.likeCount", "Comment.properties.listId", "Comment.properties.mentions", "Comment.properties.modifiedDate", "Comment.properties.parentId", "Comment.properties.replyCount", "Comment.properties.text"})
in
#"Expanded Comment.properties"

So, in power BI Desktop everything worked well. No problems, everything works fine.

 However, when I publish to Power BI service I see that old error message:

[Unable to combine data] Section1/[Query]/[Last step of the query] references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Important notes:

1. When I do it only with 1 ID and publish to power BI Service, it works perfectly. The problems happens when I get more than 1 ID.
2. I could create connection for each ID, but it wont works because I have more than 1k ID...

So would be amazing if somebody could help me with a solution.

[aj1973]

Hi @TaylonLima 

I am not conviced that this could be a Sharepoint List connector

Sharepoint List or Sharepoint Online List should look like this

Also the Item Id of a Sharepoint list is not of type number 

When you are talking about more than 1k ID ! what are they? primary key of your List?

[TaylonLima]

Hello @aj1973 , thanks for your feedback... Actually in PowerBI desktop it works... Everything is working fine. Data is loading perfectly.

Yes, its not a "normal" connection... Because I am not connecting a List data, unfortunatelly I think its not possible to connect it by sharepoint list connector because there we do not have a data for comments or history version. So I have to connect this API... 

If you know a different way to connect sharepoint list comments and history version, would be amazing if you could share.

Thanks, mate!

[aj1973]

Hi @TaylonLima 

Please check this out

Solved: How to retrieve comments from a SharePoint list in... - Microsoft Fabric Community

Can you retreive comments from a SharePoint list in PowerBi? : r/PowerBI (reddit.com)