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 😉
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
Hi Marcel,
welcome to my blog!
Your solutions are very smart and more robust than my approach, so thanks a lot!!
Cheers – Imke
Hi Marcel,
I have adjusted your functions so that columns with null values in them but otherwise unique values will be transformed to those (instead of any) and combined them into one for better handling here: https://gist.github.com/ImkeF/6af3d67c91b81d9eb0adceba0261a252
Wondering if this is still “state of the art” or if you have a better method meanwhile for the transformation of the complex types?
Thanks again and cheers, Imke
Pingback: Retaining column types in Power Query – Dingbat Data
Pingback: Dynamic Data Types in Power Query - Goodly
Dear Imke,
My table appears to be holding all data as text, although there are columns with dates and values. When I apply the Detect Data Type (Transform tab) it does the job properly.
Notwithstanding, I need this step to be performed dynamically because the table changes shape and size every time.
I tried the functions posted above without success.
If you take an interest in this my data is here: https://drive.google.com/file/d/1cHZsszVlwsXIeC3AjxgMemjd-leGDqH0/view?usp=sharing
Thank you,
G
Actually it does not work if a get the data From Folder (when I use from Table it works fine)
And a reply to why it did not work here: https://stackoverflow.com/questions/77918042/power-query-detect-data-type-dynamically?noredirect=1#comment137369833_77918042