Importing data from non-OData REST API

Hi there,

I'm attempting to access data from an HTTPS REST API for import into Power Pivot.  The API in question just passes the username and password as URL parameters, rather than OAuth or setting header values.

Accessing the URL directly in a web browser works perfectly (response data is pipe-delimited text), but through Power Pivot (and Power Query) I get a "400: Bad Request".

URL format is like the below (obfuscated):

I've tried playing with the Advanced Settings in Power Pivot to no avail, and have also tried setting up a Data Feed within SharePoint, but all this does is wrap the URL (above) in an .atomsvc document and I end up with the same error.

Any suggestions where to start in order to allow Power Pivot to query this URL?

October 6th, 2014 4:39pm

Can you post the code for the Power Query query you have tried? Have you tried not sending the entire URL but using the Query field in Web.Contents() in Power Query, as shown here:



Free Windows Admin Tool Kit Click here and download it now
October 7th, 2014 7:17am

Hi Chris,

Thanks for the response.  Here's the M code which, unfortunately still results in a (400) Bad Request:

let    Source = Web.Contents(





in    Source

While I've tried Power Query as well, ideally I'm looking to do this directly in Power Pivot, as I want to make use of the scheduled data refresh capability through SharePoint.  But, if I can get PQ working, then it might lead to what the issue is with PP.

October 8th, 2014 7:53am

Strange - it looks ok to me. I suspect this is a bug or a feature: I've heard of a few authentication problems with Power Query:

Maybe they help you diagnose the problem?Chris

Free Windows Admin Tool Kit Click here and download it now
October 8th, 2014 8:01am

Thanks Chris, those links helped me narrow down the problem.

The M code above was indeed correct.  One of my parameters that I obscured in my post contained a "|" character, which seems to have been URL encoded by Excel when the request is built.  The service I was calling doesn't handle this, and returns a 400 (Bad Request).  I discovered this by creating a small .NET app and attempting to make the request directly, and playing around with different parameter values and URL encoding.  When I did it using different parameter values without the "|", it succeeded.  When I included the "|", it failed.

Unfortunately, Power Pivot is unable to consume the URL (without "|") directly anyway.  Because it's not an OData feed, it fails to read the data, throwing an error about missing root elements (if I try to read the response in XML format, it says that it's an invalid OData feed).

Instead I have to use Power Query to access the data and then use the "Load to Data Model" option to get it into Power Pivot.  But, it's a usable workaround for now.

Thanks for your help.

October 14th, 2014 10:46am

GS, and please submit this issue to the product team via the Smile/Frown button in the UI!


Free Windows Admin Tool Kit Click here and download it now
October 18th, 2014 8:07am

I have a quick follow up: Does SSIS 2008 R2 support extracting data from a REST API endpoint? Or does it only support ODATA ones?
March 10th, 2015 4:32pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics