Strange Sharepoint List integration question

[ALeef]

I have an Office 365 Sharepoint list that I am trying to connect to using Power BI Desktop.  I can connect to the source, run the query - and it comes up with the "duplicate column" error for "ID".  So when I go to choose columns in order to unselect one of them, and I hit connect - it brings up "User not Authorized".  I'm connecting to the site specific URL, using my O365 organizational credentials.

I have "contribute" access to the Sharepoint subsite that the list is hosted on.

When I try to connect to a list on the test site, where I have full control access, it allows me to choose columns, and connects just fine.  What setting is causing this issue?  I wouldn't think that I would need edit list,add or remove columns from a list permissions on sharepoint -because Power BI is just writing a query against the data - and not pulling that column, it isnt actually deleting the column from the list.

Or perhaps you could help solve the base issue of why it is pulling in and "Id" and "ID" column that I haven't created.  They are exactly the same, identical for each row, just with a capitalization difference in the column name.  It's a sequential numbered, auto generated column that I didn't add.  I'm only selecting one list from the data source, so I have no idea why it is attempting to pull in a duplicate column.

[ALeef · Accepted solution]

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.

[ALeef]

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.