Encoding basic authentication in an url with Power Query M language

More than one year ago, I edited a blog post about how to get data from Neo4j with Power BI and yesterday, Chris Skardon published a cover enriched with more details (useful if you’re new to Power BI). But Chris did more than a cover and also changed the way to authenticate. In my version, I circumnavigated the authentication obstacle by adjusting the server settings to let me run anonymous queries but Chris overcame it by providing a header to the web request (See bellow).

  Source = Web.Contents( "http://localhost:7474/db/data/transaction/commit",
      Headers = [#"Authorization" = "Basic bmVvNGo6bmVv"],
        ""statements"" : [ {
          ""statement"" : ""...""} ]

Chris explained that he encoded his user/password combination with the help of C# or PowerShell. Then he added this encoding string in the header for the Authorization tag, after the basic keyword.

This is a good idea but I thought it was also a good idea to explain how to execute this base64-encoding without relying on an external process … just with Power Query M Language!

The first step is to correctly format the string to encode. The expected format is user:password. I can create two parameters User and Password and then creating a new query named Credentials and apply the format.

    Source = User & ":" & Password,

After this you need to convert your text to bytes by the help of the function Text.ToBinary

    Bytes = Text.ToBinary(Source),

When you have the bytes, you need to encode them to a string with the help of the Base64 binary encoding.

    TextForm = Binary.ToText(Bytes, BinaryEncoding.Base64)

You can now edit the main query and replace Headers = [#"Authorization" = "Basic bmVvNGo6bmVv"], by Headers = [#"Authorization" = "Basic " & Credentials],.

That’s it, the encoding is performed in Power Query M language.

Full code:

    Source = User & ":" & Password,
    Bytes = Text.ToBinary(Source),
    TextForm = Binary.ToText(Bytes, BinaryEncoding.Base64)


  1. Could this possibly work for the Teradata.Database() function?

    The connection string is: Data Source=myServerAddress;User ID=myUsername;Password=myPassword;

    For the .NET Teradata driver.

    1. This post is about encoding authentication in an url … not sure what’s the link between a connection string and an url? Especially that Power BI as a native connector for Teradata. Did you want to comment on this https://seddryck.wordpress.com/2017/02/04/how-to-connect-to-mysql-with-nbi/ post? If yes, then the answer is “no” … you must use an oledb or ODBC driver not a native driver. Biut if you use an oledb or ODBC driver then “yes” it will work.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s