Power Query Lists

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

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:

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}

Press ENTER and Power Query will return a list 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:

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:

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 1^st, 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:

Now our list is in a table:

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

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

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

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

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 1^st, 2017 (#date(2017,1,1)), increment 365 times, incrementing by 1 day (#duration(1,0,0,0))

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 Even Numbers

=List.Numbers(2, 20, 2)

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

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 Numbers in Reverse

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

Repeating List of Numbers

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

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

List Dates YTD

Let’s say we want to generate a list of dates from January 1^st, 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 1^st 2017 (#date(2017,1,1)) to find the number of days to increment:

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:

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:

And then filter to select the StartDate named range:

Click the double arrow on the Content column to expand the Table and expose the date (deselect ‘Use original column name as prefix’):

Set the data type for Column1 to ‘Date’ to remove the time component from the column:

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

Give the query a name; ‘StartDate’:

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:

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:

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:

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:

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.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.