none
Power Query error importing certain OData feeds from Dynamics CRM 2013 Online

    Question

  • I am trying to consume data from an OData feed from Dynamics Online.  I have exposed several tables as feeds.  4 of the 6 tables were imported successfully and loaded to the data model (Customer, Salesperson_Purchaser, Country_Region, Currency Exchange Rate).  Two tables (Sales Invoice Line and Sales Invoice Header) show preview data in the Power Query window, but show an error when I try to import the data to Power Pivot (or refresh the Power Query query after initially clicking Apply & Close). The Power Query queries show me that it imports several kb of data, then tell me that it loaded 0 rows. If I go look in Power Pivot, I see the following error text.  I tried switching from load to data model to load to worksheet, but got the same result.

    [DataSource.Error] Cannot parse OData response result.  Error: An error was read from the payload. See the 'Error' property for more details.

    What kind of error does this indicate?  Where to I go to see the error property for more details?

    Friday, February 21, 2014 3:30 AM

Answers

All replies

  • Error values are typically accessible only inside the editor -- where unfortunately you're not actually getting the error.

    Are you able to use a tool like Fiddler to capture a network trace of these requests?

    Friday, February 21, 2014 2:41 PM
  • For one of the traces, fiddler does report that an error was encountered on the provider side (at least, that's how I interpret this)

        {"odata.error":{"code":"","message":{"lang":"en-US","value":"An error occurred while processing this request."}}}

    Friday, February 21, 2014 4:27 PM
  • After further investigation it seems that the OData feed is paging the results at 1000 records and Power Query doesn't seem to know how to interpret the skip token to get to the next set of results. 

    Has anyone done this successfully without manually pulling in each page of results and appending them?

    Friday, February 21, 2014 5:02 PM
  • Well, that's not good.

    Can you modify the call to OData.Feed so that instead of OData.Feed(url) it says OData.Feed(url, null, false) and see if that makes a difference?

    Thanks!

    Friday, February 21, 2014 8:43 PM
  • Thanks for the suggestion, but that results in the same error.
    Friday, February 21, 2014 11:30 PM
  • What's the exact URL that returns the error? Or would you prefer to report that privately?
    Saturday, February 22, 2014 4:14 AM
  • Dear Curt,

    I am experiencing the same problem when trying to import data from Microsoft Dynamics NAV. NAV allows to set a parameter "Max. Page size". The size is by default set to 1000 and I actually get the error whenever 1000 rows are exceeded. My workaround in NAV is to change the limit to something very high but I thought it still would make sense to share it here.

    The query that I am calling is the following:

    http://localhost:7048/DynamicsNAV71EXT/OData/Company('CRONUS%20EXT%20AG')/xmlquery

    I could share the fiddler-trace but it would not show anything interesting besides the number 1000 :)

    Best regards,

    Julian

    P.S.: I tried the suggestion to add the two extra parameters to the oData-call but unfortunately that did not make a difference. I am using Power Query 2.10.3598.81

    • Edited by Julian Wissel Sunday, March 30, 2014 6:59 PM forgot something
    Sunday, March 30, 2014 6:55 PM
  • I have a similar problem with Odata from NAV 2013 R2. But in my case I am not sure it is the page size (does not seem like it). 

    The error I am getting is: DataSource.Error: Cannot parse OData response result.  Error: A type named 'NAV.SalesOrders' could not be resolved by the model. When a model is available, each type name must resolve to a valid type.

    1. What am I looking at here. Does NAV send some extra field which is of a type that Power Query cannot handle?

    2. Looking at the discussion with page sizes. Does this mean that the Odata feeds is not suitable for example importing Value Transactions (millions of rows) for analysing inventory levels etc? I have mostly worked directly with SQL-connections for those purposes, but I would really like to use the user access and security features that NAV are giving me with Odata.

    Wednesday, April 02, 2014 6:32 AM
  • Hi Adam,

    1. Could you attach a text-export of the page that you are publishing as a web service?

    2. We just posted a workaround for the issue on our blog - see the last part:

    http://www.navida.eu/how-to-report-in-dynamics-nav-across-companies/

    in short: in the NAV Administration Management Console the size per page can be defined. A very high value should avoid the "Payload"-Error.

    I hope this helps.

    Regards,

    Julian


    Wednesday, April 02, 2014 12:53 PM
  • Great blog entry, Julian! I'm still very interested in seeing what the "next" URL looks like and how it compares with the original URL. If there's a problem in Power Query with following OData next links, we need to fix it. (Or if there's a problem in NAV with generating them, we need to report it... :)

    Adam, we'd need the text of the feed as well as the text of the metadata document to understand why you're getting that error. The easiest way to get those things is to do a trace with Fiddler.

    Wednesday, April 02, 2014 2:05 PM
  • Thanks Curt!

    May I send the fiddler-export by Mail?

    Regards,

    Julian

    Wednesday, April 02, 2014 2:28 PM
  • Absolutely. Our feedback alias is pq2fb@microsoft.com, and it would be great if you could reference this thread by URL.
    Wednesday, April 02, 2014 2:53 PM
  • Thank you for the workaround! Hopefully this workaround wont stress the NAV server too much when fetching a lot of data.

    Regarding my other issue I am not sure how to recreate it actually, now everything works as it should. Sorry for being confusing.

    Once again, thanks!

    Wednesday, April 02, 2014 6:23 PM