There is one useful Power Query M function – List.Zip, but with poor documentation on MSDN.

I hope, at some point, library of M functions will be available on Github like it is done for VBA. Power Query enthusiasts then would get a chance to contribute. E.g. from MSDN page of Workbook object we can go to Github and make a pull request for changes.

I plan 2-3 posts about application of List.Zip, this is the first one.

How does List.Zip work

Let’s start from “Help” in Power Query editor, it shows simple sample

Note: to get help on function – type name of function in formula bar and press Enter. Pay attention to register, M is case sensitive.

Having short documentation directly in power query editor is great idea! However, it is hard to show all scenarios with function and keep documentation short. In this particular case, it might be not obvious what happens when we have list of lists with more than 2 elements, or lists with different number of elements, or with more than two lists.

Let’s try two lists with three elements in each

As said in docu – “combination of items at the same position”. First with first, second with second etc.

Same works with lists that contain more than two elements:

If one of lists contains less elements – “null” will be used to fill missing position

When to use List.Zip

As many other Power Query functions, List.Zip has no own button in menu (at least I couldn’t find). Nevertheless, we can use it in Advanced Editor or formula bar.

For sure, List.Zip suites to very specific cases.

I personally find it very useful when I need to generate list of pairs, which later can be used in other functions.

For example, in Table.TransformColumnTypes, Table.TransformColumns, Table.RenameColumns.

Table.TransformColumnTypes with List.Zip

Table.TransformColumnTypes awaits list of pairs {column name, desired data type} for its second argument.

This can be used, for example, to transform data type of all columns to “text” in Power Query.

In this particular scenario, the best solution I know was introduced by Imke Feldman in her blog.

Table.TransformColumnTypes(Source,

    List.Transform( Table.ColumnNames(Source), each { _, type text } ) )

Where

    each { _, type text }

transforms each element of list into required pair. Very smart!

Version with List.Zip is more bulky, but still good for purpose of understanding how List.Zip can be used:

= Table.TransformColumnTypes(Source, List.Zip( { Table.ColumnNames( Source ), 

    List.Repeat( {type text}, List.Count( Table.ColumnNames( Source ) ) ) } ) )

How does it work?

E.g. you pull data from Excel sheet or other source that doesn’t provide info about column types. By default, Power Query set data type to “any”

But what if number of columns varies and we want text for all columns?

Let’s look at the formula generated by UI when we change type manually

Table.TransformColumnTypes(Source,

    { {“Column1”, type text},

    {“Column2”, type text},

    {“Column3”, type text} } )

Second argument – pairs of column names and desired type. So we need to make pair from each table column.

Table.ColumnNames( Source ) allows to get list of table columns:

Then for List.Zip we need another list with the same number of elements (we don’t want nulls in resulting pairs)

List.Count( Table.ColumnNames( Source ) ) helps to get number of columns in the table.

Left to repeat list {type text} several times – same as number of columns in Source table

    List.Repeat( {type text}, List.Count( Table.ColumnNames( Source ) ) )

This gives list of elements where each represents “type text”

Now, use List.Zip to cross/zip it with column names:

List.Zip( { Table.ColumnNames( Source ), List.Repeat( {type text}, 

    List.Count( Table.ColumnNames( Source ) ) ) } )

Thus, we received desired pairs which can be used in Table.TransformColumnTypes

In my next posts I plan to describe how List.Zip can be used in Table.TransformColumnNames, Table.RenameColumns, Table.TransformColumns.

Stay tuned!