Creating Non-Consecutive Number Lists with Power Query: Two Methods

Understanding Power Query: Deep Dive into Lists

In the featured YouTube video by Curbal, we learn about creating lists of non consecutive numbers using Power Query. Power Query utilizes 'M' language that supports structured values including lists, records, and tables, each serving specific purposes.

 

The focus of the tutorial is on 'lists'. Two kinds of values are identified in 'M' language - primitive and structured values. Primitive values are standalone (e.g., “a”, 1, true) while structured values are composed of other values. Lists, as one kind of structured values, are an ordered sequence of values, defined using opening and closing curly brackets. There is no limit to the list size.

The primary need for using lists arises because many functions in 'M' either expect lists as parameters or return lists as return values. Lists, therefore, operate as intermediate products for other queries.

• Ways to create lists in 'M' language:
  • Using the list initialization syntax with curly braces
  • Using native M functions, that create lists such as List.Dates(), List.Numbers(), List.Random(), and more
  • Referring to a column/ field in a table using the syntax Table[ColumnName]

There are operators available for lists along with equivalent functions, such as "=", "<>", and "&" used for comparing and combining lists. Certain native M functions like LIST.FIRST(), LIST.LAST(), LIST.LASTN(), LIST.RANGE(), LIST.SELECT() can be invoked to access specific elements within a list.

The video explains comprehensively the use of lists in transforming data models via Power Query, demonstrating the creation, manipulation and use of lists as input for functions such as Table.RenameColumns(). Further, list creation functions such as *.ToList() are explained. The video wraps up by reinforcing the indispensable role of lists in mastering the 'M' language.

Further Understanding

In Power Query, lists play a crucial role. They are ordered sequences of values that can adopt any size and can feature any value type. Lists play a colossal role in managing and manipulating large data sets, aside from being extensively used in functions for various operations. The better one's grasp of the usage and structuring of lists, the easier it is to master data transformations in Power Query.

Learn about Power Quey - Create lists of non consecutive numbers | 2 ways

 

This video, titled 'Power Query - Create lists of non consecutive numbers | 2 ways', provides a deep dive into the topic of lists in M language, used in Power Query. Particularly, it discusses the ways to create a list of non-consecutive numbers. The M language has structured values like lists, records, and tables, each serving specific purposes. The video serves as an introduction to lists, specifically in the M language.

• Primitive values: These are basic values that are not constructed out of other values. Examples include: 'a', 1, and true.
• Structured values: These are values composed of other values, both primitive and structured. A list is a structured value described as 'an ordered sequence of values'. It is introduced with an opening curly bracket '{' and ends with a closing curly bracket '}'.

Lists in M language are usually used as intermediate products for other queries. There are many native functions in M that provide useful functionality and expect lists as one or more of their parameters. Furthermore, many functions in M return lists as return values. This emphasizes the importance of understanding how to create and use lists.

The video breaks down the process of creating lists in M into three broad methods:

1. Using the list initialization syntax with curly braces

For those interested in getting a practical grasp of the topic, downloading the M code provided in the screenshots as a txt file is recommended. Users may have to switch browsers to Edge if required to input a OneDrive password during the download.

More links on about Power Quey - Create lists of non consecutive numbers | 2 ways

List.Generate in Power Query: Tutorial with Easy Examples

2. Generating Series with List.Generate. 2.1. Consecutive Numbers. Some easy ways to generate a consecutive list of numbers with List.Generate are: = List.

Lists in Power Query M / List Functions (200+ Examples)

There is an easy way to generate a series of consecutive numbers. You can ... For example, one way to generate a list of incremental numbers is by using the List.

Power Query Lists

Apr 14, 2017 — 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 ...

Lists in Power Query - how, when and why | THE SELF ...

... Power Query, Power BI. Contiguous numbers must not be indefinitely large. However, this does not mean that the list itself may not have more items, because as ...

List.Numbers - PowerQuery M

Jul 19, 2023 — Returns a list of numbers given an initial value, count, and optional increment value. The default increment value is 1.

Create list of non-consecutive dates using M...

Hi again, Next problem: how to create a list of non-consecutive dates in Power Query. ... In other words, the earliest and latest dates needs to be identified ...

Make column numbering not consecutive over the who...

Jul 18, 2022 — Make column numbering not consecutive over the whole dataset but just for equal column names.

Counting blocks of consecutive numbers -is it possible?

I have tried for 2 days straight and I'm not getting anywhere. I am trying to get the table like the one on the right (below) from the table on the left. borra.

Power Query - Subtract non-consecutive rows and create a ...

Sep 24, 2021 — This seems to work in PowerQuery. Next time please paste machine readable numbers. Unpivot. I assume that when Region=BU, thats the total.

Keywords

Microsoft expert guide, M language tutorials, Power Query list creation, Microsoft Power Query tips, Structured values in M.