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):

https://api.mydomain.com/v1/reportitems.xml?itemid=xxxxxx&startdate=20140101&enddate=20140131&format=csv&client=xxxxxx&user=xxxxxxx&password=xxxxxxx

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?

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:

http://cwebbbi.wordpress.com/2014/03/26/working-with-web-services-in-power-query/

?

Chris

Hi Chris,

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

let    Source = Web.Contents(	
	"https://api.mydomain.com/v1/reportitems.xml",	
[	
Query=[itemid="xxxxxx",startdate="20140101",enddate="20140131",format="csv",client="xxxxxx",user="xxxxxx",password="xxxxxx"]	
])
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.

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:

http://social.technet.microsoft.com/Forums/en-US/750a426d-4b72-4dba-9e44-fe3dfb54b47d/web-content-user-not-authorized-credential-error?forum=powerquery

http://social.technet.microsoft.com/Forums/en-US/f4c5b66a-6b13-4326-8059-8b310d7acad2/downloading-data-from-web-xml-file-thourgh-rest-api?forum=powerquery

Maybe they help you diagnose the problem?Chris

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.

• Proposed as answer by Ed Price - MSFTMicrosoft employee, Owner Saturday, October 18, 2014 8:07 AM
• Marked as answer by Charlie LiaoMicrosoft contingent staff, Moderator Tuesday, November 11, 2014 3:03 AM

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

Thanks!

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?