Please note javascript is required for full website functionality.


Power Query: Birthday Lists

15 November 2017

Welcome to our Power Query blog. Since I have reached the milestone of my 50th issue, I have covered a whole list of Power Query features. Talking of lists… (it’s an ellipsis, get it?)

I will start with a nice simple example – in a new query, I will create a list of consecutive numbers.

Curly brackets (or braces) { } indicate that a list is enclosed.  For whole numbers, I can define a range by giving my start and end points and separate them by an ellipsis of two dots .. to indicate that the intervening numbers should be included.  Therefore, I may specify a list from 1 to 10 by entering

            = {1..10}

in the formula bar. 

Numbers are not the only list I can create with this format; I can create a list of consecutive letters. Since letters are text, they must be surrounded by speech marks “ “.

Using ellipsis with characters is limited to single characters though, so although I can create this list

= {“0”..”9”}

I am not allowed to create this

= {“1”..”10”}

I have to specify the whole list instead

= {“1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”, “10”)

Therefore, lists with ellipses are more useful for numbers – what if I want to create a list of dates without typing them out?  In this case I can’t just type the number for today as 15/11/17 or even 151117, as this is not how Power Query will recognise it – however I can use the serial number of the date.  In order to get a date in serial number format, I can use the Number.From function.

Number.From(value as any, optional culture as nullable text) as nullable number

My value is #date and I need to give my date as year, month and day separated by commas. So, for today it would be:

= Number.From(#date(2017,11,15))

I create a list of the dates (in serial number format) until the end of November:

If I want the dates to look like dates, then I need more formatting options open to me. Having created my list, if I want to transform data or merge with other tables, then I need to convert my list to a table, which is easy to do, as the options for lists are automatically displayed when I create my list.

As I have created a very simple list I can take the defaults and create my table. In the ‘Transform’ tab, I can choose to convert my ‘Data Type’ to ‘Date’ (I could also do this from the ‘Home’ tab):

Thus, I have my list of dates in a recognisable format.

There are other ways to get a list of dates (without using the ellipsis). The List.Dates function allows more flexibility:

List.Dates(start as date, count as number, step as duration) as list

I am going to create my list again from a blank query, using this function.

=List.Dates(#date(2017,11,15), 16, #duration(1,0,0,0))

Now, since this function is more flexible, it looks more complicated.  I have specified my start date as before, then I need to specify how many list entries I want and finally, what the difference is between each entry.  Since I can also use this function for ‘datetimes’ the duration can be hours, minutes etc.  I choose to increment by one day at a time. 

That sounds complicated: if only there was some way to remind me what to enter for the List.Dates function…

Now this is useful. Power Query has recognised what I am trying to do when I enter List.Dates, and I can enter the parameters one at a time.

I enter my parameters (I enter 1 as the step or increment as I’m assuming one day is the default).

The advantage this way, is that my dates are created in a recognisable format so I can see they are all there.  There are some other interesting (well for me anyway) uses of the List functionality.  I can list numbers, without using an ellipsis, and by specifying the interval:

List.Numbers(start as number, count as number, optional increment as nullable number) as { Number }  

This function is very flexible too, so it looks complicated. I create a list with the following formula

= List.Numbers(3, 16, 3)

So, I am expecting my list to start at three, have 16 entries and have an interval or increment of three.  As before, I don’t have to remember how to use the parameters, I can just enter List.Numbers:

So now I am expecting to see my three times table:

I don’t have to stick to whole numbers; if I choose different parameters I can have a list of decimals:

This time I expect to see my 0.3 times table.

If for some reason I want to see my list in reverse, I can do this with the…

List.Reverse(list as list) as list

The list can be created using any of the methods used so far, so I can create 

= List.Reverse({1..15})

Or I can reverse my three times table:

Finally (for now), I can create a repeating list of numbers, with List.Repeat.

List.Repeat(list as list, count as number) as list 

This repeats any list as many times as I want.  Therefore, I can repeat the first three entries of my three times table.

= List.Repeat(List.Numbers(3,3,3),3)

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

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here.