Recently I was involved in building a Power BI report connecting to a REST API as the data source. In this blog post I show you what tools made my life easier working with REST services.
In order to access the REST service we needed to create a token first and pass this to the service in the HTTP header. We followed the advice here to achieve this in Power BI directly. But it didn’t work and we always got the following error at some point: “Specified value has invalid HTTP Header characters”
As far as I observed it, Power BI is pretty intransparent what happens behind the scene. So first of all I wanted to know whether the REST service works as expected.
Testing the REST service outside of Power BI
There are various options make a REST call, I’ll mention two of them.
A low installation footprint approach is using PowerShell. With the following command you can send a REST request and retrieve the result:
At least for me a more convenient way is using a non-command-line tool, for example SoapUI.
Using SoapUI I could proof the REST service works as expected. In the second screenshot on the right side I can see the actual data returned from the REST service.
But why would Power BI throw an error anyway?
Bringing light into what happens behind the scene of Power BI with Fiddler
Looking at the error message, my first guess was that something is wrong with how we set the request header in Power Query. To bring more light into this I used Fiddler. Fiddler links itself into all HTTP traffic on your computer and makes it visible for you to analyze it.
The Power BI traffic can be found in the microsoft.mashup… process.
To my surprise I found that Power BI called the REST service successfully including getting the correct data back similar to what I got in SoapUI. But why would Power BI throw an error anyway?
Compare with a generic REST service and get the riddle solved
As always in a debugging process it is helpful to have a second example to check whether an error is generic or specific to one case. For that I used a service called JSON Placeholder. Here the data import to Power BI worked like a charm. Once again using Fiddler my interest was about the response header. I compared the header of the generic service with the one in my project. Can you see it?
Finally we asked the REST service developer to remove the header line starting with an appostrophe – and suddenly it worked in Power BI 🙂