PowerQuery and NAV?

Lgoo
Lgoo Member Posts: 45
in NAV Three Tier
Hi,

i've got a Table with about 6 million records. This table/page is published with ODATA, so i could load it with PowerQuery in Excel. First i want to add some filters to the query, but adding filters results in loading the hole table data into PowerQuery. This takes some hours..
I see that the filtering on date fields is not working correctly. Powerquery loads all Records from Dynamics NAV and filters them after loading.
How can i load only a preview and not the hole table into excel, so i can add some more filters that the query would only return some tousend records?


Thanks

Answers

  • Yachty
    Yachty Member Posts: 17
    6 million records should not be a problem, if you delete not required columns. What are the Query Settings? Load to worksheet = On
  • Lgoo
    Lgoo Member Posts: 45
    Yes worksheet is on, i tryied it also with off. It makes not much different when i create a Query in Dynamics NAV with only 6 rows based on G/L Entry Table.
  • Yachty
    Yachty Member Posts: 17
    Then perhaps use Power BI Desktop instead? It's Query Editor would only load more records when you scroll down.
  • Lgoo
    Lgoo Member Posts: 45
    With Power BI its the same behavour when i put a filter on Posting Date. It loads hundreds Mbyts into Power BI. But this cannot be, becouse i filter on 1 day...
  • Yachty
    Yachty Member Posts: 17
    I just tried it (using Excel 2016) from the Navigator window where you choose xxx$G_L Entry do not select Load but Edit and the use the Column Filter on Posting Date. It only loaded the resulting records into Excel. What version are you on?
  • Lgoo
    Lgoo Member Posts: 45
    Thanks for trying, but it looks like you connecting directly to the SQL Server becouse you write the table "xxx$G_L Entry". I need to connect to the Data via ODATA Feed. I create a Webservice based on a query in NAV an publish it.
  • aseigle
    aseigle Member Posts: 207
    instead of exposing the page as ODATA, why not build a Query object in NAV so that you can prefilter the data set?
  • Lgoo
    Lgoo Member Posts: 45
    Becouse the user needs to compare different years. But if ODATA with powerquery is so unusable slow and cannot load at least a million records, its nothing for productiv environment...
  • Jut
    Jut Member Posts: 72
    Hi Lgoo,

    it sounds strange to me that loading a couple of million records takes hours. What is the infrastructure? Is the service tier in a LAN or WAN? What is the max page size for OData of the service instance?

    Filtering on Odata-feeds should work with date-fields as well, as described here: http://www.navida.eu/filter-nav-odata/

    Does that help?

    Best regards,
    Jut

Categories