Power Query OData Top and Filter not working ?

Watever · ‎07-24-2025

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?$fil... %20eq%20%27ABCD%27&$orderby=Id%20desc&$top=2

What am I doing wrong or missing ?

sevenhills · ‎07-25-2025

Web.Contents is one approach like @v-sgandrathi said!

Supporting articles: not sure whether you got a chance to look into these links:

https://blog.crossjoin.co.uk/2023/01/15/using-odata-feed-and-the-query-option-to-avoid-the-dynamic-d...

https://sites.google.com/view/powerquerywizard/pagination-and-dynamic-query-for-d365-odata-entities-...

Hope it helps!

v-sgandrathi · ‎07-24-2025

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?$fil... 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.