I have made a report requesting company information from Companies House. I have a key, and the query works well in Power BI Desktop/Power Query, where “Data source settings” accepts the API key as user name and no password.
When I try to set it up for automatic refresh in Power Query Service (no gateway), it fails, as I can’t use blank password, the UI requires a password to store the information.
Has anybody managed to trick Power Query Service and in the next step Companies House API with a suitable password? I have tried space, Power Query Service saves the information but I get a 400 response.
This sounds fairly similar to Http Basic Authentication requires NO password
I don’t use Power BI but a quick Google suggests that you should be able to work around any user interface issues by building and supplying your own http header fields using Power Query M Language. Here’s the article I found (I have nothing to do with the author and have not tried this …):
For reference (in case link goes dead) this amounts to something like (again I have no idea if this syntax is correct or the following works):
let
User = "Your API key here",
Password = "",
Plaintext = User & ":" & Password,
Bytes = Text.ToBinary(Plaintext),
Credentials = Binary.ToText(Bytes, BinaryEncoding.Base64)
Source = Web.Contents( "https://api.company-information.service.gov.uk/company/02604354",
[
Headers = [#"Authorization" = "Basic " & Credentials],
]
)
in
Source
Normally it is best to let the tool / interface / library do that for you if the option is available, but if an interface really refuses to allow you to have a specific value (e.g. blank) then this would be the way to work around the issue.
If you search this forum (magnifying glass icon in the top right on the Desktop version of the site) you will find quite a few threads dealing with Power BI etc. which may also help
Normally for “authentication failed” I would expect that you would get a 401 rather than a 400. However it may be that entering a space causes the http Basic header to be incorrectly formed by Power BI. Or perhaps Companies House just returns a 400 if there is anything in the password?
Good luck.
Can’t seem to edit that last but probably needs a comma at the end of the credentials line, before the “Source = …” line.
The Power Query/M part is working, in Power BI Desktop, pretty much as you suggest. It is when I publish the report, and want it to be refreshed on schedule in Power BI Service (“cloud”) that it is not enough with the code inside the semantic model, I also need to do an authentication in the service environment, and this authentication requires a password. As Companies House take the key as userID, and expects exactly nothing as password, this fails.
But thanks for your interest and suggestion!
Ah - in which case have you seen this article on creating a custom connector? (Again - I’ve no idea if this will do what you need, it just seems that others have managed to find a way):
Looks like you wouldn’t need all of this as a) this one is for OAuth and b) reads an OpenApi definition file - there isn’t an official one for Companies House I think. But it does look like it’s possible to write code for the online (not desktop) Power BI service, rather than just having to put values in dialog boxes.
Of course at that point you may have other problems (e.g. Companies House usually requires that you register your key to one of a few IP addresses or (if using AJAX) domain names, which may be more difficult with a cloud-based service (it may not for this one, I don’t know…)
Solved. But I almost don’t know how.
I think I managed by adding the key as User ID in the source authentication dialogue, then a space (or anything) as password, “Skip test connection” unchecked. This results in an error from the source (Companies House’s API), but fear not! Remove the space (or anything) and check “Skip test connection” and “Save”.
Now I could let Power BI Service refresh, manually triggered, and set a schedule, which was triggered as expected. Tadaa!
Glad that works for you, thanks for posting a solution for other users.