Category: Power Query

When you manually select all columns and apply a transformation (for example, changing the data type) the generated code lists each column name individually – it looks something like this:

// Generated code from manual actions
=   Table.TransformColumnTypes ( 
        #"Previous Step",
        {
            {"First Column", type text}, 
            {"2nd Column", type text}, 
            {"Col 3", type text}, 
            {"COLUMN FOUR", type text}, 
            {"Number Five (is alive)", type text}, 
            {"666", type text}
        }
    )

I frequently import data from files with varying column names, and / or varying numbers of columns. This makes it impossible to effectively hard code data transformations by column name – if somebody renames any of those columns, then the step above will fail.

So, instead of the generated steps to change each column type, we can use the List.Transform function to apply a transformation to each Column:

//Change All Columns to text
=   Table.TransformColumnTypes ( 
        #"Previous Step", 
        List.Transform ( 
            Table.ColumnNames ( #"Previous Step" ), 
            each {_, type text}
        )
    )

This example is about as simple as it gets – you can of course apply a broad range of transformation functions in this way – for example, Text.Trim:

//Trim All Columns
=   Table.TransformColumns ( 
        #"Previous Step", 
        List.Transform ( 
            Table.ColumnNames ( #"Previous Step" ), 
            each {_, Text.Trim, type text}
        )
    )