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).
Hey PetterR,
Issue Analysis & Resolution Steps
Based on your description, this appears to be a common SharePoint REST API behavior with multi-select lookup columns. Here's my recommended approach:
1. Verify Column Configuration
- Navigate to List Settings → Column Settings for both RiskReference and DecisionReference
- Compare the lookup column configurations, particularly the "Allow multiple values" setting
- Check if both columns are pointing to active, accessible lists
2. REST API Call Modifications
- Try using the internal field name instead of display name in your query
- Add explicit expansion for the DecisionReference field: $expand=DecisionReference&$select=*,DecisionReference/Id,DecisionReference/Title
- Test with $select=DecisionReferenceId to get just the ID values
3. Permission & Access Verification
- Confirm your account has read access to the target Decisions list
- Check if the Decisions list has any unique permissions or broken inheritance
- Verify the target list items haven't been deleted or moved
4. Power Query Debugging Steps
- Add a step to examine the raw JSON response from the API call
- Use Table.AddColumn with try-catch logic for DecisionReference parsing
- Compare the field structure between working RiskReference and non-working DecisionReference
5. Alternative Approaches
- Consider using /_api/web/lists/getbytitle('Monthly report')/items instead of GetByTitle method
- Try OData v4 syntax if you're currently using v3
- As fallback, query the Decisions list separately and join in Power Query
Quick Test: Run the REST API call directly in browser first to isolate whether it's a SharePoint or Power Query issue.
Fixed? ✓ Mark it • Share it • Help others!
Best Regards,
Jainesh Poojara | Power BI Developer