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.