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

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

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.

make a list of numbers in Power Query

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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

3 Responses to “Quick tip: Make a list of numbers (or dates) in Power Query easily”

  1. Sandeep Kothari says:

    Dear Chandoo
    I enjoyed the dates & everything else in the article. Truly delicious!

  2. Maooz says:

    Awesome article thank you for sharing

  3. Lorna Eddy says:

    Brill. Great article.thanks

Leave a Reply