Month: June 2017

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

Running a native query within an M project

A few weeks ago, Microsoft published the Power Query SDK, an extension of Visual Studio, on the Visual Studio Marketplace. It is available for free for VS2015 and VS2017. This extension is a really welcome in the world of “Power Query M language” aficionados. By using it, you can save a lot of time when trying to debug a query or when you want to manually test your implementation. Naturally, it’s just a version 1.0.1 and there are rooms for improvements (having more than one file in a .mproj, support for debugging, …) but it’s much better than relying on the “advanced editor” of Power BI (the name “advanced editor” is somewhere fun for just a textbox).

I was just using it since a few days when I found an interesting case. My query had a native query

Sql.Database(
   "server",
   "db",
   [Query = "select * from myTable where field=" & value]
)

When I tried to execute it, I received a message from the Power Query SDK that

The evaluation requires a permission that has not been provided. Data source kind: ‘SQL’. Permission kind: ‘NativeQuery’.

evaluation-requires-permission-not-provided

No luck with my google-fu, so I checked the github repository linked to data connectors and documenting this SDK. Nothing was available in the documentation but an attribute in the project definition caught my attention: AllowNativeQuery. This attribute is by default at false, I changed it to true

<AllowNativeQuery>True</AllowNativeQuery>

I reloaded the project and no surprise: it was where the magic happened. I can now run my native queries within Visual Studio for this project!

Sorting a table based on your own criteria with Power Query (M language)

Earlier, I published a blog about A case insensitive approach to sort a table with Power Query (M language) and I received a few pertinent comments from “Nick”. I agree with most of his comments and some remarks about the syntax were fully valid but the last part of one of his comment was not true (or at least is not true anymore). To quote him:

I wanted to use this method to sort on two fields, I guess I’ll have to go back to converting them to upper-case first, sorting on the upper-case fields and then removing them again

I’m a bit overloaded and I took a very long time to check. But at the end, it’s really possible to define multiple criteria to sort a table.

To illustrate the how-to, I’m starting back from my previous example. I’ll now sort the dataset based the label (column item) without taking into account the case [First criterion – no change] and in case of equality I’ll then sort the rows based on the price (descending) [second criterion to be introduced].

To test this, I’m introducing a few changes in my dataset. I’m adding a tenth record with the item Fishing Rod and updating the price for all the Fishing Rod and Bait items.

let
  Source = Table.FromRecords(
    {
      [OrderID=1, CustomerID=1, Item = "Fishing rod", Price = 100.0],
      [OrderID=2, CustomerID=1, Item = "1 lb. worms", Price = 5.0],
      [OrderID=3, CustomerID=2, Item = "Fishing net", Price = 25.0],
      [OrderID=4, CustomerID=3, Item = "Fish tazer", Price = 200.0],
      [OrderID=5, CustomerID=3, Item = "Bandaids", Price = 2.0],
      [OrderID=6, CustomerID=1, Item = "Tackle box", Price = 20.0],
      [OrderID=7, CustomerID=5, Item = "Bait", Price = 4.25],
      [OrderID=8, CustomerID=5, Item = "Fishing Rod", Price = 120.0],
      [OrderID=9, CustomerID=6, Item = "Bait", Price = 3.25],
      [OrderID=10, CustomerID=5, Item = "Fishing Rod", Price = 80.0]
    }
  ),
#"Changed Type" =
  Table.TransformColumnTypes(
    Source,
    {
      {"Item", type text},
      {"Price", type number}
    }
  ),

The basic principle for sorting based on you own criteria has not changed and you need to specify a list of criterion as the second argument of the Table.Sort function.

#"Sorted Rows" =
  Table.Sort(
    #"Changed Type",
    {
       each Text.Upper([Item]),
       each [Price]*-1
    }
  )

and this method definitely gives the expected result:
sort-table-multiple-criteria