Written By Rick de Groot

Rick is the founder of BI Gorilla. He believes learning is one of life's greatest pleasures and shares his knowledge to help you improve your skills.

Have you ever found yourself struggling to create sequences in Power Query? This article breaks down how to create sequences for numbers, text, symbols, and dates in a way that’s easy to understand. Knowing how to create these sequences will help in simplifying your code and making it more efficient. Give it a read, and let me know what you think.

Table of contents

1. List of Numbers

The first section looks at creating a sequential list of numbers. We can create it in a variety of ways. Let’s first have a look at creating a sequence of incrementing numbers.

Power Query has a short expression that allows you to fill the numbers starting from the number on the left of it, and incrementing with steps of 1 until it reaches the value on the right of. That expression is .. written within curly brackets.

But if you want to go for an alternative, you can also use functions like List.Numbers, List.Transform or List.Generate.

// The following expressions return { 1, 2, 3, 4, 5, 6, 7 }
= { 1, 2, 3, 4, 5, 6, 7 }
= { 1 .. 7 }
= { 1 .. 5, 6, 7 }
= List.Numbers( 1, 7 )
= List.Generate( () => 1, each _ <= 7, each _ + 1 )
= List.Transform( { 49 .. 55 }, each Character.FromNumber ( _ )  )

If List.Generate is still relatively new, and if you want to learn more, you can have a look at this article that takes you step-by-step through some great examples.

We can also try to create a decreasing range of numbers. In this case, we can’t resort to the shorthand .. expression within a list. That construct does not support a decreasing list.

Rather, you can make use of List.Reverse to turn the number range around, or resort to List.Numbers or List.Generate.

// Decrements not supported, returns an empty list: { }
= { 7 .. 1 } 

// The following expressions return { 7, 6, 5, 4, 3, 2, 1 }
= { 7, 6, 5, 4, 3, 2, 1 }
= List.Reverse( { 1 .. 7 } )
= List.Numbers( 7, 7, -1 )
= List.Generate( () => 7, each _ >= 0, each _ - 1 )

2. List of Letters

Next to sequences of numbers, you can also generate sequences of letters. Power Query is smart enough to realize the order of the alphabet (and special characters).

// The following expressions return { "a", "b", "c", "d" }
= { "a", "b", "c", "d" }
= { "a" .. "d" }
= { Character.FromNumber( 97 ) .. Character.FromNumber( 100 ) }

// The following expressions return { "A", "B", "C", "D" }
= { "A", "B", "C", "D" }
= { "A" .. "D" }
= { Character.FromNumber( 65 ) .. Character.FromNumber( 68 ) }

And just like with numbers, you can only create incrementing sequences of letters. To reverse the sequence, you will have to resort to List.Reverse or List.Numbers in combination with List.Transform.

// The following expressions return { "D", "C", "B", "A" }
= { "D", "C", "B", "A" }
= List.Reverse( { "A", "B", "C", "D" } )

= List.Transform( 
     List.Numbers( 68, 4, -1 ),
     each Character.FromNumber( _ ) 
   )

3. List of Special Characters

As the previous section showed, Power Query easily generates sequences of special characters (and letters). Each text value represents a Unicode value, and this code is used ‘underwater’ to generate a sequence.

To find out what the Unicode number is for a character, you can use the Character.ToNumber function. To reverse it, you can use Character.FromNumber.

// The following expressions return { "!", """", "#", "$", "%", "&" }
= { "!", """", "#", "$", "%", "&" }
= { "!" .. "&" }
= { Character.FromNumber( 33 ) .. Character.FromNumber( 38 ) }

4. List of Dates

Another important area to create sequences in is when creating a date column. The date series is used as a foundation for creating a date table in your model. But also, in financial reporting and budgeting, you can make good use of these sequences. For example, imagine creating the ending date of each month in a year you’re budgeting. So, how can we create these sequences?

The first few examples are the easiest:

StartDate = #date(2023, 1, 1)
EndDate =   #date(2023, 1, 4)

// The following expressions return dates Jan 1, 2023 to Jan 4, 2023

 { #date( 2023, 1, 1),  
   #date( 2023, 1, 2), 
   #date( 2023, 1, 3), 
   #date( 2023, 1, 4) 
 }

= List.Dates( StartDate,  4, #duration(1, 0, 0, 0 ) )

= List.Dates( 
     StartDate,  
     Duration.Days( EndDate - StartDate ) + 1, 
     #duration(1, 0, 0, 0 ) 
   )

For some more complex transformations, you can make use of the List.Transform function. It works great for converting numbers to date.

= List.Transform( 
     List.Numbers( 
          Number.From( StartDate ),  
          Duration.Days( EndDate - StartDate ) + 1
     ),
     each Date.From( _ ) 
  )

= List.Transform( 
     { Number.From( StartDate ) .. Number.From( StartDate ) 
     }, 
     each Date.From( _ ) 
  )

Lastly, you can combine these steps in a single List.Generate statement too.

= List.Generate( 
     () => Number.From( StartDate ),   // initial value
     each _ <= Number.From( EndDate ), // run until EndDate
     each _ + 1,                       // increase 1 for each step
     each Date.From( _ )               // return date from values
  )

= List.Generate( 
     () => StartDate,           // initial value
     each _ <= EndDate,         // run until EndDate
     each Date.AddDays(_, 1 )   // increase 1 day for each step
  )

Conclusion

And there you have it! In conclusion, creating sequences in Power Query can be a great way to simplify your code and streamline your data processing. Whether you need to generate a series of numbers, text, symbols, or dates, Power Query makes it easy to do so using a few simple methods.

Happy Querying!

Share on:

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.