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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image1.png/f45e335f3c18914c39229e3cc5ad1c59.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image2.png/ac4001f54114a0cba2e3b70eb70f34ed.jpg)
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 “ “.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image3.png/d4a89590683148daedfcd5df16a48adc.jpg)
Using ellipsis with characters is limited to single characters though, so although I can create this list
= {“0”..”9”}
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image4.png/95297faab388ffd77dde892e51eab025.jpg)
I am not allowed to create this
= {“1”..”10”}
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image5.png/efaa3b7f73e3088e4504da71190489dd.jpg)
I have to specify the whole list instead
= {“1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”, “10”)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image6.png/4e4aa977724fc69f74fc1ffa7f8523d2.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image7.png/de38adddb1cfcf48d4c8071379c7ae40.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image8.png/31f4be8f766a70afe3ed629813e6e9c8.jpg)
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):
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image9.png/70037901d4d0ce791c6224bc799feaae.jpg)
Thus, I have my list of dates in a recognisable format.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image10.png/93a264ede9793207b7912af49d5e4cf4.jpg)
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…
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image11.png/d928203e7ca4bc36022ef5355dc3ce29.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image12.png/7ef737105f18163d50d98344bbbe7bb1.jpg)
I enter my parameters (I enter 1 as the step or increment as I’m assuming one day is the default).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image13.png/bb80c799b6e09333260a82daf83afc7d.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image14.png/1bc407c713a96ac5eaa5734bb481a22a.jpg)
So now I am expecting to see my three times table:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image15.png/d3ce03948f2c8b964077ae41630d457c.jpg)
I don’t have to stick to whole numbers; if I choose different parameters I can have a list of decimals:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image16.png/82adfcda58d8fb4d8fea01b65620454c.jpg)
This time I expect to see my 0.3 times table.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image17.png/43d96cace89c0b7a3ed373a23fa979ea.jpg)
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})
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image18.png/46e35ed186d1160e5befc5a109318d79.jpg)
Or I can reverse my three times table:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image19.png/a65b369981d98b05a4ab11ffcc0d13e8.jpg)
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)
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2017/power-query/nov/pq-50-image20.png/29e4ffaf31124b9f92067bf5b3b2871e.jpg)
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.