• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Power Query Lists

You are here: Home / Power Query / Power Query Lists
Power Query Lists
April 14, 2017 by Mynda Treacy

In Excel we often need to work with lists. List of numbers, letters, dates, countries, products, you get the idea.

With Power Query* we can generate lists of standard items like numbers, letters and dates quickly and easily.

We can use one of the many Power Query List Functions, or we can use a shortcut to create a list of consecutive numbers or letters. Letโ€™s look at some examples.

Warning: Power Query functions and formulas are case sensitive.

*Power Query is available for Excel 2010 onwards.

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Power Query Lists - Quick Links

  • Power Query List Consecutive Numbers
  • Power Query List Consecutive Letters
  • Power Query List Dates
  • Convert Lists to Tables
  • List Dates with List.Dates Function
  • List Even Numbers
  • List of Decimal Numbers
  • List Numbers in Reverse
  • Repeating List of Numbers
  • List Dates YTD
  • List Dates using Start and End Date Parameters from Named Ranges
  • Get Power Query
  • Learn More Power Query

Power Query List Consecutive Numbers

Letโ€™s start by creating a list of consecutive numbers. First we need to create a new blank query:

power query lists of consecutive numbers

Note: in Excel 2016 go to the Data tab > New Query > From Other Sources > Blank Query.

This opens the Query Editor and in the formula bar type:

={1..10}

open Query Editor

Press ENTER and Power Query will return a list of numbers from 1 through 10:

power query lists of numbers from 1 through 10

Notice how we surround our list arguments in curly braces, and the ellipsis points . . instructs Power Query to fill in the blanks.

Power Query List Consecutive Letters

We can also generate lists of letters like this:

power query lists of consecutive letters

Notice the A and Z are surrounded by double quotes, as they are text, just like we would in an Excel formula. And again, the list arguments are surrounded by curly braces.

Power Query List Dates

Dates are a little more complicated. Just like in Excel, Power Query recognises date serial numbers.

We can convert dates into their data serial number to more easily create a list of dates using the Number.From function, like so:

convert dates to data serial numbers

The list formula:

