Transform Columns with List.Zip

Ivan Bondarenko

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

Table.TransformColumns(#"Changed Type", {}, Text.Trim)

// 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

func = each if Text.Contains(_, "!") then 
    Text.Upper(_) else _ , // not null-proof function

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

List.Repeat( { func } , 2 )

In Preview we will see


Zip’em all

Code

func = each if Text.Contains(_, "!") then Text.Upper(_) else _ ,
result = List.Zip( { {"Col 1", "Col 2"}, 
        List.Repeat( { func } , 2 ) } )

Gives list of pairs


In combination with Table.TransformColumns

func = each if Text.Contains(_, "!") then Text.Upper(_) else _ ,
result = Table.TransformColumns(#"Changed Type",
        List.Zip( { {"Col 1", "Col 2"},
            List.Repeat( { func } , 2 ) } ) )

It performs transformation


Simplification

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

List.Transform( {"Col 1", "Col 2"}, each { _, 
    each if Text.Contains(_, "!") then Text.Upper(_) else _ } )

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

func = each if Text.Contains(_, "!") then Text.Upper(_) else _ ,
result = List.Transform( {"Col 1", "Col 2"}, each { _, func } )

Now it looks better.

Combined with main function:

func = each if Text.Contains(_, "!") then Text.Upper(_) else _ ,
result = Table.TransformColumns(#"Changed Type",
        List.Transform( {"Col 1", "Col 2"}, 
            each { _, func } ) )

Gives same result as with List.Zip.

Alternative way

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

func = each if Text.Contains(_, "!") then Text.Upper(_) else _ ,
result = Table.TransformColumns(#"Changed Type", {"Col 3", each _ },
    func )

{“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:

https://github.com/tycho01/pquery/blob/master/List.Zip.pq

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.

//Usage:

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 comment