Small and medium business | Business Central, N...

How to filter big Nav tables in ODATA or make them fold in Power Query

(1) Share

Report

Posted on by Community member

Hi
Created a webservice in Nav with a very big table which has our items quantities to query w/ ODATA
The goal is to lookup quantities for a much smaller list of some items using the Power Query through ODATA in Excel (so I can upload our inventory feed in eCommerce channels)
https://somedomain.com:7048/Something/OData/Company('Comp')/ItemListODATA?$filter=Gen_Prod_Posting_Group eq 'AAA' and (Status_Code eq 'BBB' or Status_Code eq 'CCC' or Status_Code eq 'DDD')&$format=json&$select=No,Qty1
The problem is that ItemList is very big, has too many rows. Even with filters, it doesn't load to the power query. It loads in the browser after a while but paginates after 100,000 lines. I need to load everything in order to vlookup(xlookup) or /merge/ queries in Power Query for it to fold somehow, but it seems that it doesn't fold.
How do you query these big tables in Nav (for a person who doesn't have much access or understanding of Nav)?

Thanks

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

YUN ZHU 95,681 Super User 2025 Season 2 on at

Like (0)

Report

Hi, there are several solutions to improve performance, hopefully this will give you some tips.

1. Please do not use the standard Item List page, create a new one that does not contain any standard logic.

2. Reduce the fields on the page and only include those that need to be queried and filtered, which will improve efficiency.

3. Please do not use page object. Using Query object will be more efficient. https://learn.microsoft.com/en-us/dynamics-nav/queries?wt.mc_id=DX-MVP-5004336

Thanks.

ZHU

Was this reply helpful? Yes No
Community member 6 on at

Like (0)

Report

Don't know how to create the query.. I've tried to install "Dynamics Nav Development Environment," but it wants an SQL server connection. I'm guessing the SQL server runs on Azure or some cloud and Dynamics credential/server is in another place. I can install the client Nav and connect to its server, but not the SQL server.
So there were many prebuilt queries in the "Web Services," but I couldn't see which one was responsible for the Quantities.
I've managed to query by breaking the ODATA URL with a Parameter and "Invoke Custom Function" in "Add Column" in Power Query, but looping it that way is very-very slow, approximately 1 second per ODATA query (even if I limit it to two columns by using $select=sku,qty)
I have client Nav user privileges that allow me to create Web Services. Do you know of any way I can do what you've suggested me to do? To create a query or a new list

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,681 Super User 2025 Season 2 on at

Like (0)

Report

Sorry, all the points I mentioned require a development license. I think you could contact your partner to discuss this.

Thanks

ZHU

Was this reply helpful? Yes No
Community member 6 on at

Like (0)

Report

So no way to optimize ODATA link, or do it from other available-standard queries/pages?

When I write my ODATA URL in the browser, it paginates.. After some 15-20 minutes, it stops and gives a link to the next page.

"odata.nextLink": "https://...

But Power Query seems not to be aware of this functionality and doesn't automatically load the next page, or it does, and this is why it doesn't end...

I won't mind running it overnight and having a pulled table in PQ in the morning if there is a way.

Was this reply helpful? Yes No
Suggested answer

YUN ZHU 95,681 Super User 2025 Season 2 on at

Like (0)

Report

Unfortunately, as far as I know, there is no good way. . . .

Hopefully other experts can give you better advice.

Thanks.

ZHU

Was this reply helpful? Yes No

Join the conversation

Community site session details

How to filter big Nav tables in ODATA or make them fold in Power Query

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates

Community site session details

How to filter big Nav tables in ODATA or make them fold in Power Query

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates