OData JSON Light


  • Are there any online sample odata services which implement the JSON Light format that Power Query can consume?



    Sunday, February 16, 2014 11:57 PM


All replies

  • Monday, February 17, 2014 2:29 AM
  • Hi Curt,

    Thanks. I just tried that feed in Power Query, using Fiddler to override the header with

    Accept: application/json;odata=minimalmetadata;q=1.0,application/atom+xml;q=0.8,application/xml;q=0.7

    Everything seems to work except that when you mouseover an entity to get a preview you instead get the message "Formula.Firewall: information about a datasource is required"

    The tables loads if you select one and click "Load" but I'm curious what's the issue with the preview.



    Monday, February 17, 2014 5:48 AM
  • This is an unfortunate consequence of the interaction between our data privacy logic, the case-sensitive nature of URLs and the way we automatically grant permissions to HTTP sites that don't require authentication. If you went into the editor, you probably got a data privacy prompt asking you to set levels for two different (but very similar) URLs. Because that information hadn't been configured before you went into the editor, the flyouts were blocked from fetching data by the privacy policy. But the two URLs that are causing the problem are really for the same site -- in fact, if you had entered the URL with capital "N"s as, you wouldn't have seen either the prompt or the error in the flyout.

    We know we have some work to do to improve this experience.

    Why did you need to change the headers with Fiddler?

    Monday, February 17, 2014 3:13 PM
  • Thanks Curt. I'll experiment with the URL case.

    Setting the header seemed like the most direct way to force Power Query to talk to the Northwind service using JSON.  Glad you asked because I did observe that only two of the three requests were JSON. The service requests and the data request were JSON but the metadata request was not (it was ATOM).  I also observed this. If I set the accept header to just

    Accept: application/json;odata=minimalmetadata;q=1.0

    Then the server returns error "(415) Unsupported Media Type."

    Monday, February 17, 2014 3:52 PM
  • Power Query always tries JSON light first before falling back to ATOM. You shouldn't need to change the headers to observe this behavior.

    There is no defined JSON format for the metadata; it's always XML (but not ATOM).

    Monday, February 17, 2014 4:04 PM
  • Hi Curt,

    Indeed it does! Was that a change in the latest version?

    I do see now in the OData spec that there isn't a JSON representation for metadata.

     - Chris

    Monday, February 17, 2014 5:14 PM
  • I think we first supported JSON light in 2.8.
    Monday, February 17, 2014 5:43 PM
  • Hi Curt,

    I observe that when I made an OData request that the service URL and the metadata URL are each invoke three times even before any data is retrieved. Seems a performance killer. Any way to avoid that?

     - Chris

    Monday, February 17, 2014 8:43 PM
  • I've never seen three requests, but I have seen two. When this happens, it's because of a hard-to fix race condition in the editor where we evaluate the same M code simultaneously in two different processes. The overall impact on client-side performance ought to be fairly small unless you're in an environment with severely constrained bandwidth or memory, as the requests are concurrent and we only need the result of one of the evaluations.

    We plan to address this at some point in the future by rewriting the component in question, but for now this has taken a back seat to feature work. 

    Tuesday, February 18, 2014 3:24 PM
  • Hi Curt,

    I'm pretty consistently seeing three. But I'm running all locally which may exasperate the race condition. Glad to know that it's on the list of things to address.

    If I use the advanced editor and run OData.Feed() then I don't see any duplication.

    Tuesday, February 18, 2014 4:50 PM
  • To clarify, I mean that I put a fully-qualified odata url into OData.Feed()
    Wednesday, February 19, 2014 4:20 PM