Getting companies data into PowerBI through Rest APIs

Hi,

I’m a complete starter :slight_smile:

Can someone please give me a hint how can I GET data through rest API? I have created a REST Key but can’t connect to get any data.

GET https://api.company-information.service.gov.uk/search

how should I know how to modify this url?

I use PowerBI so Power Query

Welcome - thanks for sharing your level of knowledge! How much of a “complete starter”? Tools like PowerBI are designed to simplify things so you can concentrate on the data / your understanding. It might be a better use of your time to find a tool in PowerBI which does all this for you or an add-in for this program. However if not then using the API is pretty simple but it might be worth having some background understanding in a few areas. I assume you can
Google if you’ve got this far :wink:. This forum has a lot of information and a search tool so for details you can find most things here. Searching will find all the following (which is how I got started):

Articles about REST API / PowerBI use
I don’t use PowerBI so I can’t help with detail but there are lots of articles at the end of a Google e.g.:

Just try investigating the data
You can investigate the data - in a very similar way to using the API - via the web interface: https://find-and-update.company-information.service.gov.uk/
If you want to play with the data in bulk you can get various data sets here: Companies House data products - GOV.UK

Read the Companies House documentation
The “first page” is here: https://developer.company-information.service.gov.uk/
It’s worth reading around this. It’s not quite a “step by step” guide but it will give you background information.
The actual documentation for the APIs (there is a main one and a “just download filings” one. There’s also a “streaming” API but I don’t think this is useful for PowerBI):
Companies House Public Data API: Specification summary
Document API: Specification summary

For an overview of REST APIs and how the data gets to you
The http protocol (Wikipedia’s article is short and pretty good) Hypertext Transfer Protocol - Wikipedia
Most guides to REST APIs will give you the basics e.g. Understanding And Using REST APIs — Smashing Magazine

A bit about Companies in the UK / the rules
The law is full of odd details and exceptions. To help understand it better it might help to look at things from the perspective of company directors / secretaries e.g.:
https://www.gov.uk/government/publications/life-of-a-company-event-driven-filingsc

Good luck

Hi,

Thanks for exhausting answer :slight_smile:

I have read dozens pages about power query / basic HTTP authentication etc. Still can’t make Power BI to get data from CH.

I have tried power quesry as below:
“let
Source = Json.Document(Web.Contents(“https://api.company-information.service.gov.uk/company/00000006”, [Headers=[Accept=“application/json”, Authorization=“api-key XXXXXX-XXXX-XXXX-889c-13d07662abca”]])),
messages = Source[messages]
in
Source”

But I’m getting “(400): Bad Request”

I can go easier way with query with GUI:

But what should I put in headers to be able to get data? If I will just click “OK” with link only - it asks for authorization:

When I paste my REST API KEY and hit CONNECT I’ll get error message:

Any ideas?

Just as a check before doing anything further - did you ensure that the url you’re using and your API key work from the place you’re actually running this? I would make sure to do this first - again I’d use a simple command line tool ideally e.g. curl. The curl syntax here would be:

curl -uYOUR_API_KEY: “https://api.company-information.service.gov.uk/company/00000006

The -u flag means “use http Basic authentication with the following username and password. Note the colon (”:") after the API key. This is because the format for http Basic (as provided to curl) is to have a username and password separated by the colon. For Companies House API the API key is the username and there is no password.

(You shouldn’t need the Accept=“application/json” - it won’t do any harm though).

As long as that works then you can go to the next part. I know nothing about Power BI specifically, but it looks like it provides support for several “methods” of authentication (Web.Contents - PowerQuery M | Microsoft Docs). I notice that you’re using:

Authorization=“api-key XXX…

…in your code example and in your screenshots you’ve selected “Web API”.

From the Companies Hosue documents, what you need is “http Basic authentication”. (See my note about that in the part about curl above).

In your screenshots you’ll see there is also a “Basic” option. I believe this is what you’ll need. Again - the username is your API key and there is no password (so that will be blank). It may be that Power BI doesn’t let you have a blank password - I don’t know. If that is true then you’ll only be able to do this via code.

In your code you also should use the appropriate format for http Basic (not “api-key”). I don’t know if Power BI has some specific format for headers but generally when writing a http Basic header, you’ll need to follow the format as given in e.g. the Wikipedia article:

‘Authorization: Basic {credentials}’, where {credentials} is the Base64 encoding of ID and password joined by a single colon ‘:’

So to say this again you’ll need to do your own Base64 encoding. This is unlike when you type the API key into the username box on the “Basic” screen - where presumably Power BI will do the encoding for you.

Good luck.

It works :slight_smile:
Silly enough - I was usung test application API key, after creation a LIVE key it all works fine. I knew it it’s something simple :stuck_out_tongue:

Kind regards