List functions in Power Query are often used for data transformations. You commonly find lists of numbers, letters, times, dates etc. And these lists are useful by themselves, yet combining them with other functions really makes them stand out.
Lists allow you to take on advanced data challenges where you apply logic on lists. Yet you can start using them at any stage in your journey. To become an expert in the Power Query M-language mastering lists is essential.
This post is the complete guide that introduces you to lists. It shows you the characteristics of a list, how to use them and access values. More importantly, you will find practical m language examples that you can adjust to your needs. This is a long post, so buckle your seat belts!
Table of contents
1. What are Lists?
A list is a series of values. They are required to make use of the most powerful Power Query functions. You can visualize a list as a single column of data, whereas a table is a combination of many columns of data.
To create a list manually you write comma separated values and surround them by curly brackets { }. The curly brackets indicate the start and end of a list. The following list contains three values, and 8 is the second value.
= { 4, 8, 12 }
Just like a Record and Table a List is a so called structured data value. The list can contain any M value, including a list itself. It is possible to have an empty list, a list with text or numbers, but you can also mix different data types together.
= {} // Returns an empty list
= { 1, 2, 3 } // A sequence of consecutive numbers
= { "A", "B", "C" } // A list of text values
= { "Rick", "De,Groot" } // Text values in a list can have comma's
= { 1, "ABC", true } // List with a number, text and logical
So far the values in the list were primitive values. Yet Power Query lists can also contain more advanced data types. It is perfectly valid to mix numbers, letters, records, table objects and list objects within a single list. Meaning that an item in a list can also be a list, creating a list with nested lists.
= { { 1, 2 }, { 8, 9 } } // Returns a list containing two lists
= { [ Name = "Bob", Age = 29], [ Name = "Ava", Age = 44 ] }
// Returns a list containing two records
= #table( {"a"}, { { 1 }, { 2 } } ) // Creates table with 1 column 2 rows
= { #table( {"a"}, { { 1 }, { 2 } } ), // creates a list
#table( {"a"}, { { 1 }, { 2 } } ) } // containing 2 table objects
2. Generate Lists
The Power Query M-language supports different ways to create lists. This chapter shows you how to leverage constructs and list functions to create lists from scratch.
2.1. Consecutive Numbers
There is an easy way to generate a series of consecutive numbers. You can create a list by using curly brackets and then input a starting value and ending value, while separating the values by the two dots operator (..). This generates a list between two numbers.
= { 1 .. 6 } // Equals { 1, 2, 3, 4, 5, 6 }
= { 1..3, 7..9 } // Equals { 1, 2, 3, 7, 8, 9 }
= { -2 .. 3 } // Equals { -2, -1, 0, 1, 2, 3 }
The before examples create lists of numbers but only for increasing sequences. Trying this with a decreasing sequence results in an empty list.
To generate a decreasing sequence you can use several other list functions. For example, one way to generate a list of incremental numbers is by using the List.Numbers function. This function returns a list of numbers starting from a value and by default adds increments of 1. You can adjust this increment to get a list of decimal numbers, or and even negative numbers.
= List.Numbers( 1, 5 ) // Equals { 1, 2, 3, 4, 5 }
= List.Numbers( 1, 5, 1 ) // Equals { 1, 2, 3, 4, 5 }
= List.Numbers( 1, 5, -1 ) // Equals { 1, 0, -1, -2, -3, -4 }
= List.Numbers( 1, 5, 0.1 ) // Equals { 1, 1.1, 1.2, 1.3, 1.4, 1.5 }
Other ways to generate a decreasing sequence are by using the List.Reverse and List.Generate functions. The List.Reverse function takes an increasing series and reverses it. Whereas the List.Generate function applies a function for an indicated amount of steps.
= { 6 .. 1 } // Equals {}, does not work
= List.Reverse( { 1 .. 6 } ) // Equals { 6, 5, 4, 3, 2, 1 }
= List.Generate( ( ) => 6, each _ >= 1, each _ - 1 )
// Equals { 6, 5, 4, 3, 2, 1 }
Power Query can generate any consecutive list as long as it fits within a 32 bit integer value. This means you can only create a continuous list that is smaller than 2^{32} or 2^32. The result of this is 2.147.483.648.
= { 1.. 2147483647 } // starting from 1 this is the longest list
// of Numbers Power Query can create
Any longer continuous list results in the error: “Expression.Error: The number is out of range of a 32 bit integer value.”
2.2. Consecutive Letters
In a similar way, you can create a sequence of continuous letters. The only difference is that you surround each text value with quotations.
= { "a", "b", "c", "d" } // List from a to d
= { "a" .. "d" } // Similar list as above
= { "A", "B", "C", "D" } // List with Capitals A to D
= { "A" .. "D" } // Similar list as above
2.3. Consecutive Dates
There are several list functions you can use to generate a list of dates. The most obvious function to use is the List.Dates function. The List.Dates function generates a list that begins with a starting date and increments with a specified value.
= List.Dates( // Generates list of 5 dates
#date( 2021, 10, 15 ), // Starting from Oct 15th 2021
5, // Adding 5 increments
#duration( 1, 0, 0, 0 ) // Of a single day
)
Alternatively you can generate a list of numbers that represent your date range. You can then turn those numbers into a continuous list of dates by transforming the data type. The following examples use the parameters StartDate and EndDate to create a list of dates between two dates:
= { Number.From( StartDate ) .. Number.From( EndDate ) }
= List.Numbers(
Number.From( StartDate ),
Duration.Days( EndDate - StartDate ) + 1
)
= List.Generate(
() => Number.From( StartDate ),
each _ <= Number.From( EndDate ),
each _ + 1
)
After you enter the before code, you can change the data type to date. This results in a list of consecutive days. You can find a more elaborate explanation on the previous examples in this article on creating the ultimate date table.
2.4. Special Characters
A special way to create a list is by using characters. Power Query allows you to create a consecutive list of special characters using a similar syntax as before. The bigger question is, how do they decide the right order?
In reality, Power Query treats all characters the same. Under the hood it uses unicode. To make it easy for you as user, you can simply put in the character you wish.
= { "!", """", "#", "$", "%", "&" } // Special characters
= { "!" .. "&" } // Similar list
If instead you want to reference Unicode values, you can make use of the Character.FromNumber function. This function translates the Unicode to the corresponding special character. Since most people do not know these by heart, the regular characters do make your life easier. If you want to retrieve the corresponding unicode value of a character you can find it by using Character.ToNumber.
= { Character.FromNumber( 33 ) .. Character.FromNumber( 38 ) }
// Generates the characters ! to &
= { Character.FromNumber( 48 ) .. Character.FromNumber( 57 ) }
// Generates the numbers 1 to 10
= { Character.FromNumber( 65 ) .. Character.FromNumber( 90 ) }
// Generates the letters A to Z
= { Character.FromNumber( 97 ) .. Character.FromNumber( 122 ) }
// Generates the letters a to z
2.5. Custom Series
You can find different list functions to generate lists with custom series. For example, if you want to show numbers that increase with a step of 2 you can try use List.Alternate.
The List.Alternate function takes the original list as input, needs a count to specify how many steps to skip. Optionally you can add an interval and offset. The interval shows the amount of values to return before skipping values and the offset tells how many values to pass before skipping values.
= List.Alternate(
list,
count, // number of values to skip each time
repeatInterval, // num values added between skipped values
offset // offset before skipping values
)
= List.Alternate( { 1..10 }, 0 ) // Equals { 1..10 } skip no value
= List.Alternate( { 1..10 }, 3 ) // Equals { 4..10 } skip 3 values
= List.Alternate( { 1..10 }, 0, 1 ) // Equals { 1..10 }
= List.Alternate( { 1..10 }, 1, 1 ) // Equals { 2, 4, 6, 8, 10 }
= List.Alternate( { 1..12 }, 2, 1 ) // Equals { 3, 6, 9, 12 }
= List.Alternate( { 1..8 }, 1, 2 ) // Equals { 2, 3, 5, 6, 8 }
= List.Alternate( { 1..12 }, 3, 3 ) // Equals { 4, 5, 6, 10, 11, 12 }
// skip 3 values before step
// add 3 values each step
= List.Alternate( { 1..10 }, 0, 1, 0 ) // Equals { 1..10 }
= List.Alternate( { 1..10 }, 1, 1, 0 ) // Equals { 2, 4, 6, 8, 10 }
= List.Alternate( { 1..10 }, 1, 1, 1 ) // Equals { 1, 3, 5, 7, 9 }
// skip 1 value before step
// add 1 value each step
// skip values at offset 1
A more advanced way of generating custom lists is by using the List.Generate function. You can build a series using logic. As long as the given condition is true, the serie values increment.
= List.Generate(
initial value, // the starting value
condition as function, // as long as the condition is true
next as function, // increment the value
optional selector
)
= List.Generate( () => 1, each _ <= 6, each _ + 1 )
// Equals { 1, 2, 3, 4, 5, 6 }
= List.Generate( () => 2, each _ <= 12, each _ + 2 )
// Equals { 2, 4, 6, 8, 10, 12 }
= List.Generate( () => 1, each _ <= 12, each _ + 2 )
// Equals { 1, 3, 5, 7, 9, 11 }
The List.Generate function is one of the most powerful ones but is a tricky to master. To get a better understand you can read my in-depth article on the List.Generate function in Power Query or work out how to use it with API-calls.
In a similar fashion you can use List.Accumulate to generate a series of values.
= List.Accumulate( // List.Accumulate,
list as list, // by using the items in this list,
seed as any, // transforms the seed (starting) value
accumulator as function // with this function
) as any
= List.Accumulate(
{0 .. 12}, // the list used as input
"", // the starting value
( state, current ) =>
if state = "" then
{ current }
else if Number.IsEven( current ) then
List.Combine( { state & { current } } )
else
state
) // Returns a list with only even numbers. { 0, 2, 4, 6, 8, 10, 12 }
= List.Accumulate(
{0 .. 12}, // the list used as input
"", // the starting value
( state, current ) =>
if state = "" then
{ current }
else if Number.IsOdd(current) then
List.Combine( { state & { current } } )
else
state
) // Returns a list with only odd numbers. { 1, 3, 5, 7, 9, 11 }
In this example we generated a list of values, but you can also use List.Accumulate to return a scalar value, table, record or a combination of these. Further your reading with this article full of examples with List.Accumulate or create a fast running total with it.
When you want to repeat a list of values you can make use of the List.Repeat function. It takes a list as input and duplicates it for a given amount of times.
= List.Repeat( { 1, 2, 3 }, 3 ) // { 1, 2, 3, 1, 2, 3, 1, 2, 3 }
= List.Repeat( { "a", "b" }, 3 ) // { "a", "b", "a", "b", "a", "b" }
To create a list of random numbers, Power Query has the function List.Random. The List.Random function requires one argument with the number of random values to generate. It has an optional second argument which is the seed value. If you enter the seed value with a number, each call to the function results in the same list of random numbers.
= List.Random( 3 ) // { 0.982217873, 0.744085241, 0.281192752 }
// On refresh above list changes
= List.Random( 3, 2 ) // { 0.771093898, 0.404162595, 0.165998677 }
// On refresh above list remains the same
When you want to split your list into a list of lists you can use the List.Split function. The List.Split function takes a list as input. Then in the second argument you can indicate the size of each of the lists. When splitting into lists of two it looks similar to the List.Zip function, yet the order of the values is different.
= List.Split( { 1..8 }, 2) // { { 1,2 }, { 3,4 }, { 5, 6 }, { 7, 8 } }
= List.Split( { 1..8 }, 3) // { { 1, 2, 3 }, { 4, 5, 6 }, { 7, 8 } }
= List.Split( { 1..8 }, 4) // { { 1, 2, 3, 4 }, { 5, 6, 7, 8 } }
= List.Split( { "a".."d" }, 2 ) // Equals { { "a", "b" }, { "c", "d" } }
There are several functions that require you to generate a list containing one or more lists of pairs. These lists of pairs contain the old value and the new value to replace. For example, you find this in the list functions Table.TransformColumnTypes, Table.TransformColumns, TransformColumnNames and Table.RenameColumns. You can use these to rename columns in bulk.
It would be arduous to write these combinations by hand. Instead Power Query knows List.Zip. The List.Zip function allows you to combine different lists, returning a list of lists. The values in each list have in common that each has the same position in the original list. So the first list contains all first items from all lists, the second lists contains all second values from the given lists, etc.
= List.Zip( { { "a", "b", "c" }, { "A", "B", "C" } } )
// Returns { { "a", "A" }, { "b", "B" }, { "c", "C" } }
= List.Zip( { { "a", "b", "c" }, { "A", "B", "C" }, { 1, 2, 3 } } )
// Returns { { "a", "A", 1 }, { "b", "B", 2 }, { "c", "C", 3 } }
// In actual scenarios you will often reference column names
= List.Zip( { TableName[Column1], TableName[Column2] } )
//Or you can achieve the same, but shorter with:
= Table.ToRows( TableName[[Column1], [Column2] ] )
This section showed different ways to generate lists from scratch. Yet there are other functions that result in a list.
3. Other Ways to Retrieve Lists
Besides creating lists from logic, there are other ways to create a list.
3.1. Reference Columns
This method explains how to create a list from column values. You can see a table as multiple lists next to each other. Knowing this, it may come natural that referencing a column results in a list. If you want to work with the values in a column you can extract column values by writing: TableName[ColumnName]. This syntax turns the column values into a list.
The same happens when you right-click a column header and press drill-down.
3.2. Non-List Functions
This section shows non-list functions that result in a list. For as this article focuses on lists and list functions, I will but shortly describe the functions.
- Table.Column: Returns the column values from the table as a list.
- Table.ColumnNames: Generates a list of column names in the table as list of text.
- Table.Split: splits a table into a list of tables.
- Table.SplitAt: returns a list containing two tables.
- Table.ToColumns: creates a list of nested lists from the table.
- Table.ToList: converts a table to a list of records.
- Table.ToRecords: converts a table to a list of records.
- Table.ToRows: creates a list of nested lists from the table
- Text.ToList: Creates a list of individual characters from the text value.
- Text.Split: Transforms text into a list of text values based on a single specified delimiter.
- Text.SplitAny: Returns a list of text values based on the splitting by any specified delimiter.
- Binary.ToList: Converts a binary value into a list of numbers.
- Record.ToList: Returns a list of values containing the field values from the input.
- Lines.FromBinary: Converts a binary value to a list of text values split at lines breaks.
- Lines.FromText: Converts a text value to a list of text values split at lines breaks
Because the mentioned functions result in a list, you can apply list functions to them. This allows you to retrieve information, transform the lists, or filter the lists. Now you know different ways to create lists it is time to learn how to access values within the list.
4. Access Values from Lists
You can assess values from a list by applying a zero-based index inside curly brackets. In a zero-based index, the first value in a list has position 0. Earlier you learned that braces { } indicate the start and end of a list. Yet when you add a zero-based index number between curly braces behind the list, it retrieves the relating value from the list. It gives you item access to the list values by its location.
= { 1, "ABC", true }{1} // Returns the value "ABC"
= { { 1, 2 },{ 8, 9 } }{0} // Returns the first list {1, 2}
= { { 1, 2},{ 8, 9 } }{0}{1}
// Returns the second item from the first list
= { [ Name = "Bob", Age = 29 ], [ Name = "Ava", Age = 44 ] }{0}
// Returns the first record
= { [ Name = "Bob", Age = 29 ], [ Name = "Ava", Age = 44 ] }{0}[Name]
// Returns the Name column from the first record in a list
= #table( { "A","B" },{ { 0, 1 }, { 2, 1 } } ){ [ A=2 ] }
//Returns { 2, 1 } the column values in the row where A=2
Yet if the zero-based index number is bigger than the amount of values in your list, Power Query throws the error: “Expression.Error: There weren’t enough elements in the enumeration to complete the operation.” It means that the location does not exist in the existing list.
You can prevent this error from happening by adding a question mark behind the expression. This action returns null if the list does not contain enough values. For example:
= { 1, "ABC", true }{4} // Returns an error
= { 1, "ABC", true }{4}? // Returns null
5. Working with Lists
5.1. Retrieve Values
An easy way to retrieve the first or last element in a list is by filtering it using the List.First and List.Last functions. You can provide an optional default value for when the list is empty.
= List.First( { 1, "ABC", true } ) // Returns 1
= List.First( {} ) // Returns null
= List.First( {}, "Missing" ) // Returns default "Missing"
= List.Last( { 1, "ABC", true } ) // Returns true
= List.Last( {} ) // Returns null
= List.Last( {}, "Missing" ) // Returns default "Missing"
// Alternatively you can find the amount of items in a list .
// and subtract one, to retrieve a zero-based index.
= { 1, "ABC", true } { List.Count( { 1, "ABC", true } ) - 1 }
// Which is the same as
= { 1, "ABC", true }{2}
If instead you want to retrieve a range of values at the start or end of a list you can use List.FirstN or List.LastN. Both functions have 2 arguments. The list to search, yet the second argument can hold either a count or a condition. As second argument:
- when you enter a number, up to that many items are returned
- when you enter a condition, the function return all items that initially meet the condition. So once a value fails the condition, no other items are considered.
= List.FirstN( { 1, 2, 3, 4 }, 2) // Returns { 1, 2 }
= List.FirstN( { 1, "ABC", true }, 2) // Returns { 1, "ABC" }
= List.FirstN( { 3, 2, 4, 1 }, each _ <= 3) // { 3, 2 }
= List.FirstN( { 3, 5, 4, 1 }, each Number.IsOdd( _ ) ) // { 3, 5 }
= List.LastN( { 1, 2, 3, 4}, 2) // { 3, 4 }
= List.LastN( { 1, "ABC", true}, 2) // { "ABC", true }
= List.LastN( { 3, 2, 4, 1 }, each _ <= 3) // { 1 }
= List.LastN( { 3, 5, 4, 12 }, each Number.IsEven( _ ) ) // { 4, 12 }
You can use the List.FirstN function to compute a running total.
The functions List.MinN and List.MaxN allow you to retrieve minimum or maximum values from a list.
= List.MinN( { 1, 1, 2, 3, 4, 5, 6 }, 2 ) // Equals { 1, 1 }
= List.MinN( { 1, 10, 3, 14, 6 }, 3 ) // Equals { 1, 3, 6 }
= List.MaxN( { 1, 1, 2, 3, 4, 5, 6 }, 3 ) // Equals { 6, 5, 4 }
= List.MaxN( { 1, 10, 3, 14, 6 }, 3 ) // Equals { 14, 10, 6 }
You can also extract a range of values in the middle of your Power Query list by using List.Range. The List.Range function requires you to enter the number of values to retrieve and indicate from which offset position it should retrieve the values.
= List.Range( { 1, 2, 3, 4 }, 0, 2 ) // Returns { 1, 2 }
= List.Range( { 1, 2, 3, 4 }, 2, 2 ) // Returns { 3, 4 }
= List.Range( { 1, 1, 2, 3, 4 }, 2, 3 ) // Returns { 2, 3, 4 }
A more flexible way to filter a list is by using List.Select. The List.Select function allows you to create a function which determines what values to keep. In other words, it returns all values that match a condition.
With such conditions, you are able to only show a certain data type. For example, only keep numbers, texts or lists from your list.
= List.Select( { 1, 1, 2, 3, 4, 5, 6 }, each _ <= 2 )
// Returns values smaller or equal to two: { 1, 1, 2 }
= List.Select( { -1, -2, 5, 6 }, each _ < 0 )
// Returns negative values: { -1, -2 }
= List.Select( { 1, 1, 2, 3, 4, 5, 6 }, each Number.IsEven( _ ) )
//Returns even numbers: { 2, 4, 6 }
= List.Select( { 1, 1, 2, 3, 4, 5, 6 }, each Number.IsOdd( _ ) )
// Returns odd numbers: { 1, 1, 3, 5 }
= List.Select( { "a", 1, "c", 3, 4, 5, 6 },
each Value.Type( _ ) = type text )
// Returns text values only: { "a", "c" }
= List.Select( { "a", 1, "c", 3, 4, 5, 6 },
each Value.Type( _ ) = type number )
// Returns number values only: { 1, 4, 5, 6 }
= List.Select( { "a", 1, "c", { 3, 4, 5 }, 6 },
each Value.Type( _ ) = type list )
// Returns list values only: { { 3,4,5 } }
With what you just learned you can replicate the COALESCE operator in Power Query. It returns the first value that is not null, or the default value if everything returns null.
= List.First( List.RemoveNulls( { null, null, 1, 2 } ) )
= List.First( List.RemoveFirstN( { null, null, 1, 2 }, each _ = null ) )
= List.RemoveFirstN( { null, null, 1, 2 }, each _ = null ){0}
= List.Select( { null, null, 1, 2 }, each _ <> null ){0}
= null ?? null ?? 1 ?? 2
// All previous formulas return 1
// To return a default value you can use one of the following:
= List.First( List.RemoveNulls( { null, null, "Default" } ) )
= List.First( List.RemoveNulls( { null, null } ), "Default" )
5.2. Remove Values
You can also remove a range of values from a list by using List.RemoveFirstN and List.RemoveLastN. Both list functions have 2 arguments. The first argument is the list to search, and the second argument can hold either a count or a condition. As second argument:
- when you enter a number, up to that many items are removed.
- when you enter a condition, the function removes all items that initially meet the condition. So once a value fails the condition, no other items are considered.
= List.RemoveFirstN( { 1, 2, 3, 4 }, 2 ) // { 3, 4 }
= List.RemoveFirstN( { 1, 2, 3, 4 }, 3 ) // { 4 }
= List.RemoveFirstN( { 3, 2, 4, 1 }, each _ <= 3 ) // { 4, 1 }
= List.RemoveLastN( { 1, 2, 3, 4 }, 2 ) // { 3, 4 }
= List.RemoveLastN( { 1, 2, 3, 4 }, 3 ) // { 1 }
= List.RemoveLastN( { 5, 4, 12 }, each Number.IsEven( _ ) ) // { 5 }
When you want to remove a list of values from a list you can use List.RemoveItems or List.RemoveMatchingItems. At first sight these two functions look identical. They both remove items from list1 that are present in list2. However, the List.RemoveMatchingItems function has an optional equation criteria. It for example allows you to control equality testing and with that ignore capital letters.
= List.RemoveItems( { 1, 2, 3, 4, 5, 6 }, { 4, 5 } )
// Returns { 1, 2, 3, 6 }
= List.RemoveMatchingItems( { 1, 2, 3, 4, 5, 6 }, { 4, 5 } )
// Returns { 1, 2, 3, 6 }
= List.RemoveMatchingItems( { "a", "b", "c", "d" }, { "A", "B" } )
// Returns { "a", "b", "c", "d" }, searches capital sensitive
= List.RemoveMatchingItems( { "a", "b", "c", "d" }, { "A", "B" },
Comparer.OrdinalIgnoreCase )
// Returns { "c", "d" }, searches capital insensitive
Before example showed how to remove matching items. Sometimes you only want to keep matching items. You can use List.Intersect to keep the matching items between lists. The function has an optional equation criteria that allows you to ignore capitals. The function also works with multiple lists and supports duplicate values.
= List.Intersect( { { 1, 2, 3 }, { 2 } } ) // Equals { 2 }
= List.Intersect( { { 1..4 }, { 2..5 }, { 3..6 } } ) // Equals { 3, 4 }
= List.Intersect( { { 1, 1, 3 }, { 1 } } ) // Equals { 1 }
= List.Intersect( { { 1, 1, 3 }, { 1, 1 } } ) // Equals { 1, 1 }
= List.Intersect( { { "a", "b", "c", "d" }, { "a", "b" } } )
// Equals { "a", "b" }
= List.Intersect( { { "a", "b", "c", "d" }, { "A", "B" } } )
// Equals {}, respects capitalization
= List.Intersect( { { "a", "b", "c", "d" }, { "A", "B" } },
Comparer.OrdinalIgnoreCase )
// Equals { "a", "b" }, ignores capitalization
Alternatively you can keep all values in a list that do not appear in another list by using the List.Difference function. This function also has an optional equation criteria value. It support duplicate values and only accepts two lists.
= List.Difference( { 1, 2, 3 }, { 2 } ) // Equals { 1, 3 }
= List.Difference( { 1..4 }, { 2..3 } ) // Equals { 1, 4 }
= List.Difference( { 1, 1, 2 }, { 2, 2, 2 } ) // Equals { 1, 1 }
= List.Difference( { "a", "b", "c", "d" } ,{ "a", "b" } )
// Equals { "c", "d" }
= List.Difference( { "a", "b", "c", "d" }, { "A", "B" } )
// Equals { "a", "b", "c", "d" } respects capitalization
= List.Difference( { "a", "b", "c", "d" }, { "A", "B" },
Comparer.OrdinalIgnoreCase )
// Equals { "c", "d" } ignores capitalization
In case you want to remove null values from a list, use List.RemoveNulls.
= List.RemoveNulls( { 1, null, 2, null, 0, 1, 2 } )
// Returns { 1, 2, 0, 1, 2 } by removing null but keeping 0's
You can remove a continuous range of values with the List.RemoveRange function. You can input an offset and a number of values to remove. The default number of values removed is 1.
= List.RemoveRange( { 1, 2, 4 }, 2 ) // Returns { 1, 2 }
= List.RemoveRange( { 1, 2, 4 }, 2, 1 ) // Returns { 1, 2 }
= List.RemoveRange( { 1, 2, 4, 5 }, 2, 2 ) // Returns { 1, 2 }
= List.RemoveRange( { 1, 2, 4, 5 }, 3, 1 ) // Returns { 1, 2, 4 }
At other times you may want to skip a few values in your list but retrieve all others. You can easily do that by using List.Skip. The first argument is the list to search, and the second argument can hold either a count or a condition. As second argument:
- when you enter a number, up to that many items are skipped
- when you enter a condition, the function skips all items that initially meet the condition. So once a value fails the condition, no other items are considered.
= List.Skip( { 1, 2, 3, 4 }, 1 ) // Returns { 2, 3, 4 }
= List.Skip( { 1, 2, 3, 4 }, 2 ) // Returns { 3, 4 }
= List.Skip( { 1, 2, 3, 4 }, each _ <= 3 ) // Returns { 4 }
= List.Skip( { 9, 5, 1, 2, 6 }, each _ >= 5 ) // Returns { 1 ,2, 6 }
One of the most used list functions is List.Distinct. This function returns a list that contains all values from the source list but removes duplicates.
= List.Distinct( { 1, 1, 2, 1, 2, 5, 6 } ) // Returns { 1, 2, 5, 6 }
= List.Distinct( { "a", "a", "b", "b" } ) // Returns { "a", "b" }
= List.Distinct( { {2, 2}, {1, 1}, {1, 1} } )
// Returns { {2, 2}, {1, 1} }
5.3. Add Values
Previous section showed you how to remove list values or how to keep list values. This section shows how you can add new data to your lists. When we look at list functions, you can make use of List.Union, List.Combine or List.InsertRange.
- List.Combine concatenates two lists into a single list.
- List.Union combines a list of lists, transforms them into a single list and keeps only unique values. You can add the equationCriteria to control equality testing. Comparer.OrdinalIgnoreCase allows you to ignore capitals.
- List.InsertRange lets you insert a list of values at a specified position in the input list.
= List.Combine( { { 0, 1, 2 }, { 2, 3 } } ) // Equals { 0, 1, 2, 2, 3 }
= List.Union( { { 0, 1, 2 }, { 2, 3 } } ) // Equals { 0, 1, 2, 3 }
// Returns unique list of values
= List.Union( { { "a", "b" }, { "B", "c" } } )
// Returns unique list, respecting capitals { "a", "b", "B", "c" }
= List.Union( { { "a", "b" }, { "B", "c" } } ,
Comparer.OrdinalIgnoreCase )
// Return unique list, ignore capitals { "a", "b", "c" }
= List.InsertRange( { 0, 1, 2 }, 1, { -1, -2 } )
// Equals { 0, -1, -2, 1, 2 }
5.4. Transform Values
The next area we look at is transforming the values in a list. A function that transforms values in a list is List.Transform. The List.Transform function performs a transformation for each item in the list.
This transformation is based on a function. The functions you can create are only limited by your creativity. See the following examples:
= List.Transform( initial list, function to transform list )
= List.Transform( { 1, 2, 3 }, each _ + 1 ) // Equals { 2, 3, 4 }
= List.Transform( { 1, 2, 3 }, each _ * 2 ) // Equals { 2, 4, 6 }
= List.Transform( { "amy", "may", "joe" }, Text.Proper )
// Returns { "Amy", "May", "Joe" }
= List.Transform( { "amy", "may", "joe" }, each Text.Start( _, 2 ) )
// Returns first two characters of each value { "am", "ma", "jo" }
A more advanced function to transform values is List.TransformMany. The List.TransformMany function takes an initial list (x), takes an intermediate step to transform this list (y), and then uses a function to combine the results (x and y).
= List.TransformMany(
initial list, // initial list x
( x ) => ..., // transforms elements of list x to y
( x, y ) => ... // combines results of x and y
)
= List.TransformMany(
{ 1..5 },
( x ) => { #date( 2021, x, 1 ) },
( x, y ) => Text.From( x ) & " - " & Date.ToText( y, "MMM" ) )
)
//Returns { "1 - Jan", "2 - Feb", "3 - Mar", "4 - Apr","5 - May" }
= List.TransformMany(
{ 2021..2023 },
( x ) => { #date( x, 1, 1 ) },
( x, y ) => "First day " & Text.From( x ) & ": " &
Date.DayOfWeekName( y )
)
// Returns { "First day 2021: Friday", "First day 2022: Saturday",
// "First day 2023: Sunday" }
5.5. Replace Values
Another way to change values is by replacing them. You can replace values in a list with the function List.ReplaceValue. The function searches within a list of values and replaces matching values with a replacement. Just like the Table.ReplaceValue function, the List.ReplaceValue function has a replacer function. You can either replace when a value has a partial match or when it has an exact match. To understand the differences you can read more on this in my article on replacing values.
= List.ReplaceValue( { "Bar", "CAT", "Tea" }, "a", "1",
Replacer.ReplaceText )
// Replaces with partial match { "B1r", "CAT", "Te1" }
= List.ReplaceValue( { "Bar", "CAT", "Tea" }, "a", "1",
Replacer.ReplaceValue )
// Replaces with exact match { "Bar", "CAT", "Tea" }
= List.ReplaceValue( { "Bar", "CAT", "Tea" }, "Bar", "1",
Replacer.ReplaceValue )
// Replaces with exact match { "1", "CAT", "Tea" }
Imagine doing 5 replace operations. You would have 5 separate steps in your query. You can consolidate these with another function. The List.ReplaceMatchingItems function can replace multiple sets of values in a list. The function has three arguments.
- the original list
- one or more lists of two values with the old value and the new value, provided in a list
- Optional equation criteria (for example Comparer.OrdinalIgnoreCase)
= List.ReplaceMatchingItems( { 1, 2, 3, 4 }, { { 1, 10 } } )
// One list with replacement values, returns { 10, 2, 3, 4, 5 }
= List.ReplaceMatchingItems( { 1, 2, 3, 4 }, { { 1, 10 }, { 3, 30 } } )
// Two lists with replacement values, return { 10, 2, 30, 4, 5 }
= List.ReplaceMatchingItems( { 1, 2, 3, 4 },
{ { 1, 10 }, { 3, 30 }, { 4, 400 } } )
// Three lists with replacement values, return { 10, 2, 30, 400 }
= List.ReplaceMatchingItems( { "a", "b", "c", "d" }, { { "a", "z" } } )
// One list with replacement values, returns { "z", "b", "c", "d" }
= List.ReplaceMatchingItems( { "a", "b", "c", "d" }, { { "A", "z" } } )
// Capital sensitive replace returns { "a", "b", "c", "d" }
= List.ReplaceMatchingItems( { "a", "b", "c", "d" }, { { "A", "z" } },
Comparer.OrdinalIgnoreCase )
// Ignores capitals and returns { "z", "b", "c", "d" }
In some cases you may want to replace a range of values, regardless of their contents. The function to replace a range of values is List.ReplaceRange. List.ReplaceRange replaces a given amount of values with a replacement list starting at a specified position. This allows you to for example replace 3 values from the original list with 5 values of your replacement list.
= List.ReplaceRange( { 1, 1, 1, 4, 5 }, 1, 2, { 2, 3 } )
// Returns { 1, 2, 3, 4, 5 }
= List.ReplaceRange( { "A", "B", "Dan", "Ace" }, 0, 2, { "Avi", "Ben" } )
// Returns { "Avi", "Ben", "Dan", "Ace" }
// Notice how you can replace 4 values and return only 2.
// Or the other way around.
= List.ReplaceRange(
{ "A", "B", "Dan", "Ace" }, 0, 4, { "Avi", "Ben" } )
// Returns { "Avi", "Ben" }
= List.ReplaceRange(
{ "A", "B", "Dan", "Ace" }, 0, 2, { "Aad", "Bas", "Cas", "Gia" } )
// Returns { "Aad", "Bas", "Cas", "Gia", "Dan", "Ace" }
5.6. Reorder Values
Many times, the order in which your values show is important. If those cases you can sort your data with the List.Sort function. The List.Sort function is a very flexible function that allows you to transform data before you sort it.
It takes a list as first argument and has an optional comparisonCriteria as second argument. When you leave the second argument empty, it automatically sorts in an Ascending way.
You can control the sort order with the comparison criteria. That can be one with simple functions like Order.Ascending or Order.Descending.
= List.Sort( { 2, 3, 1 } ) // Returns { 1, 2, 3 }
= List.Sort( { 2, 3, 1 }, Order.Ascending ) // Returns { 1, 2, 3 }
= List.Sort( { 2, 3, 1 }, Order.Descending ) // Returns { 3, 2, 1 }
= List.Sort( { "A", "B", "Da", "Ac" } ) // { "A", "Ac", "B", "Da" }
= List.Sort( { "A", "B", "Da", "Ac" }, Order.Descending)
// Returns { "Da", "B", "Ac", "A" }
You can also transform the list of values. For example by making the numbers absolute using Number.Abs, and then optionally include a sort order. Or by transforming letters to uppercase using Text.Upper so you can sort capital insensitive.
// For sorting you can transform numbers to absolute values.
= List.Sort( { 1, 5, -8, -9 }, each Number.Abs( _ ) )
// Returns { 1, 5, -8, -9 }
= List.Sort( { 1, 5, -8, -9 },
{ each Number.Abs( _ ), Order.Ascending } )
// Returns similar to previous: { 1, 5, -8, -9 },
= List.Sort( { 1, 5, -8, -9 },
{ each Number.Abs( _ ), Order.Descending } )
// Returns previous in reverse order: { -9, -8, 5, 1 }
// You can transforms letters uppercase to sort case insensitive
= List.Sort( { "a", "A", "b", "B" } ) // Returns { "a", "b", "A", "B" }
= List.Sort( { "a", "A", "b", "B" }, each Text.Upper( _ ) )
// Returns similar to previous: { "a", "A", "b", "B" }
= List.Sort( { "a", "A", "b", "B" },
{ each Text.Upper( _ ), Order.Descending } )
// Sorts previous list in Descending order: { "b", "B", "a", "A" }
Yet you can even apply logic through a function to indicate a custom sort order. This is helpful when you can’t use any text or logical functions to indicate the correct order. You often see helper columns for this, but it can be achieved without.
= List.Sort( {"Feb", "Jan", "Feb", "Jan", "Mar" } )
// Returns { "Feb, "Feb", "Jan", "Jan", "Mar" }.
The regular sorting order is wrong for month names. You can fix that by applying the following code:
= List.Sort( { "Feb", "Jan", "Feb", "Jan", "Mar" } ),
( x, y ) => Value.Compare(
List.PositionOf( { "Jan","Feb","Mar" }, x ),
List.PositionOf( { "Jan","Feb","Mar" }, y )
)
)
// Returns { "Jan", "Jan", "Feb", "Feb", "Mar" }
Perhaps the order of your list is not alphabetical or numerical. Yet you want to reverse the order of your data. For those instances, you can make use of the List.Reverse function in Power Query.
= List.Reverse( { 2, 3, 1 } ) // Returns { 1, 3, 2 }
= List.Reverse( { "A", "B", "G", "F" } ) // Returns { "F", "G", B", "A" }
6. Find Values in Lists
6.1. Find Position of Values
If you want to know where a value occurs in a list, you can make use of the list functions List.PositionOf and List.PositionOfAny.
- List.PositionOf: searches for a value and returns the position of the first match in the list.
- List.PositionOfAny: searches for a list of values and returns the position of the first match in the list.
The List.PositionOf function is helpful when you want to know if a value occurs in a list. The function searches for a value and returns the index number of the first value that matches in the list. Its arguments are:
1. list of values
2. value to search for
3. optional occurrence parameter
4. optional equation criteria.
By default the function returns the position of the first occurrence of the substring. However, you can use the occurrence parameter in the optional third argument to change this behavior. You can use:
Occurrence.First – returns the position of first occurrence of the searched value
Occurrence.Last – returns the position of last occurrence of the searched value
Occurrence.All – returns a list of positions of all occurrences of the searched value
When the searched value does not appear in the list, the function returns -1.
= List.PositionOf( { 1, 3, 5, 7, 9 }, 9 ) // Returns 4
= List.PositionOf( { "ape", "duck", "cow" }, "cow" ) // Returns 2
// Use the Occurrence parameter to input how many values to return
// when multiple values match in a list, returns { 0, 2 }
= List.PositionOf( { "ape", "duck", "ape", "cow" }, "ape", 2 )
// The Occurrence parameter also determines which value to return
= List.PositionOf( { "ape", "duck", "ape", "cow" }, "ape", 0 ) // 0
= List.PositionOf( { "ape", "duck", "ape", "cow" }, // Returns 0
"ape", Occurrence.First )
= List.PositionOf( { "ape", "duck", "ape", "cow" }, // Returns 2
"ape", Occurrence.Last)
= List.PositionOf( { "ape", "duck", "ape", "cow" }, // { 0, 2 }
"ape", Occurrence.All )
= List.PositionOf( { "ape", "duck", "cow" }, "Ape" )
// Returns -1 because "Ape" does not exist with a capital
= List.PositionOf(
{ "ape", "duck", "cow" },
"Ape",
1,
Comparer.OrdinalIgnoreCase )
// Returns 0 because search for "Ape" ignores case
= List.PositionOf(
{ "ape", "duck", "ape", "cow" }, // List to search
"Ape", // Value to match
2 , // Max Num of Values
Comparer.OrdinalIgnoreCase ) // Ignore Capitals
// Returns { 0, 2 }, number of occurrences is 2, capitals ignored
The List.PostionOfAny function allows you to search for multiple values in your list. The function uses a search list with values and compares it with the original list. It then returns the offset position of the first value from the original list that matches a value from the search list.
Just like with the List.PositionOf function, you can provide the an occurrence parameter to in argument 3, and the equation criteria in argument 4.
= List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 } ) // Returns 1
= List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 }, 2 ) // Returns { 1, 2 }
// The occurrence parameter impacts which value to return
= List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 }, Occurrence.First) // 1
= List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 }, Occurrence.Last ) // 2
= List.PositionOfAny( { 1, 2, 3, 4 }, { 2, 3 },
Occurrence.All ) // { 1, 2 }
= List.PositionOfAny(
{ "ape", "duck", "ape", "cow" },
{ "APE", "DucK" },
2 )
// Returns {}, it has no match because search is case sensitive
= List.PositionOfAny(
{ "ape", "duck", "ape", "cow" }, // List to search
{ "APE", "DucK" }, // Values to match
2 , // Max Num of Values
Comparer.OrdinalIgnoreCase ) // Ignore Capitals
// Returns { 0, 1, 2 } because search ignores case.
6.2. Test if List Contains Values
Another common requirement is to check whether a list contains a value. There are list functions that are helpful here: List.Contains, List.ContainsAll and List.ContainsAny. By default all of these look for an exact match. From these three functions, the last two can check if a list contains either one or all of multiple values.
- List.Contains: returns true when a list contains the searched value
- List.ContainsAll: returns true when a list contains all values from a list of values
- List.ContainsAny: returns true when a list contains any value from a list of values
Let’s see some examples. The List.Contains function contains an optional equation criteria. Comparer.OrdinalIgnoreCase indicates whether it should respect capitalization in its search. Yet you can also apply your custom function.
// The following examples look for an exact match
= List.Contains( { 2, 4, 6, 8 }, 6 ) // Returns true
= List.Contains( { 2, 4, 6, 8 }, 1 ) // Returns false
= List.Contains( { 2, 4, 6, 8 }, "6" ) // Returns false
= List.Contains( { "a", "b", "c" }, "a" ) // Returns true
= List.Contains( { "a", "b", "c" }, "A" ) // Returns false
= List.Contains( // Returns true
{ "a", "b", "c" }, "A", Comparer.OrdinalIgnoreCase )
// For a partial match you can apply a custom equation criteria.
= List.Contains( { "Rick", "RickdeGroot", "GorillaBI" }, "ick",
( x, y ) => Text.Contains( x, y ) )
// Returns true
= List.Contains( { "Rick", "RickdeGroot", "GorillaBI" }, "ICK",
( x, y ) => Text.Contains(
x, y, Comparer.OrdinalIgnoreCase ) )
// Returns true, example is capital insensitive
= List.Contains( { "Rick", "RickdeGroot", "GorillaBI" },
"ICK",
( x, y ) => Text.Contains(
Text.Lower( x ), Text.Lower( y ) ) )
// Returns true, shows an alternative way
By applying the before logic, you can use the List.Contains function to replicate the IN operator in Power Query.
Besides checking for a single value, you can also check whether a list contains all values within a list by using the List.ContainsAll function.
= List.ContainsAll( { 2, 4, 6, 8 }, { 3, 4 } ) // Returns false
= List.ContainsAll( { 2, 4, 6, 8 }, { 2, 4 } ) // Returns true
= List.ContainsAll( { "a", "b", "c" }, { "a", "b" } ) // Returns true
= List.ContainsAll( { "a", "b", "c" }, { "A", "b" } ) // Returns false
= List.ContainsAll( { "a", "b", "c" }, // Returns true
{ "A", "b" }, Comparer.OrdinalIgnoreCase )
Lastly, you can also check whether a list contains one value from a list of values using the List.ContainsAny function.
= List.ContainsAny( { 2, 4, 6, 8 }, { 3, 5 } ) // Returns false
= List.ContainsAny( { 2, 4, 6, 8 }, { 3, 4 } ) // Returns true
= List.ContainsAny( { "a", "b", "c" }, { "a", "b" } ) // Returns true
= List.ContainsAny( { "a", "b", "c" }, { "A", "B" } ) // Returns false
= List.ContainsAny( { "a", "b", "c" }, // Returns true
{ "A", "B" }, Comparer.OrdinalIgnoreCase )
Any of the earlier functions can easily be reversed to check if a list does not contain a value. You can use the not operator for that.
= not List.Contains( { 2, 4, 6, 8 }, 6 ) // Returns false
= not List.ContainsAll( { 2, 4, 6, 8 }, { 3, 4 } ) // Returns true
= not List.ContainsAny( { 2, 4, 6, 8 }, { 3, 5 } ) // Returns true
This section showed how to test whether a list contains one or more values. These functions require the exact list of items to check for. But what if you want to test if the values in your list satisfy certain conditions?
6.3. Test if Values Satisfy Condition
If you want to test if the values in a list satisfy a condition you can make use of the List.MatchesAny and List.MatchesAll functions.
- List.MatchesAny: returns true when any of the list values satisfies the condition function.
- List.MatchesAll: returns true when all of the list values satisfies the condition function.
//List.MatchesAny tests if any value satisfies the condition
= List.MatchesAny( { 2, 4, 6 }, each _ < 10 ) // Returns true
= List.MatchesAny( { 2, 4, 6 }, each _ > 10 ) // Returns false
= List.MatchesAny(
{ 1, 2, 3 }, each Number.IsEven( _ ) ) // Returns true
//List.MatchesAny tests if all value satisfies the condition
= List.MatchesAll( { 2, 4, 6 }, each _ < 10 ) // Returns true
= List.MatchesAll( { 2, 4, 6 }, each _ > 10 ) // Returns false
= List.MatchesAll( { 1, 2, 3 },
each Number.IsEven( _ ) ) // Returns false
6.4 Return Values Containing Text
Your list may contain all kinds of text values. If you want to select only the text values that contain specified characters you can use the List.FindText function. The List.FindText function takes a list and returns the values that contain the specified text value. The condition needs to satisfy both the characters and the case.
= List.FindText( { "ape", "duck", "bird", "fish" }, "i" )
// Returns { "bird", "fish" }
= List.FindText( { "ape", "duck", "bird", "fish" }, "I" )
// Returns {}, the function searches case sensitive
// A solution is to turn list values into uppercase
// You can then search the transformed list.
= List.FindText(
List.Transform( { "ape", "duck", "bird", "fish" },
each Text.Upper( _ )
), "I"
)
If you want a similar result and ignore capitalization, you can also use a combination of List.Select and Text.Contains:
// The following syntax has a similar result: { "bird", "fish" }
= List.Select( { "ape", "duck", "bird", "fish" },
each Text.Contains( _, "i") )
// Below adjustment ignores capitals: { "bird", "fish" }
= List.Select( { "ape", "duck", "bird", "fish" },
each Text.Contains( _, "I", Comparer.OrdinalIgnoreCase ) )
7. List Operators
There are three operators that work with lists in Power Query.
7.1. Combine or Concatenate Lists
A way to combine lists in Power Query is by using the ampersand (&) also known as the concatenate operator. You can put the ampersand between the lists you want to combine.
= { 3, 4 } & { 1, 2 } // { 3, 4, 1, 2 }
// Combines 2 lists of number values
= { "a", "b" } & { "C", "D" } // { "a", "b", "C, "D" }
// Combines 2 lists of text values
= { "a", "b" } & { 1, 2 } // { "a", "b", 1, 2 }
// Combines list of numbers and text values
You can also concatenate list values using the List.Combine function. It is good to remind yourself that the List.Combine function requires you to wrap the lists you want to combine within curly brackets. That means below construct has three sets of curly brackets for combining two lists.
= List.Combine( { { "a","b" }, { 1, 2 } } ) // Equals { "a", "b", 1, 2 }
Another way to combine lists is by using List.Union. The difference between List.Union and List.Combine is in the values it returns. The List.Union function returns a list of unique values, whereas the List.Combine function includes duplicates.
= List.Combine( { { 1, 2 }, {1, 2} } ) // Equals { 1, 2, 1, 2 }
= List.Union( { { 1, 2 }, {1, 2} } ) // Equals { 1, 2 }
7.2. Compare Lists
You may have times where you want to compare two lists. To check if two lists are equal you can use the equal (=) or unequal (<>) operators. You will find that two lists with values in a different order are not equal.
= { 2, 4 } = { 2, 4 } // returns true, same order
= { 2, 4 } = { 4, 2 } // returns false, different order
= { 2, 4 } <> { 4, 2 } // returns true, different order
8. Perform Math Operations on Lists
Power Query offers a selection of math functions that work with lists. Many of them perform standard math operations. You can get an idea of the list length by counting the number of values in a list. But you can also think of calculating the average, summing values, finding the minimum and maximum and many more. The most used list functions you can find below:
= List.Count( {1, 1, 2, 3, 4, 5, 6} ) // Equals 7
= List.Sum( {1, 1, 2, 3, 4, 5, 6} ) // Equals 22
= List.Mode( {1, 1, 2, 3, 4, 5, 6} ) // Equals 1
= List.Modes( {1, 1, 2, 3, 4, 5, 6} ) // Equals 1
= List.Average( {1, 1, 2, 3, 4, 5, 6} ) // Equals 3.142857
= List.Median( {1, 1, 2, 3, 4, 5, 6} ) // Equals 3
= List.Max( {1, 1, 2, 3, 4, 5, 6} ) // Equals 6
= List.MaxN( {1, 1, 2, 3, 4, 5, 6}, 3) // Equals {4, 5, 6}
= List.Min( {1, 1, 2, 3, 4, 5, 6} ) // Equals 1
= List.MinN( {1, 1, 2, 3, 4, 5, 6}, 3) // Equals {1, 1, 2}
= List.Product( {1, 1, 2, 3, 4, 5, 6} ) // Equals 720
= List.StandardDeviation( {1, 1, 2, 3, 4, 5, 6} ) // Equals 1,9518
Even if these may seem basis operations, some proof extremely useful as I show in my article on summing null values.
9. Other List Functions
You have learned about many different list functions. And you may find that there are many other list functions than the ones we have seen so far. And knowing a range of them can be handy when you want to apply transformations on the contents of a column. Other common list functions this article does not cover are:
- Date, DateTime, Time and Duration functions
- Buffer function: List.Buffer
- Information functions like List.IsEmpty, List.IsDistinct, List.NonNullCount, List.Positions
To learn more about these and other functions, see the Microsoft List Functions Overview.
That was a long one! Did you learn anything new? And are there list functions you want to see more content on? Let me know in the comments.
Enjoy Power Query!
Recommend Reading >>> Power Query - Foundations <<< ፠ List Functions (200+ examples) ፠ Text functions (150+ examples) ፠ Creating Tables from Scratch (40+ examples) ፠ Understanding If Statements ፠ Generating a Date Table >>> Power Query - Advanced Topics <<< ፠ Master List.Generate with Easy Examples ፠ Deep Dive into List.Accumulate ፠ Create Calendar with Dynamic Language ፠ Replacing Values (User Interface and Beyond)
Nice article on list. Book marked the page and subscribed to your youtube channel.
Hi Rick,
Thanks for this great article.
I learn M Language from you web. It is a #1 resource on this topic.
To help you improve the content, I found some typos on this page as follow,
Article 8. Perform Math Operations on Lists
Line10
it should be
Line12
it should be
// Equals 1.9518
I can find a typo because I read your article every character, read and run.
on another page, https://gorilla.bi/videos/
the anchor link to Youtube is wrong, please correct.
“subscribe to BI Gorilla on YouTube.”
Kahn,
I edited the lines as suggested.
Thank you very much for pointing them out!
Cheers, Rick
Great & complete article – for sure I will keep it as reference/loopback when needed. Thanks Rick
very good effort
Very useful article, very much appreciated !
Happy to hear you like it!
Hi, Rick, very well written article. A lot of useful examples that show practical use of lists. Even though it does not contain much more text than official documentation on individual functions, I got considerable more from this article then from Microsoft documentation.
One thought on List.Intersect, based on what you demonstrated on List.Contains function.
From your example, List.Contains with custom equation criteria can be used for looking for partial match (even case-insensitive). One limitation, however, is that it enables you to search for one value only.
I was wondering whether something more powerful could be achieved with List.Intersect.
For example, I have a list of words with some containing diacritics. And I want to keep only those that contains the diacritics. When I saw your article I tried something like you can see below but it did not work. I am not sure whether it is not possible with List.Intersect or whether I used incorrect syntax.
I would like to have this result:
Is there a relatively simple solution to this or a completely different approach is needed?
Hey Andrej,
That is a tough cookie. I tried with different functions. Especially the List.FindText one seems relevant. But I can’t get it to work yet. Perhaps someone else can help you out?
Thanks for commenting!
Rick
Hi Andrej,
You could try something like this, just note that it will only work for single character items in the LookFor list.
Hi Andrej,
After delving into the List.Accumulate function, I’ve found a crisp solution for your question. If you want to find the items in your list that have a text string in it, a single search would suffice with:
Now if you want to run this same logic for multiple values, you can call this function multiple times once for each value to search for. List.Accumulate is the function that can help you with that:
As per your requirement this returns
For a better understanding of that function, please have a look at:
https://gorilla.bi/power-query/list-accumulate/
Hi Rick, thank you for looking into my question further. I found a workaround at that time, but this solution is much cleaner and more robust at the same time. Really appreciate the extra effort.
Okay here are two other approaches:
Or alternatively:
All credits go to Bill Szysz.
Thank you for sharing your knowledge and amazing techniques Bill !
Thanks Melissa, these approaches are a piece of art. Glad you were willing to share them 🙏
Great article. I have a question about working with lists from lists. I am trying to pull in a Microsoft List into PowerBI so that I can create some visuals. One of the visuals is a breakdown of how many times each possible choice selection in a particular column is selected. The challenge is that the choice column allows for multiple selections. I am new to PowerQuery and PowerBI and wandering how I need to transform the data coming in from the Microsoft List into my dataset to allow me to get the totals I want (if I just pull it in right now, using Microsoft Lists integration functionality with PowerBI, it sees each combination of selections as unique values, which is not what I am trying to achieve. Any suggestions on what I should do and how? Thank you.
Hi David,
I’m afraid I can’t help you here. Also, the Lists you are referring to are different from the lists this article refers to. Hope you solve it soon!
Rick
Man, this is a great comprehensive article. Great job, looking forward to your next posts!
Wao just amazing and learnt too many things .Sir please also share about record and table data type in power querry .
Sure! Record and Table functions may come later. I released a post on Text Functions today. Enjoy:
https://gorilla.bi/power-query/text-functions/
Hi Kamran,
You can now also find the post on tables right here:
https://gorilla.bi/power-query/creating-tables/
Enjoy!