Forum Discussion

Watever's avatar
Watever
Frequent Visitor
1 year ago
Solved

Power Query OData Top and Filter not working ?

I have a sharepoint list that eventually will grow large.

But for my query (in an excel file), I only need to work with the last 30 days or last 200 items for a specific column value.

 

Unfortunately, any filter or order or top doesn't work. I don't understand why.

If I do a web query on the same parameters, it work.

 

let

Source =
OData.Feed(
"https://tenant.sharepoint.com/sites/SharepointName/_api/web/Lists/GetByTitle('List_Name')/Items",
null,
[
Implementation = "2.0",
Query = [
filter = "Receiving_Site eq 'ABCD'",
orderby = "Id desc",
top = "2"
]
]
),

 

But this works :
https://tenant.sharepoint.com/sites/SharepointName/_api/web/Lists/GetByTitle('List_Name')/Items?$filter=Receiving_Site %20eq%20%27ABCD%27&$orderby=Id%20desc&$top=2

 

 

What am I doing wrong or missing ?

Need Help
  • v-sgandrathi's avatar
    v-sgandrathi
    1 year ago

    Hi Watever,

    Thank you for reaching out.

     

    We understand you're trying to get a filtered and limited set of items from your SharePoint list using Power Query, but the $filter, $orderby, and $top options aren't working as expected with OData.Feed. This is a known issue, as Power Query doesn't always apply these options properly due to the way it manages metadata and query folding.

    Since your direct web URL works, we suggest using the Web.Contents() function instead. This approach gives you full control over the request and ensures the query parameters are used as intended.

     

    Here’s how you can set up your Power Query with Web.Contents:

     

    let
        url = "https://tenant.sharepoint.com/sites/SharepointName/_api/web/Lists/GetByTitle('List_Name')/Items?$filter=Receiving_Site eq 'ABCD'&$orderby=Id desc&$top=2",
        Source = Json.Document(Web.Contents(url)),
        Items = Source[value]
    in
        Items

    This method should provide the filtered and sorted results you expect. If your dataset is large, make sure your SharePoint list is properly indexed, and check your authentication settings in Excel if you encounter any access issues.

    Thank you.

5 Replies