One of many cool things about Power Query is the way that it allows you to retrieve data from web services and load it into Excel. While this is a subject that lots of people (including me) have already blogged about, the Web.Contents() function – which is the M function that you’ll need to use to call a web service in your query – has gained some new, useful functionality over the last few releases that isn’t fully documented anywhere and which is important to know about.
For this post I’ll use a real-life example of a web service that I’ve been working with recently. As you may know, I’m one of the organisers of SQLBits and a few weeks ago I was given the task of building a Power BI solution to monitor registrations. SQLBits uses RegOnline to handle registrations and they have an API that allows developers to access registration data for events. I’ve been using this API in Power Query.
To take a simple example that illustrates the new functionality in Web.Contents(), take a look at the documentation for RegOnline’s GetEvents method:
http://developer.regonline.com/getevents/
When calling this method in Power Query using Web.Contents() there are two things I need to do:
- Pass two query parameters to it, called filter and orderBy – although I only need to pass empty values to these parameters
- Handle authentication by passing an API token either through another query parameter called APIToken or via an HTTP header also called APIToken
Here’s one way of doing this by handling authentication using the APIToken query parameter:
Web.Contents(
"https://www.regonline.com/api/default.asmx/GetEvents",
[
Query=[#"filter"="",#"orderBy"="",#"APIToken"="insertAPITokenHere"]
])Here’s the other way of using Web.Contents() to do this, by passing the API token through an HTTP header:
Web.Contents(
"https://www.regonline.com/api/default.asmx/GetEvents",
[
Query=[ #"filter"="", #"orderBy"=""],
Headers=[#"APIToken" = "insertAPITokenHere"]
])To explain what’s going on in this second example, the second parameter to Web.Contents() is a record (so it has to be enclosed in square brackets) containing two options fields, Query and Headers. The Query field is itself a record, with one field for each of the two query parameters I’m passing. The Headers field is another record, with one field for each custom HTTP header I need (see this post on the Power Query forum for more details). The RegOnline API specifies that for the purposes of authentication a header is needed called APIToken which contains the API token, so the Headers option here contains one field for that header.
The problem with handling authentication as I do in both these examples is that I need to hard-code the RegOnline API token in the code for my Power Query query, which is not exactly secure because anyone who can see the code for my query can see the API token.
Instead, Power Query gives you the option of storing the API token in its own secure credentials store on your own PC (the same one that it uses for storing the usernames and passwords you enter for all other data sources). This only works if authentication takes place through a query parameter, as in the first example above – unfortunately it can’t be used if authentication needs to take place via a HTTP header as in the second example.
To use this technique I have to use the ApiKeyName option to tell Power Query the name of the query parameter that should contain the RegOnline API token:
Web.Contents(
"https://www.regonline.com/api/default.asmx/GetEvents",
[
Query=[ #"filter"="", #"orderBy"=""],
ApiKeyName="APIToken"
])Now, when the Power Query query is executed for the first time, you get prompted to tell Power Query how to authenticate the GetEvents method and in the dialog you have to choose Web API and enter the API token in the Key box, as shown below:
After this the API token gets stored separately from the query in the secure credentials store and you don’t need to enter it again each time the query runs; this also means that the API token will not travel with the workbook if you email it to someone else, for example.
One last thing to mention (and this is something that deserves a separate blog post really) is that while Web.Contents() generates a GET request by default, you can make it generate a POST request by specifying the Content option. You can find details on how to do this on this thread started by Lee Hawthorn on the old Data Explorer forum.
That’s AWESOME Chris, I was just trying to work out how to do this 🙂
Reblogged this on MS Excel | Power Pivot | DAX .
Chris, do you think that this technique could also work with Google Analytics … thank you for your feedback.
I don’t know anything about the Google Analytics API so I can’t say, sorry…
It`s good to know that even you don`t know 🙂
Chris, the question remaining is if/when will it be possible to define refresh schedules for his kind of connections in a BI Site and/or SharePoint Online. Because that would be very very cool!
Yes, it would be – hopefully support for Power Query in the cloud comes soon!
Really, ahead we need power Query in cloud comes.. be helpful fir developers…
Thanks, I’d been trying to figure out more about using Power Query for data collection from APIs.
One concern I had though were API call limitations and charges, so I wanted to find out if Power Query would make more calls than necessary. I didn’t find anything much about it online, so tried some basic testing, checking a server log after restarting Excel to see whether it would refetch the pages. I did get more server hits this way than expected though, so I’d rather not use it for this on a bigger scale, fearing it may occasionally refetch a bunch of things at once.
Hi Chris,
Is it possible to combine input that is split over 6 rows on an input file into a single record on Output using the M language?
For example on input rows 1, 7, 13, 19 etc each sub-set of 1 logical record begins.
As the first step I wish to combine these as the ID is only on the first record of each set.
Thanks,
Ted.
Yes, it sounds possible. I would try the following: add an index column, then calculate the value of the index mod 6, then pivot the table on this value.
all our web-sources require us to authenticate using a username and password, is it possible to perform an auto login when the username/pwd form is presented by the web-site ?
Hmm, I don’t think so, sorry.
Chris-
Very helpful post! However I do have one question. You mention that ApiKeyName tells power query the name of the header that contains the API key. However, when we implemented this instead of modifying the header it added a query string parameter. For example, when we did something like this:
Web.Contents(
“https://url/”,
[
ApiKeyName=”APIToken”
])
when we run the query the url ends up being:
https://url/?APIToken=tokenvalue
Fortunately for us we’ve implemented the Bearer strategy which allows for a query string token as well but I would much prefer including the token in the header. Any thoughts as to why this is occurring?
Strange – are you using the latest build of Power Query?
Version: 2.12.3660.142, as far as I know this is the most current public build.
Might be a good idea to ask this question on the MSDN forum and see what the dev team say.
Thank you for this post. Unortunately I have problems with generating a query. I’m trying to replicate this one in Power Query:
POST /json-api/v4 HTTP/1.1
Host: api-sandbox.direct.yandex.ru/json-api/v4/
Content-Type: text/plain; charset=UTF-8
{
“method”: “GetCampaignsList”,
“locale”: “ru”,
“token”: “1b652016a45d4b5db18132e071e36489”
}
But I have no idea on how to use method POST (I read linked article regarding this – but no idea on what to put inside Content). Maybe you can help me with this.
Hi Edgard,
To be clear, you’ve read this post? http://cwebbbi.wordpress.com/2014/04/19/web-services-and-post-requests-in-power-query/
From what I can see, you need to use the Web.Contents() function as described in this post and pass in the following text to the Content option:
{
“method”: “GetCampaignsList”,
“locale”: “ru”,
“token”: “1b652016a45d4b5db18132e071e36489″
}
You will also need to use Text.ToBinary(), also shown in the post, before you pass it in.
Hi Chris, I’m built my own wcf service which feeds a Power Query application I’ve created. When I updated the Power Query it requires me to authenticate. Consequently I am converting my service to a secured service (should have done that from the start). What security mode did you use is the SQL Bits API? Do I need to be concerned with which security mode I implement when using the Web.Contents() feature?
Hmmm… I’m not a developer, so I don’t really understand what you’re asking here, sorry!
Hi Chris,
I am trying to get data from a research database into web (scopus, web of science), using an automatic configuration script as in this image: http://www.cs.nuim.ie/~vishal/images/winxp/ie_proxy.jpeg
I can perform this sort of proxy settings and retrieve data from web that need authentication before? I am receiving this error message “(407) Proxy Authentication Required”.
I’ve read some articles arguing that might be needed creating a file in the Excel.exe file location, like here:
https://social.msdn.microsoft.com/Forums/en-US/5c88db17-27f1-41aa-8099-185fe3e7fb94/using-data-explorer-behind-a-proxy-solution
or here:
https://social.msdn.microsoft.com/forums/sqlserver/en-US/cd5c7877-b7e4-4041-a1d6-feaa33e5e74e/powerpivot-datamarket-place-and-proxy-authentication
Best regards,
Felipe
Brazil
I don’t know, sorry – the best place to ask this question is on the Power Query Technet Forum, I think.
Thanks Chris!
How would one set this up with a two-step auth? And how would one set that up to automatically refresh? I’ve been able to acquire my data through two step authentication (posting the username and password and taking the access_token from the response), but when I try to set it up for automatic refresh it whines about credentials. How can I work around this?
The only way you’ll get this to work properly is to write a Power BI custom data connector, and unfortunately that’s still in preview and not supported for scheduled refresh yet. Sorry!
Hi Chris,
out of curiosity – have you had a chance to connect Power BI to any API?
I’m struggling with PBI connection to Smart Rectruiters API. I’ve posted an issue on a community page, no luck.
Any help appreciated.
Community.https://community.powerbi.com/t5/Integrations-with-Files-and/PBI-SmartRecruiters-connection-API/m-p/377532#M16217
SMartRecruiters API: https://dev.smartrecruiters.com/customer-api/live-docs/customer-api-2/
Kind regards,
Chris
What kind of errors are you getting?
The key one is: “I continuously get an error saying: “A web API key can only be specified when a web API key name is provided”. I do have a key generated by SR admin – and he assured me there was no such a thing like key name.”
I’ve just taken a brief look at the documentation (https://dev.smartrecruiters.com/customer-api/authentication/api-key/) and it seems like you’ll have to pass the key through a HTTP header called X-SmartToken. I show how to pass a key to a header in the post; you can’t use the Web API authentication method when passing a value to a header, you’ll have to use Anonymous authentication in this case.
I want to use Soap service in POWER Bi , can you please provide any helpful material. I am just Noob to the tool
I want to use Add service from the mentioned linked
http://www.dneonline.com/calculator.asmx
Web.Contents(
“http://www.dneonline.com/calculator.asmx/Add”,
[
Query=[ #”intA”=”2″, #”intB”=”4″],
])
it give me error
DataSource.Error: Web.Contents failed to get contents from ‘http://www.dneonline.com/calculator.asmx/Add?intA=2&intB=4’ (500): Internal Server Error
Details:
DataSourceKind=Web
DataSourcePath=http://www.dneonline.com/calculator.asmx/Add
Url=http://www.dneonline.com/calculator.asmx/Add?intA=2&intB=4
how to slove this
Looking at the documentation, to use this service you need to make a POST request and pass some XML in the body with the parameters – not pass the parameters through the url in the way you’re doing. See here: https://blog.crossjoin.co.uk/2014/04/19/web-services-and-post-requests-in-power-query/ for details on making a POST request.
Chris, I am trying to figure out how to connect to a Dynamics 365 CRM On-Prem Web API Service but can’t figure out the authentication setup. I have Registered an App in AAD and have the Key etc. But I just can’t figure out what goes where in Excel Power Query when trying to setup Get Data\From Web\Advanced. I just don’t know what goes where, I read that some data, the APP Key, must be sent in Headers. If I had a specific example done/described in Postman maybe I could figure it out from that as well. Any help would be appreciated, I just can’t a very specific example of what exactly goes where in the auth fields.
Chris, you mention that the Web API Token is Secure:
“After this the API token gets stored separately from the query in the secure credentials store and you don’t need to enter it again each time the query runs; this also means that the API token will not travel with the workbook if you email it to someone else, for example.”
1) Can you expand on this? How so?
a) Is it encrypted when it is passed to the API?
b) How is it stored securely in the PBIX workbook (can some else see it if they DL from PBIS)?
Thank you for your help!
Only someone from the dev team can answer questions (1) and (2), I’m afraid. For question (3), no credentials are ever stored in a Power BI pbix file or an Excel file – they are stored separately, so the credentials never leave your PC. If you publish up to the Power BI server they need to be re-entered, and there’s no way anyone can see those credentials after they have been entered in any scenario.
hello! how can I setup the credentials now in the power bi service? I cannot find it, I use a simple key. Thank you very much in advance.
Hello,
Does any of you know plans for supporting ApiKeys as Credentials that are sent in Headers? I have a few API-s that use headers and I would prefer not to store the secret key in the query definitions.
Tx,
Chris, great article. How do you manage the refresh (i.e. call the API every 30 seconds)?
Hi chris,
I keep getting this error when trying to connect to a REST API. i did use your method of APIKEYNAME, but the error persists.
“A web api key can only be specified when a web api key name is specified.”
The eror occurs in the credentials window when i enter the API key
Please help
Hi Chris,
I was wondering if you could give me some insights on how to get the response headers of a POST request. I am developing a custom connector in SDK and I thought I would get the response headers in the Metadata, but these are restricted just as in Power BI.
Thanks in advance
I don’t know, sorry – I think the best thing to do is post the question on the connectors GitHub repo forum https://github.com/microsoft/DataConnectors/issues
Hi Chris, I am looking for a while to get my issue solved.
I have found a way to call an API but getting error:
DataSource.Error: Web.Contents failed to get contents bad request 400
Any help? thank you
let
getProjects= let
relpath = “/api/v1/interop/test”,
Body = “{“”ResultType””:””Hierarchical””,””FilterConditions””:{“”EntityName””:null,””Filter””:null,””Sort””:null},””PageNumber””:1,””PageSize””:25,””ProjectionOnly””:false,””Parameters””:{}}”,
Source =Json.Document(Web.Contents(“https://test.com”,[RelativePath = relpath,
Content = Text.ToBinary(Body),
Headers = [
#”Content-type” = “application/json”,
Authorization = “Bearer tokentext”]])),
response = try Source otherwise null
in Source
in getProjects
Are you trying to call a REST API that uses OAuth2 authentication? If so, see https://blog.crossjoin.co.uk/2021/08/29/connecting-to-rest-apis-with-oauth2-authentication-in-power-query-power-bi/
Thank you for your reply! I am not sure how to find that.. I have successfully connected through postman and call the API (Post method). Also, successfully connected to get only the token via power query. But when trying to call the actual request (via power query is getting the error.. I feel that defining the body is the hard part.. this is the body: {
“ResultType”: “Hierachical”,
“FilterConditions”: [
{
“EntityName”: null,
“Filter”: null,
“Sort”: null
}
],
“PageNumber”: 1,
“PageSize”: 25,
“ProjectionOnly”: false,
“Parameters”: {}
}
In any case, I will ask tomorrow the support and check your recommendations.
“see this post on the Power Query forum” – the link provided here is no longer working.