Quick tip: Make a list of numbers (or dates) in Power Query easily

Just a quick tip to revive the blog from a month long silence. I am alive and kicking. I have been occupied with a quest to rescue princess & maidens on video game console. Recently we bought SNES classic console from Nintendo and I have been playing Legend of Zelda – a link to past regularly. As it is almost summer, I am also enjoying the beautiful outdoors in Wellington. All this means, little time for blogging. I will try to post a few more times before the end of year.

Make a list of numbers in a jiffy with Power Query:

We know that in Excel, you can type a few numbers and use the fill handle to fill down (or up etc.) numbers as you want.

But what if you need some numbers in Power Query?

Simple, just type ={1..10} in the Power Query formula bar to get the numbers 1 thru 10. See this in action.

What formula bar? I don’t see any: If your Power Query is devoid of formula bar, just go to view ribbon and slash “Formula bar” option with your master sword. You will now acquire the magical power of formula bar. You can see and edit mysterious M language instructions now.

Oh wait, my numbering needs are a little crazy

Of course they are. You just need to unleash a bottled fairy on those numbers. Or take the easy route and use =List.Numbers() function. Go ahead, it won’t bite you.

Examples of List.Numbers() in Power Query:

• List.Numbers(1,10):  Same as {1..10}
• List.Numbers(1,10,2): You get numbers {1,3,5…,19}  ie first 10 odd numbers
• List.Numbers(0,10,2): Take a guess… what do you think this will do?
• List.Numbers(0,100,10): You get 100 multiples of 10, starting with 0, ending at 990

Can I get some dates?

You can also get raisins, dried apricots and cranberries, but only on the bulk-food aisle. If you just want dates, as in 13th of November 2018, then you can use List.Dates() function.

Examples of List.Dates():

Unlike simple numbers and text values, dates & durations need to be typecasted. We use #date, #duration to generate the required format first. Keep that in mind when reading below examples.

• List.Dates(#date(2018, 1, 1), 365, #duration(1, 0, 0, 0)): You get all 365 dates in year 2018, starting with 1 January 2018.
• List.Dates(#date(2018, 11, 13), 31, #duration(1, 0, 0, 0)): You get 31 days from 13th of November 2018.

Try with other numbers in duration to see what happens.

Last but not least – You get List, not Table

In all these cases, Power Query spits out a list. While it looks like a table, list is different and can ever have only one column. So if you want to do something with the list, you need to convert this to a table. This is a simple matter of spilling magic potion on the list using the List tools > Transform > “To Table” button.

Now that you have a table, you can add columns, sort or do other cool things easily.

How do you make a bunch of numbers in Power Query?

I use a variation of {1..10} or List.Dates on most of my Power BI files when I need to make up some data.

What about you? How do you like this tip? Share your Power Query wins in the comments.

I am off to D&D now – Dinner and dungeon time that is. The seven maidens can’t be saved with Power Query. Tada…

New to Power Query? Check out this great intro.