New M-function: Table.TransformColumnTypesToFirstRowsTypes for PowerBI and PowerQuery

The following function automatically transforms all columns to the types that have been detected in the cells of its first row. Provided they come as: Number, date or text (but you can add additional type conversions if you need them.).

It also has some rough edges: If the first value is empty, the column will be converted to text. Also, it contains the (improved) logic from this article:  So if a date is written in a way that it could also be a number, then it will be converted as a number. To minimize the room for errors here, I’ve converted the values to text first, but this is still something to watch out for. But in very many cases it will just do what you have long been looking for:

Use cases:

  • You don’t want to use the automatic but static/hard coded type-conversion in the 2nd step (because you know you’re table is going to have more columns in the future and you want to cater for proper type-conversion of them as well)
  • You’ve lost your column types due to some other command (like Table.ReplaceValues)

M-Code

Code to download: TableColumnTypesToFirstRowsTypes.txt

 

Enjoy & stay queryious 😉

Comments (2) Write a comment

  1. My suggestion would be to use the actual value types.

    Below 2 functions:
    1 using Table.TransformColumnTypes, which has some limitations with regard to the supported types.
    1 complex function using Value.ReplaceType and a workaround via a typed record to apply the new types.

    The latter works fine, even with a complex table as:
    = #table(null,
    {{1,#date(2017,1,1),”Imke”,#duration(1,2,3,4), () => “Booh”, type text, #table(0,{})},
    {2,#date(2017,1,2),[Name = “Marcel”, SurName = “Beugelsdijk”],#duration(2,4,6,8), () => “Mooh”, type number, #table(0,{})}})

    Function fnTableTransformColumnTypes:

    (Table as table, optional MaxSample as number) =>
    let
    Sample = if MaxSample = null then Table else Table.FirstN(Table,MaxSample),
    Types = Table.TransformColumns(Sample,{},Value.Type),
    ColumnTypes = Table.ToColumns(Types),
    DistinctTypes = List.Transform(ColumnTypes, List.Distinct),
    NewTypes = List.Transform(DistinctTypes, each if List.Count(_) = 1 then _{0} else type any),
    Typed = Table.TransformColumnTypes(Table,List.Zip({Table.ColumnNames(Table), NewTypes}))
    in
    Typed

    Function fnTableReplaceColumnTypes:

    (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 else Table.FirstN(Table,MaxSample),
    ColumnNames = Table.ColumnNames(Sample),
    Types = Table.TransformColumns(Sample,{},Value.Type),
    ColumnTypes = Table.ToColumns(Types),
    DistinctTypes = List.Transform(ColumnTypes, List.Distinct),
    NewTypes = List.Transform(DistinctTypes, 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.”,
    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

    Reply

    • Hi Marcel,
      welcome to my blog!
      Your solutions are very smart and more robust than my approach, so thanks a lot!!
      Cheers – Imke

      Reply

Leave a Reply