I am attempting to use the below query to connect to an XML Forms Library on SharePoint Online.
https://tenant.sharepoint.com/teams/sitecol/site/_api/Web/lists/getbytitle('List Name')/items?$select=Created,Reviewer_x0020_Names/Title,Reviewer_x0020_Names/EMail&$expand=Reviewer_x0020_Names/Id
The query works fine in the browser and I can see all required data, however when using this in Power BI, the 'Created' column appears as expected, but instead of the Title & Email fields from the person picker field entitled "Reviewer Names" being displayed, it just displays the text "Table" (for every row). When clicking on the word "Table I get the error message:
DataSource.Error: OData: Request failed: The remote server returned an error: (500) Internal Server Error. (Specified method is not supported.)
Details:
DataSourceKind=OData
DataSourcePath=https://tenant.sharepoint.com/teams/sitecol/site/_api/Web/Lists(guid'dce824dd-44e6-44c6-bcb4-eee2764...
SPRequestGuid=9c60969e-8041-7000-c786-25bc969086de
Url=https://tenant.sharepoint.com/teams/sitecol/site/_api/Web/Lists(guid'dce824dd-44e6-44c6-bcb4-eee2764...
What I was expecting was to have 3 columns visible:
- The Created field
- The Title of the 'Reviewer Names' Person Picker field
- The Email address of the 'Reviewer Names' Person Picker field
How can I change my OData query to bring back this information?
2nd question, I was hoping to retrieve the login name from the person picker field also but can't seem to get it, I've tried adding:
Reviewer_x0020_Names/UserName
Reviewer_x0020_Names/Account
These both fail (in the browser) - web page not found.
Using "Reviewer_x0020_Names/Name" works but brings the whole i:0#.f|membership|USERNAME@domain.ac.uk back - I just want the username aspect.
