List.Zip

Updated on

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 list

Description

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
    RenameOperation

Renaming 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.

Learn more about List.Zip in the following articles:

Other functions related to List.Zip are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/list-zip