Skip to main content

Notifications

Microsoft Dynamics NAV (Archived)

How to make filters on odata url in Excel

Posted on by 285

Hello,

How can I put a date filter on the odata uri when using Excel?

I can make a connection and get the G/L Accounts into Excel. But I also want the user to be able to make date filters.

But when I try to add ?filter= to the connection command text Excel gives an error "We cannot find the table 'G/L Account?filter'... in the datamodel.

Is it possible to make an uri filter on the odata connection in Excel?

*This post is locked for comments

  • Suggested answer
    keoma Profile Picture
    keoma 32,675 on at
    RE: How to make filters on odata url in Excel

    hi,

    which excel version do you use?

    did you try to import using "Data tab" --> "Get External Data" --> "From Other Sources" --> "From OData Data Feed"?

    i tried additional following (using excel 2010):

    * On the Data tab --> From Web: giving odata url --> imported --> got a ready to use table with the received data

    * "Data tab" --> "Get External Data" --> "From Xml Data": giving the odata url --> imported --> resulted in a textual import of the resulting xml doc.

  • Anders Hedegaard Profile Picture
    Anders Hedegaard 285 on at
    RE: How to make filters on odata url in Excel

    Hi Jonathan,

    thank you for helping.

    I understand that I can make filters in the url and that works just fine in the brower in my setup as well.

    But I cannot use these urls in Excel. I get an error saying "The table does not exist".

    NB: I am not using Power Query.

    thank you

    Anders

  • Suggested answer
    keoma Profile Picture
    keoma 32,675 on at
    RE: How to make filters on odata url in Excel

    hi,

    i published the Item List as webservice and tried the following sample "all items with last modified date equal to 2013-11-07":

    localhost/.../Company('CRONUS%20AG')/ItemsList?$filter=Last_Date_Modified eq datetime'2013-11-07'

    when executed in the browser it is translated to:

    localhost/.../Company('CRONUS%20AG')/ItemsList?$filter=Last_Date_Modified%20eq%20datetime%272013-11-07%27

    this worked fine.

    the same with "greater than" instead of "equal":

    localhost/.../Company('CRONUS%20AG')/ItemsList?$filter=Last_Date_Modified%20gt%20datetime%272013-11-07%27

    worked also fine

  • Anders Hedegaard Profile Picture
    Anders Hedegaard 285 on at
    RE: How to make filters on odata url in Excel

    Hi Tharanga,

    thank you for the response.

    I still do not see a solution in the links you sent.

    If the dataset is large, then I want to make filters before I retrieve the data.

    For example this uri does not Work in Excel.

    soda.demo.socrata.com/.../4tka-6guv Islands region&source=pr

    Excel will give the error that the Table 4tka-6guv?region does not exist.

    1. I cannot add the uri to the connection string

    2. I cannot add the 4tka-6guv?region=Virgin Islands region&source=pr" part in the command text.

    Thank you

    Anders

  • Suggested answer
    TharangaC Profile Picture
    TharangaC 23,116 on at

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,134 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,928 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans