Getting data from companies house using API key

[borozu]

Hi,

I can't get any data from link:

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

even using proper api key.

https://developer.company-information.service.gov.uk/authentication 

I'm getting errors like:

"A web API key can only be specified when a web API key name is provided."

what should I set in headers parameters?

[tigertedd]

Hi,

I cracked it, sort of, this post got me on the right track. I assumed the authenticaiton would be through the web api, and got the same message as you, but if you read the Companies Hoiuse guidance, it says it uses basic authentication. That's an option in Power Query. The user name is the api, and there is no password. So using the basic option, this worked.

However, now my problem is that it pnly brings up the info for that one company 000006, which kind of defies the point. What I'm looking for is a table based on a big list of company registration numbers I've got, of addresses etc.

Any idea how we go about doing that?

[PaulMac]

I wonder, did you ever find out how to get all company data from Companies House?

[borozu]

Oh, I was doing it 2 years ago so barely remember anything 😄

But simple answer is no as project was handed over to someone else. I managed to get API running but you have limit (500 requests per certain time - you can find it in API specification) but I remember from basic calculation that to get all companies via API you have to let system run for a month. So technically it is possible.

I guess there's more clever way to do it but I'm just a student and that's all I could get at the time

[PaulMac]

Ah OK. Thanks for the response. Atealst you didn't have hassle of dealing with it. Hope the colleague it was passed on to had better luck. 😅
I have spoken with some of my colleagues before I fell down the rabbit-hole in trying to build this API connection. Turns out we have it set up already in our D365 environment so I can just tap in to that.
Cheers
P

[v-kelly-msft]

Hi  @borozu ,

See Chris Webb's post on this for a good example:

 https://blog.crossjoin.co.uk/2014/03/26/working-with-web-services-in-power-query/.  

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!