Forum Discussion

PetterR's avatar
PetterR
Helper I
1 year ago
Solved

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

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 ...
  • MarkLaf's avatar
    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. 

    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).