In the blog post I am going to show you the steps that I took to get data from the XE.COM API which uses a username and password to log into the API

You might be thinking that I could put in the username and password when I used the Web Connector.

My challenge is that I wanted to create a function that I could pass through multiple currencies to the API. And in order to do that I wanted to store the details within the function.

Creating the encoded values to pass through in the headers

In order to pass through the authentication this had to be done in the API call in the headers section.

Something that took me a while to figure out was that I had to encode the username and password in Base64 and in a particular format in order for this to work.

  • The format required to encode my username and password was “username:password”
    • For example my Username was: GilbertQ
    • And my password was: Password1234
  • The format to encode would be “GilbertQ:Password1234”
  • I then went to the following site: https://www.base64encode.org/
    • I then put in my details above and clicked on Encode
    • I then copied the output
      • a2FtYWxhdGVjaDM4OTEwMTAxMTphcWtvdmdlcDN2OTk1a3VvazFtajQzZDZkdQ==

Changing the Web Connector to use the Authentication in the Headers

I then needed to update the Web.Contents to include the headers

  • This is what my Web.Contents looked like initially
    // My Function to extract the Exchange Rates
fx_MyExchangeRate = (FromCurrencyISO as text, StartDate as text) => 
    let
        Source = Json.Document(Web.Contents("https://xecdapi.xe.com/v1/historic_rate.json/?from=" & FromCurrencyISO & "&date=" & StartDate & "&to=USD,GBP,EUR&amount=1"
)),
  • I had to then change the connector from Basic to Anonymous, this is because I am embedding the authentication as part of the headers.
  • I then updated it and the trick was I had to put in the Headers section in the right area.
  • I also had to then put in the authorization of Basic and then put in the Encoded value as shown below.
  • // My Function to extract the Exchange Rates
fx_MyExchangeRate = (FromCurrencyISO as text, StartDate as text) => 
    let
        Source = Json.Document(Web.Contents("https://xecdapi.xe.com/v1/historic_rate.json/?from=" & FromCurrencyISO & "&date=" & StartDate & "&to=USD,GBP,EUR&amount=1", 
[Headers = [#"Authorization"="Basic a2FtYWxhdGVjaDM4OTEwMTAxMTphcWtvdmdlcDN2OTk1a3VvazFtajQzZDZkdQ=="]]
)),
  • As you can see above in line 6 is where I added the value
  • This is what it looked like in Power Query
  • Finally, when I then ran my query, I could then successfully see the values

Summary

As shown in this blog post I have embedded my username and password for my API within the headers section of the API call. I also explained and showed how to create the encoding value and then where to put it within Power Query.

Thanks for reading I hope you found this interesting and useful.