TransformColumnTypesDynamically.pq

let func =  
 (t as table, optional maxSample as number) =>
let

primitiveTypes = (t as table, optional maxSample as number) as table =>
let
Table = t,
MaxSample = maxSample,
Sample = if maxSample = null then Table.FirstN(Table,10) else Table.FirstN(Table,Number.From(maxSample)),
Columns = Table.ToColumns(Sample),
Types = List.Transform(Columns, each List.Distinct(List.Transform(List.Select(_, (ListItem)=>ListItem<>null), (i)=>Value.Type(i)))),
NewTypes = List.Transform(Types, each if List.Count(_) = 1 then _{0} else type any),
Typed = Table.TransformColumnTypes(Table,List.Zip({Table.ColumnNames(Table), NewTypes}))
in
Typed,

complexTypes = (Table as table, optional MaxSample as number) =>
let
#"**** DetermineTypes" = "New types are based on types of all values in the complete or the sample table." &
"If these are the same for all values in a column, then that will become the new column type." &
"Else the new column type will be type any.",
Sample = if MaxSample = null then Table.FirstN(Table,10) else Table.FirstN(Table,Number.From(MaxSample)),
Columns = Table.ToColumns(Sample),
Types = List.Transform(Columns, each List.Distinct(List.Transform(List.Select(_, (ListItem)=>ListItem<>null), (i)=>Value.Type(i)))),
    NewTypes = List.Transform(Types, each if List.Count(_) = 1 then _{0} else type any),
#"**** Apply types to first record" = "As far as I know, this is the only workaround to apply the types to the table, without using Table.TransformTypes which can not handle all types.",
ColumnNames = Table.ColumnNames(Sample),
FirstRecord = Table{0},
Values = Record.FieldValues(Table{0}),
ValuesWithNamesAndTypes = List.Zip({Values,ColumnNames,NewTypes}),
RecordType = Type.ForRecord(Record.FromList(List.Transform(NewTypes, each [Type = _, Optional = false]),ColumnNames), false),
TypedFirstRecord = Value.ReplaceType(FirstRecord,RecordType),
#"**** Create table" = "Create a table with the nested record in 1 column, apply the record type to this first column," &
"so these types will be promoted to the table columns while expanding.",
DummyTable = Table.FromColumns({{TypedFirstRecord}},type table[Record = RecordType]),
TableWithNewTypes = Table.ExpandRecordColumn(DummyTable, "Record", ColumnNames),
#"**** Apply table type" = "Now the type of the dummy table can be applied to the original table",
TypedTable = Value.ReplaceType(Table,Value.Type(TableWithNewTypes))
in
TypedTable,

Result = try primitiveTypes(t, maxSample) otherwise complexTypes(t, maxSample)
in
Result
, documentation = [
Documentation.Name =  " Table.TransformColumnTypesDynamically
", Documentation.Description = " Dynamically transforms column types from table  <code>t</code> according to the columns contens of its first 10 rows by default. The number of rows to be taken into consideration can be adjusted by the optional parameter  <code>maxSample</code>.
" , Documentation.LongDescription = " Dynamically transforms column types from table  <code>t</code> according to the columns contens of its first 10 rows by default. The number of rows to be taken into consideration can be adjusted by the optional parameter  <code>maxSample</code>. Complex column types are also covered.
", Documentation.Category = " Table 
", Documentation.Source = " local
", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com .
", Documentation.Examples = {[Description =  " Inspired by a solution from Marcel Beugelsdijk : http://www.thebiccountant.com/2017/08/10/table-transform-column-types-to-first-rows-types/ .
" , Code = " 
 ", Result = " 
"]}] 
 in 
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))