Forum Discussion
SharePoint list column not accessible via Power Query call to rest api
- 1 year ago
Check out this doc: https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests
As it mentions, some complex / resource-intensive fields (this can often include lookups) will not be included by default. If RiskReference is single-select and DecisionsReference is multi-select, that could be a factor in the discrepancy; also, probably the number of columns in the lookup lists would be a factor.
To ensure you get the lookup column metadata you need, you'll have to request them via $select in your query.
- This is done via Query=[#"$select"="..."] in the Web.Content's options parameter (ie Query is a field in the record just like Headers).
- You must use the internal name of the field. Get this by checking URL of field settings in SharePoint or you can get all field metadata including internal names with _api/web/getbytitle('<list name>')/fields. If all else fails, you can query a list item with OData.Feed - it's got horrible performance but usually pulls in everything, which you can then inspect to double-check shape of data coming in and internal names.
- If the lookup is single-select, you can usually get just the ID by selecting FieldNameID. If multi-select, you can get the ID with $select=Lookup/ID&$expand=Lookup
So, assuming RiskReference is single-select and DecisionsReference is multi-select, and those are the internal names for both, something like the following may work better for you.
Web.Contents( "https://entity.sharepoint.com/sites/" & [ProjectName] & "/_api/web/lists/getbytitle('" & [ListName] & "')/items('" & [ListItemId] & "')", [ Headers = [accept = "application/json"], Query = [ #"$select" = "Id,Title,RiskReferenceID,DecisionsReference/ID", #"$expand" = "DecisionsReference" ] ] )Also, as an aside, it is pretty inefficient to make a web request per item. You would probably get better performance (and better avoid throttling) if you make a single call on the list to get all items and then either join them in Power Query or load in separately and handle with a relationship in your model (the latter is usually the better option IMO). You can get up to 5k items with a single call (will need to specify #"$top"="5000" in Query), after which you'll need to implement paging (there are a few ways to do this in PQ for SharePoint).
Check out this doc: https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint-rest-requests
As it mentions, some complex / resource-intensive fields (this can often include lookups) will not be included by default. If RiskReference is single-select and DecisionsReference is multi-select, that could be a factor in the discrepancy; also, probably the number of columns in the lookup lists would be a factor.
To ensure you get the lookup column metadata you need, you'll have to request them via $select in your query.
- This is done via Query=[#"$select"="..."] in the Web.Content's options parameter (ie Query is a field in the record just like Headers).
- You must use the internal name of the field. Get this by checking URL of field settings in SharePoint or you can get all field metadata including internal names with _api/web/getbytitle('<list name>')/fields. If all else fails, you can query a list item with OData.Feed - it's got horrible performance but usually pulls in everything, which you can then inspect to double-check shape of data coming in and internal names.
- If the lookup is single-select, you can usually get just the ID by selecting FieldNameID. If multi-select, you can get the ID with $select=Lookup/ID&$expand=Lookup
So, assuming RiskReference is single-select and DecisionsReference is multi-select, and those are the internal names for both, something like the following may work better for you.
Web.Contents(
"https://entity.sharepoint.com/sites/" & [ProjectName]
& "/_api/web/lists/getbytitle('" & [ListName] & "')/items('" & [ListItemId] & "')",
[
Headers = [accept = "application/json"],
Query = [
#"$select" = "Id,Title,RiskReferenceID,DecisionsReference/ID",
#"$expand" = "DecisionsReference"
]
]
)
Also, as an aside, it is pretty inefficient to make a web request per item. You would probably get better performance (and better avoid throttling) if you make a single call on the list to get all items and then either join them in Power Query or load in separately and handle with a relationship in your model (the latter is usually the better option IMO). You can get up to 5k items with a single call (will need to specify #"$top"="5000" in Query), after which you'll need to implement paging (there are a few ways to do this in PQ for SharePoint).