Power Query: To Do List

Welcome to our Power Query blog. This week, I check the list(s).

Last week, I looked at several ways to create a record; this week, I will take a similar look at lists. The simplest way to create a list is to use the curly brackets ‘{‘, ‘}’:

In the above image, I have used the M code:

When I enter this step, I should get a list of names:

In this case, I have used text, but I can create lists of any data type.

In this image (above), I have created a list of lists using the M code:

I can also create lists by specifying the beginning and end of a series of values or by citing a contiguous list, viz.

Here, I have used the M code:

This creates a list of all letters from A to Z.  The ‘..’ indicates that Power Query should fill in the interim values.  Reversing the list is interesting…

I’ve cheated here slightly by using lowercase ‘a’, but the M code I have entered is:

This shows me the special characters in the order of their Unicode number; I can verify this by transforming my lists to a table, and then adding a column to show the Unicode value.

The M code I have used is:

Since the Unicode value of ‘Z’ is 90 and that of ‘a’ is 97, the interim values are shown.

Another way to create a list is from an M function.  There are a lot of functions that create lists, many of which begin with List.().  These can be found at https://docs.microsoft.com/en-us/powerquery-m/list-functions.

I will look at one of these functions:

The M code I have used is:

I have selected those values from my original list (Source) where the Unicode is more than 95, which gives me shorter list of values.  In this M code, the combination of each and ‘_’ indicates that the function Character.ToNumber() should be applied to each field in the column.

I can also create a list from a column in a table.  I can do this using the Power Query buttons:

On the Transform tab, if I select a column, I have the option to ‘Convert to List’:

The M code generated is:

which means that the Unicode column has been extracted from the table which existed in the ‘Added Custom’ step.  Therefore, I may use the syntax Table[‘Column Name’] to extract a column from a table and present it as a list. 

In the above image, I have used the following M code:

I have converted each of the columns to a list and created a list of my lists. 

This has allowed me to convert my list of lists to a table and then extract the values, giving me my original values in rows instead of columns. Lists allow me to manipulate my data in new ways.

Come back next time for more ways to use Power Query!