SharePoint list column not accessible via Power Query call to rest api

[PetterR]

Dear all, 

for our projects we have created standard SharePoint site templates with standard lists to document project stuff (action items, risks, decisions, monthly report, etc.), each of these lists uses a content type. In the list "Monthly report" we're referencing items from other lists of the same project; these are columns of type lookup in the same site: RiskReference, DecisionsReference with multi select enabled.

On top of this SharePoint structure we have a Power BI report that aggregates the information per project across the related logs.

Getting the data out of SharePoint is done by using SharePoint Search API queries in Power Query. However, for getting the cross-references in the list monthly report to other lists (risks, decisions) we can't use SharePoint Search API as these list columns couldn't be implemented in the content type, as they are site specific. So we're getting that part of the monthly report list via SharePoint rest api call on the method "GetByTitle" which works like a charm, but only gives results for the column RiskReference and not for the column DecisionReference. The expected result of the API call would be the ListId of the linked items, which works for RiskReference, but for DecisionReference is just empty.

Here is the power query call to the rest api:

Do you have an idea what could be the cause for this inconsistency? The columns are configured identically in SharePoint.

[MarkLaf · Accepted solution]

Check out this doc: https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/use-odata-query-operations-in-sharepoint...

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. 

1. This is done via Query=[#"$select"="..."] in the Web.Content's options parameter (ie Query is a field in the record just like Headers).
2. 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.
3. 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).

[jaineshp]

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