Forum Discussion
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 ?
5 Replies
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.Hi Watever,
We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
Thank you for being a valued member of the Microsoft Fabric Community Forum!
Hi Watever,
I wanted to check in your situation regarding the issue. Have you resolved it? Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum
Web.Contents is one approach like v-sgandrathi said!
Supporting articles: not sure whether you got a chance to look into these links:Hope it helps!
