Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Try using $select=*
to return all available fields.
Unfortunately the list has too many lookup fields to use select=* so I need to manually pick out the fields I need.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |