Debugging access to REST data sources in Power BI

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.

The Error

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”

PBIErrorREST

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.

PowerShell

A low installation footprint approach is using PowerShell. With the following command you can send a REST request and retrieve the result:

Invoke-RestMethod -Uri <URL> -Body (‘{“systemId”: “it-logix”, “apiToken”: “abcd”}’) -Method POST

SoapUI

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.

SOAPUI

SOAPUI_Response

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.

Fiddler1

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?

JSONResponse1

The HTTP response header of the actual REST service

JSONResponse2

The HTTP response header of the JSON placeholder service

Finally we asked the REST service developer to remove the header line starting with an appostrophe – and suddenly it worked in Power BI 🙂

Advertisements