none
Perform an action of all columns in a table - when columns names/count may fluctuate RRS feed

  • Question

  • Hi,

    If I want to transform all columns in a table to type text, but the number of, or the name of the columns are likely to change, how would you select all columns if you don't know the contents of the list.

    e.g.      Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}) expects and will only transform Column1 and Column2. If the user adds Column3 it won't be transformed.

    I think I need something like   Table.TransformColumnTypes(Source, {AllColumns} {type text}).

    I could create a list of columns using Table.Columns to give {"Column1","Column2"} but I don't know how to change this to
    {{"Column1", type text},{"Column2", type text}}.

    Any help appreciated.  Thanks.

    Thursday, September 3, 2015 10:34 PM

Answers

  • Hi PQUK,

    You can do that in one or few steps.

    One step:

    let
        Source = Excel.CurrentWorkbook(){[Name="YourTable"]}[Content],
        Transformation = Table.TransformColumnTypes( Source, Table.ToRows(Table.FromColumns({Table.ColumnNames(Source), List.Repeat({type text}, List.Count(Table.ColumnNames(Source))  )})))
    in
        Transformation

    Few steps:

    let
        Source = Excel.CurrentWorkbook(){[Name="YourTable"]}[Content],
        LSTHeaders = Table.ColumnNames(Source),
        HowMany = List.Count(LSTHeaders),
        Transformation = Table.TransformColumnTypes( Source, Table.ToRows(Table.FromColumns({LSTHeaders, List.Repeat({type text}, HowMany  )})))
    in
        Transformation

    Regards

    Friday, September 4, 2015 8:22 AM

All replies

  • Hi PQUK,

    You can do that in one or few steps.

    One step:

    let
        Source = Excel.CurrentWorkbook(){[Name="YourTable"]}[Content],
        Transformation = Table.TransformColumnTypes( Source, Table.ToRows(Table.FromColumns({Table.ColumnNames(Source), List.Repeat({type text}, List.Count(Table.ColumnNames(Source))  )})))
    in
        Transformation

    Few steps:

    let
        Source = Excel.CurrentWorkbook(){[Name="YourTable"]}[Content],
        LSTHeaders = Table.ColumnNames(Source),
        HowMany = List.Count(LSTHeaders),
        Transformation = Table.TransformColumnTypes( Source, Table.ToRows(Table.FromColumns({LSTHeaders, List.Repeat({type text}, HowMany  )})))
    in
        Transformation

    Regards

    Friday, September 4, 2015 8:22 AM
  • Thanks Bill, works a treat.
    By creating and filtering a list from Table.ColumnNames(Source),  I could select which columns to set to which types.  Many thanks

    Friday, September 4, 2015 2:11 PM
  • Code that also works:

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

    Wednesday, September 9, 2015 4:59 PM