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.

  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" =
      {"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" =
    #"Changed Type",
       each Text.Upper([Item]),
       each [Price]*-1

and this method definitely gives the expected result:


Presenting before the summer ’17

I’ll be really busy presenting before this summer. With four sessions (including three completely new) to deliver in less than 30 days, I’ll have less times for my open-source projects.


The first session will be at Charleroi (Hainaut, Belgium) on the 2nd of June for the Journée Agile 2017. This is a bit special for me, I never talked so close to my hometown and it will be my first time in Wallonia since 10 years. No one is a prophet in his own country, even if it is a small one! It will also be different because the main topic of the conference is not SQL Server or databases but agility. A concept that I use since 10 years, I’m really not a newcomer. Agility and BI are two concepts with little traction, let’s break the walls! I’ll speak (in French) about testing automation for BI solutions, this is the session that I’m presenting across Europe but I’ll adapt it a bit to the attendance and stress the agility topic (and a bit less time on the demos). If you’re interested by the agile topic (and if you’re not, you should really care about this … the world has changed, hurry up!), a few seats are still available.

The next stop will be at SQL Saturday Rheinland (Sankt Augustin, North Rhine-Westphalia, Germany – University between Bonn and Köln). The session will be about the M Language (the language used for Power Query/Power BI to get and transform data) and how much it’s fun to code with a functional language. It’ll be the first time that I’ll present this session as-is but I already included large parts of this session in some blog posts or show-cases. Not a lot of stress to be ready on time. About M, the main topic of this session, I’m really impressed by the power of this language and I’ll try to share a bit of my knowledge and help you to make the first steps in the right direction. Don’t be confused, it’s not a session about Power Query/Get & Transform, I won’t use the UI … I’ll show you the code and how to resolve more advanced cases that you won’t be able to implement with just the user interface.

SQLSaturday #605 - Rheinland 2017

Then, at the end of the month, I’ll be presenting at the 24 Hours of Pass (French edition – 2017). This year, I accepted to present two sessions (Webcasts in French). Can you really say “no” twice to Isabelle and Jean-Pierre? The first session is about going high in normal forms for data warehouses. Don’t panic, I won’t be boring explaining in theory what’s a normal form. I just want to explain why Data Vault, Anchor Modeling (and Tibre Modeling, my own “methodology” to model time-based relations in a data warehouse) are really powerful approaches for some specific cases. I’ll also show you why SQL Server is suited to host and serve this kind of model. I’m really excited about this session, it’ll be a worldwide première and I hope to have the opportunity to present it again later, let’s cross-fingers.

The second session at the 24 HOP French Edition is about Graph processing with SQL Server 2017 (aka SQL Graph). That’s now several years that I’m following Neo4j and I appreciate how simple it’s to resolve some use-cases with Cypher and this engine. I still need to develop large parts of my session but the plan is surely not to make an in-depth comparison of the two engines. I’m expecting to explain why a Graph processing is better suited for some specific cases and show the difference in terms of expressiveness and performance with a standard SQL approach.

A case insensitive approach to sort a table with Power Query (M language)

Have you already tried to sort a table based on a text field? The result is usually a surprise for most people. M language has a specific implementation of the sort engine for text where upper case letters are always ordered before lower case letters. It means that Z is always before a. In the example (here under), Fishing Rod is sorted before Fishing net.


The classical trick to escape from this weird behavior is to create a new column containing the upper case version of the text that will be used to sort your table, then configure the sort operation on this newly created column. This is a two steps approach (Three steps, if you take into account the need to remove the new column). Nothing bad with this except that it obfuscates the code and I hate that.


The documentation of Table.Sort is really sparse, especially the last parameter comparisonCriteria. I never found any documentation on this parameter and most of the available examples just use the name of one of the fields of the table. Hereby, I’ll give you a trick to apply a case insensitive sort for a table in one step.

The most important thing to understand is that comparisonCriteria doesn’t need to be a column, it can be any function. Based on this, it’s an safe move to change the code {"columnSort"} into {each Text.Upper(_[columnSort])} … and that’s it!

  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 = 3.25],  
      [OrderID=8, CustomerID=5, Item = "Fishing Rod", Price = 100.0],  
      [OrderID=9, CustomerID=6, Item = "Bait", Price = 3.25]  
  #"Changed Type" = 
    Table.TransformColumnTypes(Source,{{"Item", type text}}),
  #"Sorted Rows" = 
    Table.Sort(#"Changed Type",{each Text.Upper([Item])})
  #"Sorted Rows"

Auditing login attempts with SQL Server

Today’s quest is similar to the blog post that I wrote two months ago about auditing security changes: we’re admin of our non-production environnements of SQL Server and we’d like to put in place some auditing. In this case we’d like to store all the login attempts on our database/server.

The core issue is that we have many users and logins on our databases and we have huge doubt their respective needs. The root cause is identified: sometimes, for a short period of time, we’re making exceptions to our own rules and let a few other friend projects access to our DEV database. On some other cases, we’re connecting our own solution in DEV environnement to the QA environnement of another solution. Why … planning, data quality issue, … we’ve valid reasons to do it … but these exceptions should be removed as soon as possible. And you know what? People forget. Nowadays, on our largest solution, we have 20 users but only 7 of them are expected and documented … other should be removed. But before executing this cleanup, we’d like to be sure that these users are not effectively used by other solutions. If it’s the case, we’ll need to update first the configuration of the corresponding solution.

To know if a user is effectively in use on a database, you need to capture the attempts of connexion (success and failure). Technically, you’ve several ways to implement this in SQL Server but I decided to use the audit feature to accomplish this task.


The first step is always to create an audit on the server or database (here, I’d like to track the whole server).

create server audit
    [Audit Login Informations]
to file
(   FILEPATH = N'N:\Temp\Trace\'
    , MAXSIZE = 1024 MB
    , MAX_FILES = 10
(   QUEUE_DELAY = 1000
    , AUDIT_GUID = '33e055a9-1924-47c3-9777-e8875a5bcd37'

This audit must be activated

alter server audit
    [Audit Login Informations]
with (state=on);

Then the specification of this audit must be described. In our case we’ll intercept five groups of events related to login attempts (successful or not) and to contained database or not.

create server audit specification
    [Audit Login Informations specification]
for server audit [Audit Login Informations]
with (state=on);

By experience this kind of script is probably more useful and less breakable, if you add a cleanup phase before creating your audit and its specification.

Let’s start by testing if the audit is already existing. The view sys.dm_server_audit_status will let you check if the audit is existing and if it’s running. If it’s the case then you need to stop it and drop it (and its specification).

if exists(
         name='Audit Login Informations'
    alter server audit specification
        [Audit Login Informations specification]
    with (state=off);

    drop server audit specification
        [Audit Login Informations specification];

    alter server audit
        [Audit Login Informations]
    with (state=off);

    drop server audit
        [Audit Login Informations];

That’s it! You can now query your audit to get all the connection attempts on your server by the means of this simple query:

declare  @LogFilePath varchar(255);
set @LogFilePath = N'N:\TEMP\Trace\';

declare  @AuditName varchar(255);
set @AuditName = N'Audit Login Informations';


set @LogFilePath=@LogFilePath
    + REPLACE(@AuditName, ' ', '%5')
    + '*.sqlaudit';

            convert(datetimeoffset, event_time)
            , datename(TzOffset, sysdatetimeoffset())
    sys.fn_get_audit_file (@LogFilePath,default,default) f
inner join
    sys.dm_audit_class_type_map m
    on m.class_type=f.class_type
order by
    event_time desc;
Illustration performed with the help of icons from the noun project: Database by Dmitry Mirolyubov, Sightview by Andy Ivandikov, Lock by Maxim Kulikov. Common creative licence.

Managing firewall rules for Azure databases with SSMS

When you create a new Azure database, you usually need to open the firewall to remotely administrate or query this database with SSMS. An option is to create rules from the Azure Portal. It’s surely a convenient way to do it when you create a database but I prefer to keep a minimum of tools and when the Azure portal is not open, I prefer to not have to open it just to define a few firewall rules.

Opening the firewall with SSMS is a kind of chicken and eggs problem: to connect to your database/server, you need to open the firewall. Hopefully, SSMS has a great suite of screens to call the underlying API of Azure Portal and open the firewall for the computer running SSMS.

Attempt to connect to your Azure database.


If the firewall is not already open for this IP, you’ll receive the following screen:


The first step is to identify you with your Azure subscription.

ssms-azure-03If you’ve created your account a long time ago, you could receive this screen … If your account is linked to professional resources (granted by your comany) the good choice is probably the first. If you’re developing from home with resources that you’ll pay by yourself, the correct choice is probably the second.


When you’re successfully identified, you can create a new rule. Open the firewall for your own IP or for a subnet.


Now, you can connect and query your database or the virtual master database (open the folder System Databases).


Firewall rules applicable at the whole server are effectively stored in the master database, you can list them by querying the view sys.firewall_rules.


At this moment, you see that I was a bit lazy and didn’t clean all these rules since a long time. If you want to remove some rules, you can use the stored procedure sp_delete_firewall_rule.


It’ll clean-up your list of firewal rules.ssms-azure-11


If you want you can create firewall rules at the database level by connecting to the database and using the sys.database_firewall_rules. The firewall rules at the database level are evaluated before these at the server level. To improve performance, server-level firewall rules are temporarily cached at the database level. To refresh the cache, see DBCC FLUSHAUTHCACHE

Web.Contents and 404 page not found in Power BI

The M language (empowering Power Query and Power BI) supports the try ... otherwise ... feature. It is handy to catch errors when processing some of the rows and handle these cases in alternative paths.

Unfortunately this feature is not intuitive when surrounding a call to the function Web.Contents(). Intuitively you could expect that an answer 404: page not found would start the otheriwse path. But it’s not the case, an error will throw!

To correctly manage this kind of error, you’ll need to add a second parameter to your Web.Contents call (after the url): [ManualStatusHandling={404}]). This parameter specifies to Web.Contents() that you don’t want to apply the default behavior of Power BI when receiving a 404 error: you’ll handle this case by yourself and not stop the load of the table.

The pragmatic option is probably to continue your transformation flow independently of the response received from the web server. Your next step will (probably) miserably fail and you’ll be able to add a try ... otherwise ... on this step to handle an error.

But, if you really want to do this cleanly, you’ll have to parse the metadata of the result of Web.Contents. The function Value.Metadata gives you this opportunity. In the metadata, you’ll find a Response.Status field where you’ll be able to check the value received: 404, 100, … Based on this value you can decide which path to follow with a simple “ìf … then … else“`

  Source = Web.Content(
    , [ManualStatusHandling={404}])
  ResponseCode = Value.Metadata(Source)[Response.Status],
  NextStep = if ResponseCode=404 then ... else ...

How to connect to PostgreSQL with NBi?

I often receive questions such as “Does NBi work with PostgreSQL?” or “Is it possible to use another database than SQL Server?”. The answer is definitively: YES! NBi is working with all databases supporting OleDB or ODBC connections.

Yesterday, I edited a blog post for the connection to MySQL, today we’ll see how to connect to PostgreSQL from NBi by using both the ODBC and the OleDB connectors. At the opposite of MySQL, PostgreSQL has at least one ODBC driver and at least one OleDB provider still maintained (and even actively developed).

I’ll start by the ODBC driver for PostgreSQL. You can find the binaries on the official PostgreSQL website. Download them and install this driver. You can check that the ODBC driver has been correctly installed in the “Program and Features” panel:



Another great way to check that the ODBC drivers are installed is to user PowerShell and the cmdlet


This method returns the following list where you should find something related to PostgreSQL.


This method will also give you the exact name of the driver, something where my google-fu usually miserably fails. In this case I’ve two drivers named PostgreSQL ANSI(x64) and PostgreSQL Unicode(x64). Be sure to use these exact names in your connection strings!

When these connectors are successfully installed, edit your test-suite and define the connection string to your PostgreSQL server as:

      "Driver={PostgreSQL ANSI(x64)};
     select 'Hellow world'

That’s it!

For the OleDB provider, I’ll use the commercial version provided by Intellisoft and available on this page. This product comes with a free trial. Once downloaded and installed, you can check that it has correctly been registered by running the following PowerShell cmdlet:


and it should contain the following result:


Unfortunately, this method doesn’t return the nickname of the provider that we must use in our connection string. In this specific case, this name is PNGP.1.

Because this OleDB provider is not pre-registered by NBi, we need to map it to the OleDb namespace as explained in the documentation of NBi. The config file must be updated with:

<nbi testSuite="PostgreSQL-TestSuite.nbits">
    <add id="PGNP.1" invariant-name="System.Data.OleDb"/>

Once it’s done, you can safely edit your connection string into your test-suite.

     Data Source=;
     Initial Catalog=postgres;
     User ID=CI-Build;
     select 'Hello World'

We could move this connection string to the settings (references or defaults) or to the config file.

You can now configure a connection to a PostgreSQL instance without any problem!

How to connect to MySQL with NBi?

I often receive questions such as “Does NBi work with MySQL/PostgreSQL?” or “Is it possible to use another database than SQL Server?”. The answer is definitively: YES! NBi is working with all databases supporting OleDB or ODBC connections.

Today, I’ll specifically answer the question about MySQL, tomorrow I’ll edit another blog post about PostgreSQL. For MySQL, I’ll connect from NBi by using an ODBC connector. Indeed, MySQL has no support for an OleDB connector. To be 100% transparent, some old versions of OleDB connectors exist but it doesn’t sound to have any active development on these projects.

Start by downloading the latest version of the ODBC driver for MySQL from the MySQL website. Then install it. You can check that the ODBC driver has been correctly installed in the “Program and Features” panel:


Another great way to check that the ODBC drivers are installed is to user PowerShell and the cmdlet


This method returns the following list where you should find something related to MySQL.


Do you need the 32-bits(x86) or 64-bits(x64) drivers? It depends of the application using the ODBC driver … both need to match. When using the nunit-console or the GUI of NUnit, you’ll need the 64-bits version. But if you’re running your tests from Visual Studio then you’ll need the 32-bits version. You can install both on the same computer but it’s apparently recommended to install first the 64-bits version and then the 32-bits. Haven’t checked this personally.

When the connector is successfully installed, edit your test-suite and define the connection string to your MySQL server as:

      "Driver={MySQL ODBC 5.3 UNICODE Driver};
     select count(*) from adventureworks.address

We could also use this connection string in our settings (defaults or references) or in our config file.

Now, you can use your MySQL instance exactly the same way you’d use a SQL Server instance.

Handling enum values in fact tables with Power BI

In a previous blog post, I wrote about the analysis of survey’s results and specifically how to build a hierarchy when this notion was not really transparent into the source data. Then, in a second post, I explained how I skipped to create some temporary tables and used transpose and unpivot.

I’ll now continue my journey and explain how to build the tables to manage the results. Some of the challenge that I’ll have to face are:

  • Answers are an enum where I’m expecting some values such as NONE, LARGELLY, FULLY, … [Green area]
  • NOT APPLICABLE should be treated as a no-answer (that is also possible).
  • I DON’T KNOW, should be considered as an answer but not taking into account when I create an aggregation (average) of the results received.

To start, I’ll create two tables and not one. In the first I’ll create a new dimension with the list of possible answers to the questions. I can do this manually with the “Enter Data” feature.


For the second, I’ll restart from the previously created table Result-Staging. I’ll just merge this table with the newly manually created Value table.

    Source = #"Results-Staging",
    #"Merged Queries" = Table.NestedJoin(#"Source",{"Answer"},Value,{"Label"},"NewColumn",JoinKind.Inner),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Value"}, {"Value"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded NewColumn",{"Answer"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns1", each ([Value] <> null))
    #"Filtered Rows1"

Due to the usage of an Inner Join, all the questions where the answer was not one of the predefined values of the enumeration are discarded. It’s a good new because it’ll remove the yellow area, the red area but also all the NOT APPLICABLE answers!


Our model looks like:


We still have to implement a few measures. The count of answers with a given label for a category or a Respondent can be easily acgieved with the straightforward DAX formulae:

CountOfAnswer = COUNT(Results[Value])

We’ve previously decided that a DON’T KNOW, shouldn’t influence our average of the answers. To apply this decision, we just need to filter the table Result and remove all the values equal to 0 (Enum value of DON’T KNOW). Then we calculate the average and subtract 1 to get a value between 0 and 4. Coooool, except that if we’ve no value non equal to 0, it will return -1 … not what we’re expecting. We’ll need to validate that the average is not null before subtracting 1.

AverageOfResponse =
   VAR NonZeroValues =
           FILTER('Value', 'Value'[Value]<>0)
       IF(ISBLANK(NonZeroValues), BLANK(), NonZeroValues-1)

That’s it, now I can use the DON’T KNOW values in a count of answers received but they don’t influence my average:



Merge on a filtered set of rows without creating a new table (Power BI).

In a previous blog post, I wrote about the analysis of survey’s results and explictely how to build a hierarchy when this notion was not really transparent into the source data.

I’ll now continue my journey and explain how to build the table/dimension “respondent”. To summarize the challenge that I’ll face:

  • Each respondent means a new column (and not a new row), each additional information for the respondent add a new row (and not a new column) [Orange area]
  • Some information are available in the list of answers given by the respondent and not in the information on the top [yellow area]
  • For maintenance reasons, I’ll try avoid to create temporary tables that will only be used as a subset of another table

The first step is really easy, we’ll just keep the first rows by removing all the rows where the column “A” has a value. when done, we can remove this column that will only contain null values.

   Source = Source,
   #"Filtered Rows" =
          each ([Column1] = null)
   #"Removed Columns" =
           #"Filtered Rows",

The next step is to transform each row into a column and vice versa. Usually people with a SQL background try to apply a pivot or unpivot to achieve this task. There is an easiest way with the M language … just use the function Transpose

#"Transposed Table" = Table.Transpose(#"Removed Columns"),

You’ll obtain the following result:


It’s now to promote the first row as header.

#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table"),

Now that we already have a few information about our respondent, we’ll need to add the block of yellow information to this table.

To achieve this, I’ll first create a new table named Result-Staging and containing the gray area of my excel sheet. Let’s start by removing the column “B”, promoting the first row as header and filter rows to remove them within the orange area (column “A” is null).

    Source = Source,
    #"Removed Columns" = Table.RemoveColumns(Source,{"Column2"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns"),
    #"Filtered Rows" = Table.SelectRows(
        #"Promoted Headers",
        each ([Column1] <> null)

Then, I’ll try to change this format to have one column with the QuestionId, another with the Respondent and finally a third with the response. To transform the current data table into this format I’ll need to unpivot all columns except Attribute and Value.

    #"Unpivoted Other Columns" =
            #"Filtered Rows",
    #"Renamed Columns" =
              #"Unpivoted Other Columns",
                    {"Column1", "QuestionId"},
                    {"Attribute", "Interviewee"},
                    {"Value", "Answer"}
    #"Renamed Columns"

In this table, only the results with a QuestionId equal to 111 really interest me for a merge with the existing table Respondent. If you’re familiar with the UI of Power BI Desktop then you’ll probably think to create a new table referencing this one then filter on QuestionId equals 111 and finally merge. It’ll work but applying this strategy could result in many “temporary” tables. A lot of these small tables used only for a few steps before merging with other tables tend to be a nightmare for maintenance. You can use the “Advanced formula editor” to not display this kind of temporary tables and embed them in your main table.

On the code of the Respondenttable, add a step to filter the content of the table Result-Staging.

#"Age table" =
      each [QuestionId] = 111

If you take a look to the code the function #"Results-Staging" doesn’t exist in the code associated to table Respondent. In fact it’s the end-result of the function/table Results-Staging !

Now that we’ve our filtered set of rows corresponding to the ages of the respondents, I can join it with the content of the table Respondent.

#"Merged Queries" =
       #"Promoted Headers",
       #"Age table",

With a bit more of expanding and renaming, I can finally get my table Respondent.


In the next blog post, I’ll show how to manage the table of the results.