In this tutorial, you’ll learn how to create and utilize structured list values. You’ll also learn how to maximize them using different contexts and expressions to get the desired data and outcome. Shorthand methods will also be discussed to help create lists easily.
Creating Structured List Values
The list is an ordered sequence of values; its order is considered when you compare lists. The list initializer is a set of curly brackets. If you input curly brackets ({ }) in the formula bar and press Enter, you’ll create an empty list. You can also see the icon associated with lists beside the query name in the Query page.
To create a list with values, first create a new query by right-clicking on the space inside the Query pane. Next, click New Query and select Blank Query.
Then, input your values in the formula bar. If you have multiple values, separate each of them using a comma. The values in the list don’t need to be of the same type so they can either be a primitive or structured type. This is a list with a text, list, and record values:
Using Double Dots For Sequential Numbers And Characters
If you need to create a list of sequential numbers, there’s a shorthand for that. First, input the initializer followed by the first number. Then, input two dots and then the last number.
You can see that it created a list of sequential numbers from 1 to 5. The numbers need to be incremental. So if you change the last number to -5, you’ll get an empty list because the starting value is greater than the ending value.
This method also works with characters.
You can see that it created a list of characters from the first value to the ending value in the code. But if you change the values the other way around, it will show an empty list. This is because unicode character values associated with the characters are not incremental.
This is a query to further illustrate this process.
If you input this in the advanced editor window and press Done, you’ll see the numeric values associated with the letters.
However, this method doesn’t work with dates. If you enter date values in the formula bar, you’ll get an error message.
Accessing An Item From A Structured List
There are other ways to create lists. You can use M functions that return lists or you can extract a single column from a table that will also return a list. But more commonly, lists are used as parameters in M functions. In these cases, it’s important that you know how to create a list so that you can provide that function with the correct parameter value.
To access an item in a list, you can refer to it by its zero-based position using the positional index operator. In this example, you can see that the index operator 0 pulled out the first value 1.
If you change the index operator from 0 to 2, you’ll get the last value 3.
So if you want to retrieve the fourth item from the list by changing the index operator to 3, you’ll get an error because there aren’t enough values in the list.
Adding a question mark to the position index operator changes the “not-found” behavior from returning an error to a null. This is also referred to as performing an optional item selection.
Combining And Equating Lists
You can use the combination and equation operators with lists. Lists can be combined using the combination operator, ampersand (&).
The first list contains the numbers 1 to 5 and the second list contains 2 and 3. You can see that the lists are combined into a single list.
When comparing lists, the order is considered. In this example, you can see that the code yielded FALSE because they are not equal to each other.
Conclusion
Structured list values are common in Power BI data reports because they show data and information in a list form. They help organize and categorize data to make reports look compelling. If you want to list certain texts, numbers, or even values, using lists is highly recommended.
Melissa
***** Related Links *****
How To Compare Two Lists Of Calculated Data Virtually – An Advanced DAX Technique
Extract Values From Records And Lists In Power Query
Create Automated Lists Based On Ranking Calculations In Power BI
***** Related Course Modules *****
Power Query M Masterclass Part One
Power Query / M Masterclass #2 of 2
Mastering Report Development – Start To Finish
***** Related Support Forum Posts *****
Lookup From Two Lists
List Of Columns In Data Flow Table
Extract Data From List In Query Editor
For more lists queries to review see here….
