Power BI API connection failure

Hi All,

I am completely new to this so any advice would be appreciated.

The situation is the following:

I created a CH account and created a Live Application and generated a stream API key.

As soon as try to “Get the data” through the web within Power Bi I ve got a 400 error code. Not too sure where is the issue but can someone tell me which is the correct URL I need to use please?

If I have the proper URL I can choose Basic authentication and provide my API key as a user name and ideally everything should work.

Maybe I am completely in the dark so every help is much appreciated.

Thanks

1 Like

Welcome. I would definitely start with:

a) Which API are you trying to connect to? There are several - the main two would be the Streaming API and the Public Data API.

b) You mention a “stream API key”. If you’re wanting to use the Streaming API then Power Bi would not seem to be a good choice. The Streaming API is designed for a process which connects then stays connected, processing data when Companies House send it. If you want to make a query the Public Data API is the one to use.

c) Error 400 suggests you’ve not got the right URL as you say. If you post what URL you are trying to access someone may be able to see what the issue is. The URLs (“endpoints”) are described in the documentation.

Before replying I recommend you look at my answer here to another Power Bi query to see if that helps:

You can also search this forum using the “magnifier” icon at the top right - you’ll find almost every question has an answer that you can find.

Good luck.

1 Like

Hi,

Thank you very much for your quick and detailed response.

The API I try to connect is the https://stream.companieshouse.gov.uk/companies. I use this URL within Power BI and use the Basic authentication option then provide my live stream API key as a username and leave the password section blank.

Unfortunately, I know PBI is not the best to use but I am restricted to use this software. I think about not using Stream but only the REST API which I tried yesterday evening with a same 400 error code result.

I am kind of run out of options what cause my issue.

Thanks

1 Like

With the error do you get an http response body? Check - you may have something like:

{"error":"Invalid Authorization header","type":"ch:service"}

… or something different?

If you have an error 400 (and not e.g. 401, 403, 429 etc) and the above response that should tell you that PowerBI is not sending Companies House correctly formatted information. (Perhaps PowerBI doesn’t really allow blank “passwords” and is putting something in itself?) Ideally you’d check exactly what it is sending. I don’t know if PowerBI can show you this? Or you can use another tool like e.g. WireShark to check what is being sent.

I don’t know if this helps and I have not tried anything shown in this myself but here’s an article Google found me about debugging PowerBI when connecting to an API.

If you have an error 400 and a different response then Companies House may think you’re passing incorrect parameters / URL. Again - you’d need to check exactly what PowerBI is sending.

As always - I really recommend using curl. That is available on most systems and it allows you to check on what is going on. Plus it avoids some confusion which may come from using a tool like PowerBI. Using that (and most likely just the Public Data API, that’s simplest) should enable you to see specifically if you have any authentication issues and what they are.

Try e.g.:

curl -v -u YOUR_API_KEY_HERE: https://api.company-information.service.gov.uk/company/00445790

… where you replace YOUR_API_KEY_HERE with your API key. Don’t forget to include the : after that also.
The -v flag will give a verbose output and show you what’s going on (including the Authorization header).

If Companies House did not recognise your API key you’d expect to see a 401 Unauthorised code. If you were accessing from an IP / “javascript domain” that you had not registered with them when you registered for you API key, then you’ll likely get 403.

I’m not sure that Companies House is completely consistent with these responses though.

What task are you trying to achieve? Is the Streaming API really what you need? I’m not sure this will work for you with PowerBI as I thought that software was mostly designed around running a query (or several) then reporting back. That’s not a good fit for the Streaming API I would think.

1 Like