= { Number.From( #date(2017,1,1) ) . . Number.From( #date(2017,12,31) ) }

Is made up of the following elements:

#date(2017,1,1) is a literal date function that creates a data value from (year as a number, month as a number, day as a number).

The Number.From functions convert the dates into their date serial number equivalent, which is why the list starts at 42736, i.e. the date serial number for January 1st, 2017.

And again, the whole formula is wrapped in curly braces to return a list.

Convert Lists to Tables

The above examples are all in a list format. We can load lists (Home tab > Close & Load) into our worksheet in an Excel Table, or load it to the data model (Power Pivot where itโ€™s converted to a Table), but in their list form in Power Query theyโ€™re restricted to being a single column of data.

If we want to add columns or merge the list query with other query tables, thenย weโ€™ll want to convert the list to a Table.

Itโ€™s easy to do; with the list selected, the contextual List Tools: Transform tab is open. Click on the โ€˜To Tableโ€™ icon:

convert list to table 1

Now our list is in a table:

convert list to table 2

From here we can change the data type to Date. Select Column1 > Home tab or Transform tab > Data type: Date

change data type to date

Double click in the Column header and change the column name to something more useful:

change column name

Give your query a name. This will help you identify it and work with it later on:

change query name

Now you can Close & Load into a Table in a worksheet, or load it into the data model (Power Pivot):

Close & Load into a table

So, that is the basics of generating lists with Power Query. Letโ€™s look at some more advanced examples.

List Dates with List.Dates Function

Another way we can generate a list of dates is using the List.Dates function like so:

= List.Dates(#date(2017,1,1), 365, #duration(1,0,0,0))

The formula above lists the dates starting at January 1st, 2017 (#date(2017,1,1)), increment 365 times, incrementing by 1 day (#duration(1,0,0,0))

List.Dates function 1

Tip: if you type = List.Dates in the formula bar and press enter, youโ€™ll get a nice GUI that you can enter the parameters in, similar to the Excel Function wizard (click Invoke to enter the formula):

List.Dates function 2

List Even Numbers

=List.Numbers(2, 20, 2)

Returns a list of numbers starting at 2, 20 numbers long, which increment by 2.

list even numbers

List of Decimal Numbers

= List.Numbers( 1, 11, 0.1)

Returns a list of numbers starting at 1, 11 numbers long, which increment by 0.1.

list of decimal numbers

List Numbers in Reverse

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

list numbers in reverse

Repeating List of Numbers

= List.Repeat( {1..5}, 3 )

Generates a list of numbers from 1 to 5 and repeats it 3 times.

repeating list of numbers

List Dates YTD

Letโ€™s say we want to generate a list of dates from January 1st, 2017 up to today. We can then refresh the query each day and it will automatically add another date.

Using the List.Dates formula (below) that we looked at earlier:

= List.Dates(#date(2017,1,1), 365, #duration(1,0,0,0))

We replace the 365 with:

Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2017,1,1))

Like so:

= List.Dates(#date(2017,1,1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2017,1,1)), #duration(1,0,0,0))

The formula above uses our PC clock to find the date portion from the local time and subtracts that from January 1st 2017 (#date(2017,1,1)) to find the number of days to increment:

list dates YTD

If you prefer to list dates up to yesterday, then simply subtract 1 like so:

= List.Dates(#date(2017,1,1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2017,1,1))-1, #duration(1,0,0,0))

Tip: If youโ€™re going to use this date table in Power Pivot itโ€™s a good idea to generate dates up to the end of the calendar year so that DAX time intelligence functions like PARALLELPERIOD return the correct results when working with year comparisons.

List Dates using Start and End Date Parameters from Named Ranges

Letโ€™s say youโ€™ve got a start and end date in your workbook that you want to use to generate your list of dates. These start and end dates are in Named Ranges:

list dates using start date

We can create a blank query to get the named ranges: Power Query > From Other Sources > Blank Query.

Then with the Excel.CurrentWorkbook function we can get a list of the file contents:

Excel.CurrentWorkbook function

And then filter to select the StartDate named range:

filter StartDate

Click the double arrow on the Content column to expand the Table and expose the date (deselect โ€˜Use original column name as prefixโ€™):

expand the table

Set the data type for Column1 to โ€˜Dateโ€™ to remove the time component from the column:

set data type to Date

Right-click the cell containing the date > Drill Down:

Drill Down

Give the query a name; โ€˜StartDateโ€™:

name the query

We now have a date parameter for the StartDate that we can use in other queries.

Repeat for the EndDate named range so you have both StartDate and EndDate parameters in your queries list:

list dates using end date 1

Now create a new blank query: Power Query tab > From Other Sources > Blank Query. Here we can use the same Number.From function we used earlier (below):

= { Number.From( #date(2017,1,1) ) . . Number.From( #date(2017,12,31) ) }

And replace the literal #dates with the StartDate and EndDate parameters:

= {Number.From(StartDate)..Number.From(EndDate)}

This formula returns a list of date serial numbers based on the start and end dates in our named ranges:

list of date serial numbers

We can go ahead and convert the list to a table, change the data type to โ€˜Dateโ€™, rename the column and give the query a name:

list dates using end date 2

When your date parameters are in the worksheet we donโ€™t need to open or edit the queries to make changes to the list of dates. This makes it easy for users who are unfamiliar with Power Query.

Get Power Query

For Excel 2010 and 2013 users you can download Power Query here:

https://www.microsoft.com/en-us/download/details.aspx?id=39379

If you have Excel 2016 then you already have Power Query on the Data tab of the ribbon in the Get & Transform group:

Power Query Ribbon 2016

Learn More Power Query

Click here for more Power Query tutorials.

And if you want to get up to speed quickly please check out my Power Query course.

Print Friendly and PDF
Power Query Lists
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Power Query Posts

get started with power query

Get Started with Power Query

10x Productivity with Excel Power Query in 3 easy steps. Get data > Transform Data > Load Data = HOURS Saved!

Power Query if Statements incl. Nested ifs, if or, if and

How to write Power Query if statements, including nested if, โ€˜if orโ€™ and โ€˜if andโ€™, which are easier to write than their Excel counterparts.
power query variables

Power Query Variables 3 Ways

Power Query Variables enable you to create parameters that can be used repeatedly and theyโ€™re easily updated as theyโ€™re stored in one place.
delete empty rows and columns using power query

Remove Blank Rows and Columns from Tables in Power Query

Delete blank rows and columns from tables using Power Query. Even rows/columns with spaces, empty strings or non-printing whitespace
extracting data from lists and records in power query

Extracting Data from Nested Lists and Records in Power Query

Learn how to extract data from lists and records in Power Query, including examples where these data structures are nested inside each other.
combine files with different column names in power query

Combine Files With Different Column Names in Power Query

Learn how to load data into Power Query when the column names in your data don't match up. Sampe files to download.
power query keyboard shortcuts

Power Query Keyboard Shortcuts to Save Time

Time saving keyboard shortcuts for Power Query that work in both Excel and Power BI. Download the free Shortcuts eBook
remove text between delimiters power query

Remove Text Between Delimiters – Power Query

Remove all occurrences of text between delimiters. There's no in-built Power Query function to do this, but this code does.
power query advanced editor tips

Tips for Using The Power Query Advanced Editor

Tips for using the Power Query Advanced Editor in Excel and Power BI. Watch the video to see these tips in action
pivot unknown variable number of rows to columns

Pivot an Unknown Number of Rows into Columns

How do you pivot rows to columns when you don't know how many rows you're dealing with? It's not as easy as you may think.


Category: Power Query
Previous Post:PivotTable Distinct CountExcel PivotTable Distinct Count
Next Post:Excel Row and Column ShortcutsRow/Column Shortcuts

Reader Interactions

Comments

  1. Graham S

    June 26, 2019 at 10:39 am

    Thanks Lynda,
    Really appreciate the section on parameterised start and end dates. Screenshots are invaluable.
    Regards

    Reply
    • Mynda Treacy

      June 27, 2019 at 5:33 am

      Glad I could help ๐Ÿ™‚

      Reply
  2. Ibrahima

    July 5, 2018 at 4:40 am

    Thanks a lot Mynda;
    I have one question: how to increment by month, not by day? Sorry for my poor english

    Reply
    • Catalin Bombea

      July 7, 2018 at 3:32 pm

      Hi Ibrahima,
      The easiest way from user interface, without having to write M code, is to create the daily list, then extract in new columns the month number and Year. Select Month and Year columns and remove duplicates and that’s it, you have the list of months between 2 dates.
      It should look like this:

      let
      Source = List.Dates(#date(2017,1,1), 365, #duration(1,0,0,0)),
      #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #"Inserted Month" = Table.AddColumn(#"Converted to Table", "Month", each Date.Month([Column1]), Int64.Type),
      #"Inserted Year" = Table.AddColumn(#"Inserted Month", "Year", each Date.Year([Column1]), Int64.Type),
      #"Removed Duplicates" = Table.Distinct(#"Inserted Year", {"Month", "Year"})
      in
      #"Removed Duplicates"

      Reply
  3. Jeff Jones

    March 7, 2018 at 11:51 pm

    Thank you for the article. What is the fundamental difference between a list and a table in PowerQuery?

    Reply
    • Mynda Treacy

      March 8, 2018 at 3:19 pm

      Hi Jeff,

      A list contains a single column, whereas a Table can contain many columns. There are many functions for working with lists, which you can see here.

      Mynda

      Reply
  4. Dave

    October 3, 2017 at 10:20 pm

    Great bit about the date parameters from excel being passed into PQ as arguments in the list function.

    Reply
    • Mynda Treacy

      October 4, 2017 at 8:53 am

      Glad you found it useful, Dave ๐Ÿ™‚

      Reply
  5. DJ

    April 22, 2017 at 6:05 am

    how does power query compare to ACL

    Reply
    • Mynda Treacy

      April 22, 2017 at 11:36 am

      Hi DJ,

      I don’t know, sorry. I’ve not heard of ACL.

      Mynda

      Reply
  6. Bernice

    April 19, 2017 at 9:04 am

    Thank you. I think I may just have to take the Power Query course, after I finish my Excel VBA course and my Power BA course and my PowerPoint course……… too much too learn!

    Reply
    • Mynda Treacy

      April 19, 2017 at 9:06 am

      ๐Ÿ™‚ I know the feeling, Bernice. You’ll get there.

      Reply
  7. Abid

    April 18, 2017 at 3:55 am

    Thanks Mynda for useful tips. Still I feel these formulas are complex and not many of Excel users will even attempt.

    Reply
    • Mynda Treacy

      April 18, 2017 at 8:43 am

      Hi Abid,

      Everything is complex when you don’t know how ๐Ÿ˜‰ Do you remember your first Excel formula? I do. It was like learning a foreign language, but slowly it became more familiar and now I have the confidence to try new formulas.

      I hope my tutorial has encouraged you to give Power Query lists a go by starting with the easy ones first.

      Mynda

      Reply
  8. Dirk Kemp

    April 17, 2017 at 4:04 pm

    The Power Query does not appear to be supported for Excel 2016 for Mac. Is this correct?

    Reply
    • Mynda Treacy

      April 17, 2017 at 4:12 pm

      Correct, Dirk. Power Query is not available for the Mac, nor is Power Pivot. Sorry.

      Mynda

      Reply
  9. Joan

    April 17, 2017 at 12:39 pm

    Thanks for taking the time to describe these functions. I find that Microsoft’s Developer Network (MSDN) site (https://msdn.microsoft.com/en-us/library/mt296612.aspx) has a lot of great information but is far too intimidating to me.

    Reply
    • Mynda Treacy

      April 17, 2017 at 1:02 pm

      You’re welcome, Joan.

      I like MSDN too (I also linked to it in the blog post), but it is still a bit light on examples and layman’s terms.

      Mynda

      Reply
  10. Julian

    April 15, 2017 at 10:09 am

    It’s not a cheat sheet but also a white book.

    Reply
    • Mynda Treacy

      April 15, 2017 at 10:25 am

      Cheers, Julian. Glad you’ll find it useful.

      Mynda

      Reply
  11. Julian

    April 15, 2017 at 10:02 am

    It’s not only a cheat list but also a white book.

    Reply
  12. pmsocho

    April 15, 2017 at 6:19 am

    Great post!

    Reply
    • Mynda Treacy

      April 15, 2017 at 8:34 am

      Thanks, Pmsocho ๐Ÿ™‚

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

office scripts course

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.