When you’re writing your own M code you often find yourself needing to create a list containing a sequence of numbers or characters. Luckily the M language allows you to do this very easily when you are defining lists by using expressions in the format

{lowest_integer..highest_integer}

For example, imagine you want to create a list with all of the integers between 1 and 5. Instead of writing

{1, 2, 3, 4, 5}

You can write the following:

{1..5}

and it will return the same list:

You can also use this format in more complex list definitions, for example

{1..3, 5, 7..9}

Returns the list

{1, 2, 3, 5, 7, 8, 9}

When you’re using this technique you must always put the lowest integer first and the highest integer last; if you don’t do this you get an empty list. So, for example, the expression

{5..1}

Returns an empty list:

It’s also possible to use this technique to create lists of characters. For example, the expression:

{"a".."z"}

Returns a list containing all of the lowercase letters of the alphabet:

The first character in the expression has to have the lowest Unicode value and the second character has to have the highest Unicode value, and the sequence of characters returned is the list of all characters with Unicode values in that range. As a result, the expression

{"#".."%"}

Returns the list

{"#", "$", "%"}

And the expression

{"a".."Z"}

Returns an empty list because the Unicode value of “a” is greater than the Unicode value of “Z”.

This technique doesn’t work for decimal numbers, dates or other data types. If you want a sequence of values of these types you need to use functions list List.Dates() and List.Numbers().

Lists are, of course, used all over the place in M. Building on my recent post on using #table() to create tables with no data source, here’s one last example of using lists containing sequences to create a simple table with three columns and three rows:

#table({"A".."C"}, {{1..3}, {7..9}, {11..13}})

Share this:

Published by Chris Webb

My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
View all posts by Chris Webb

Share this:

Like this:

LikeLoading...

9 thoughts on “Creating Sequences Of Integers And Characters In Power BI/Power Query Lists”

Hi Chris,
Again, many thanks for your blog; it’s saved me plenty of frustrating hours. I’m interested in maintaining lists of values and accessing these values via some indexing method. Some pseudo code to articulate:

ClosingPrices as list,
ClosingPrices (Index)….other arithmetic logic

1. Is it a best practice to do so?
2. If so, what is that best practice

Some clunky code to achieve this:
List.First( List.Range( ClosingPrices, Index, 1 ) )

Very Python-like Chris!

Hi Chris,

Again, many thanks for your blog; it’s saved me plenty of frustrating hours. I’m interested in maintaining lists of values and accessing these values via some indexing method. Some pseudo code to articulate:

ClosingPrices as list,

ClosingPrices (Index)….other arithmetic logic

1. Is it a best practice to do so?

2. If so, what is that best practice

Some clunky code to achieve this:

List.First( List.Range( ClosingPrices, Index, 1 ) )

I think this post should help: https://blog.crossjoin.co.uk/2015/09/15/referencing-individual-cell-values-from-tables-in-power-query/

can you reverse this? e.g i have a list (1,2,3,5,7,8,9,,12,13,14,15) can i convert this to (1-3, 5 ,7-9,12-15)

Yes! I thought this was an interesting problem so I blogged about it: https://blog.crossjoin.co.uk/2016/11/24/converting-lists-of-numbers-to-text-ranges-in-power-query/

can we alphabet for index A B C, AA, AB, AC.. and so on