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).

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

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.

let
    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)
in
    TextForm

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:

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

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s