List.Zip is a Power Query M function that takes a list of lists and returns a new list of lists, combining items at the same position. The function returns a list of lists with combined items from the input lists.
Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365
Syntax
List.Zip( lists as list ) as listDescription
The List.Zip function is useful for combining multiple lists into a single list of lists. Each sub-list contains elements from the original lists that share the same position. In essence, it takes a collection of lists and pairs items that occupy the same spot in their respective lists.
Examples
Let’s explore some practical examples to understand its real-world applications:
Simple List-Zipping
Consider three basic lists: { “a”, “A” }, { “b”, “B” } and { “c”, “C” }. Using List.Zip, you can merge them as follows:
// Output: { { "a", "A" }, { "b", "B" }, { "c", "C" } }
List.Zip( { { "a", "b", "c" }, { "A", "B", "C" } } )
This same procedure works for lists of a longer length.
// Output: { { "a", "A", 1 }, { "b", "B", 2 }, { "c", "C", 3 } }
List.Zip( { { "a", "b", "c" }, { "A", "B", "C" }, { 1, 2, 3 } } )
Merging Product and Price Lists
If you’re managing an inventory and have separate lists for products and their corresponding prices, List.Zip can help you merge them for a comprehensive view:
// Output: { { "Apple", 0.5 }, { "Banana", 0.3 }, { "Cherry", 0.75 } }
List.Zip( { { "Apple", "Banana", "Cherry" }, { 0.5, 0.3, 0.75 } } )Referencing Column Names
In real-world scenarios, especially when working with tables, you’ll often need to reference column names:
List.Zip( { TableName[Column1], TableName[Column2] } )However, since we can’t directly reference a column in an expression, you will often use functions like Table.ColumnNames to retrieve column names or Table.Column to return column values.
Bulk Renaming Columns
You can use List.Zip to rename column values in bulk. Below operation retrieves the current column names as list. Then transforms the column names so they are prefixed with the text “New_”. Table.RenameColumns then uses the zipped version of these two lists to perform a rename operation.
let
Source = TableName,
OldNames = Table.ColumnNames(Source),
NewNames = List.Transform(OldNames, each "New_" & _),
RenamedColumns = List.Zip({OldNames, NewNames}),
RenameOperation = Table.RenameColumns(Source, RenamedColumns)
in
RenameOperationRenaming Columns based on Another Table
Imagine you have a reference table that maps old column names to new ones. You can use List.Zip to rename columns based on this mapping:
let
Source = TableName,
RenameMap = ReferenceTable,
OldNames = Table.Column(RenameMap, "OldName"),
NewNames = Table.Column(RenameMap, "NewName"),
RenamedColumns = List.Zip({OldNames, NewNames}),
RenameOperation = Table.RenameColumns(Source, RenamedColumns)
in
RenameOperation
In conclusion, the List.Zip function has a useful ability to merge lists by zipping them. Understanding this function helps you in getting your data into the right shape to then provide to other functions.
Related articles
Learn more about List.Zip in the following articles:
- Create Date Table with Dynamic Language in Power Query
Want your Date Table in Multiple Languages? This post explains how to make your calendar dynamic to support any language or culture. » Read more - Replace Values in Power Query M (Ultimate Guide)
Learn how to replace values in Power Query. Look into conditional replacements, replacing in multiple columns, case sensitivity and more! » Read more - Lists in Power Query M / List Functions (200+ Examples)
The complete guide to Lists in Power Query M. Learn from practical examples and master Power Query’s most powerful List functions. » Read more - 3 Ways to Select List Items by Position in Power Query
This article explores 3 different ways to select multiple items in your list by index position. » Read more
Related functions
Other functions related to List.Zip are:
