Forum Discussion
Strange Sharepoint List integration question
After pulling my query into Power Query in Excel to troubleshoot, I found out the following:
The RoleAssignments issue that it was running into is actually a column automatically generated by Sharepoint. In order to access that column, and see what roles have access to it, you need admin rights - which I didn't have. So it wasn't an issue with not having access to the data in the list, or the list itself, the issue was not having permission to see who else had permission for that column.
By simply removing that column "RoleAssignments" from the query, it was no longer trying to access it, and the query worked fine. I also removed a bunch of the other columns I didn't need to query, including that mysterious duplicate "Id" and "ID" column.
I'm going to mark as solved for anyone else who might have the same issue.
I was looking at my Sharepoint permissions level, and found out that even giving me Edit access to the list (add/delete columns etc..) doesn't grant me access. The only thing I don't have checked for this specific list is "override list behavior".
When PowerBI pulls in the data using the query, it pulls in the whole data model, including the field for "Status", which is connected to another list of "Not Started, Inprogress, and Complete", and several other lookup fields. When I try to un-pivot these in Power BI, it again says "User not authorized".
If I can view the data in PowerBi's query builder, why can't I specify which fields to pull in for analysis? Seems like PowerBI is requesting Sharepoint an access role of modifying the table, instead of just ignoring columns I don't want pulled in. Any ideas? This is turning what should have been a simple dashboard into a time consuming chore.