Power Query - changing data type for each column

[zico8]

Hi,

How can I change data type to text for all columns no matter what steps has been done before and what columns I have currently??
Is it possible?

 

[horseyride]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,List.Transform(Table.ColumnNames(Source), each {_,type text}))
in
    #"Changed Type"

 

[macfuller]

Horseyride, this enumeration through the columns seems a powerful tool. Is it possible to do this conditionally - for example, only apply the transformation to columns with "Comments" in the header name, or to skip the transformation for columns with type date?

 

[horseyride]

There's probably a one line version that someone could come up with, by my longer versions are:

Find and only change columns containing "Comments" to text

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Apple", type any}, {"Car", Int64.Type}, {"Bob", type any}, {"Comments1", type any}, {"Comments2", type any}, {"Date1", type date}, {"Date2", type date}}),
    Headers = Table.ColumnNames(Source),
    #"Converted to Table" = Table.FromList(Headers, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    //filter for anything. Here I filter to find the word Comments
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.Contains([Column1], "Comments")),
    ListHeaders = Table.ToList(#"Filtered Rows"),
    Count = List.Count(ListHeaders),
    Transform = Table.TransformColumnTypes( #"Changed Type", Table.ToRows(Table.FromColumns({ListHeaders, List.Repeat({type text}, Count  )})))
in
    Transform

Change all columns to text except columns formatted as date

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   #"Changed Type" = Table.TransformColumnTypes(Source,{{"Apple", type any}, {"Car", Int64.Type}, {"Bob", type any}, {"Comments1", type any}, {"Comments2", type any}, {"Date1", type date}, {"Date2", type date}}),
    Schema = Table.Schema(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(Schema, each ([TypeName] <> "Date.Type")),
    #"Removed Other Columns" = Table.SelectColumns(  #"Filtered Rows",{List.First(Table.ColumnNames(#"Filtered Rows"))}),
    ListHeaders = Table.ToList(#"Removed Other Columns"),
    Count = List.Count(ListHeaders),
    Transform = Table.TransformColumnTypes( #"Changed Type", Table.ToRows(Table.FromColumns({ListHeaders, List.Repeat({type text}, Count  )})))
in
    Transform

 

[zico8]

This is good horseyride, thanks for it.

Could you also show how can I set Data Type - Text for columns from the 4th to the last one?

 

[macfuller]

Zico8, check this code from an answer to another thread on this forum.