This post continues series of articles about M Function List.Zip ( first post, second post).

Table.TransformColumns is another function that requires list of pairs if you want to transform several columns in Power Query

When we want to transform all fields we can simply use

// would be great to see same behaviour for Table.TransformColumnTypes

However, when we need to change only part of the table we have to generate list of pairs {column name, function}.

Text.Trim highlighted in last sample is a good example of transformation function.

In general, transformation function can contain any logic and can transform objects of any type.

If you have column with nested lists or nested tables – no problem. Read one of my post about renaming columns in nested tables in Power Query to get an idea how this can be achieved.

For example, let’s build a function that capitalize values in cells only when we have exclamation mark in text

Pattern that we will use for List.Zip is similar to one I used for Table.TransformColumnTypes in the first post. We have to repeat function as many times as many columns we want to transform, e.g. two

In Preview we will see

Zip’em all


Gives list of pairs

In combination with Table.TransformColumns

It performs transformation


However, this particular example can be simplified. List.Zip can be replaced by List.Transform:

To many underscore chars :/, and it may be confusing, better to define function as a separate step

Now it looks better.

Combined with main function:

Gives same result as with List.Zip.

Alternative way

Interesting, but same can be achieved by using 3rd argument of Table.TransformColumns

{“Col 3”, each _ } in this example does nothing with every cell of columns “Col 3”, remains original value.

3rd argument is optional, but if used is responsible for default transformation that will be applied to ALL columns not mentioned in 2nd argument.

If you want to change everything but remain one or two columns, it is easier to use 3rd argument.

Typical scenario – when you have a table with many text columns (which you want to transform) and one or two number-columns like Quantity or Sales Amount.

Alternative List.Zip

Modification of List.Zip can be found in Tycho’s collection of Custom M functions:

Basically, it allows to get a table from several lists

//Zip a list of lists so as to ‘transpose’ them — as records if names are specified.


List.Zip = Load(“List.Zip”),

List.Zip({{1,2,3},{“a”,”b”,”c”}}, {“num”,”let”})

//Result: {[num=1, let=”a”],[num=2, let=”b”],[num=3, let=”c”]}

That’s all about List.Zip so far, but maybe I’ll return to this function in one of my next posts.

Leave a Reply