none
Type conversion mysteries

    Question

  • Hi there,

    Wondering if some type conversion operations are actually buggy or what the idea behind their design is:

    1)      Table.ExpandTable column “deletes” all existing column types if the type of the column isn’t table (see Chris Webb’s article here for example: https://blog.crossjoin.co.uk/2017/09/25/setting-data-types-on-nested-tables-in-m/ ) . But when I use Table.Combine(Table[TableColumn]) the formats are kept. Why is that?

    let
        Source = Table.FromColumns({{"A","B"}, List.Repeat({#table(type table[Col1=text, Col2=number], {{"1",1}, {"2", 2}})},2)}),
        Custom1 = Table.Combine(Source[Column2])
    in
        Custom1


    2)      If I want to change the type of an existing column that contains tables into type table, Table.TransformColumnTypes returns an error, while using Table.TransformColumns works with the same command. Why does the error message occur?:

    returns error:

    let
        Source = Table.FromColumns({{"A","B"}, List.Repeat({#table(type table[Col1=text, Col2=number], {{"1",1}, {"2", 2}})},2)}),
        Custom1 = Table.TransformColumnTypes(Source, {"Column2", type table [Col1=text, Col2=number]})
    in
        Custom1


    3)      Actually, to make Table.TransformColumns work, one has to divide and re-assemble the tables like this: Table.FromColumns(Table.ToColumns(_), Table.ColumnNames(_)) instead of just using _ : Why are both expressions not treated the same?

    works:

    let
        Source = Table.FromColumns({{"A","B"}, List.Repeat({#table(type table[Col1=text, Col2=number], {{"1",1}, {"2", 2}})},2)}),
        Custom1 = Table.TransformColumns(Source, {"Column2", each Table.FromColumns(Table.ToColumns(_), Table.ColumnNames(_)), type table [Col1=text, Col2=number]}),
        #"Expanded Column2" = Table.ExpandTableColumn(Custom1, "Column2", {"Col1", "Col2"}, {"Col1", "Col2"})
    in
        #"Expanded Column2"

    doesn't work (return with col types):

    let
        Source = Table.FromColumns({{"A","B"}, List.Repeat({#table(type table[Col1=text, Col2=number], {{"1",1}, {"2", 2}})},2)}),
        Custom1 = Table.TransformColumns(Source, {"Column2", each _, type table [Col1=text, Col2=number]}),
        #"Expanded Column2" = Table.ExpandTableColumn(Custom1, "Column2", {"Col1", "Col2"}, {"Col1", "Col2"})
    in
        #"Expanded Column2"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Tuesday, September 26, 2017 9:40 AM
    Moderator

Answers

  • Table.TransformColumnTypes only works with primitive types. We have no notion of enforcing or coercing complex types.

    The "each _" thing looks like a bug to me, but I haven't figured out yet why it happens.

    The reason your last example doesn't work is because Column2 has only been assigned the generic type "table" and not a more specific type. This would work:

    let
        Source = Table.FromColumns({{"A","B"}, List.Repeat({#table(type table[Col1=text, Col2=number], {{"1",1}, {"2", 2}})},2)}, type table [Column1=text, Column2=table[Col1=text, Col2=number]]),
        #"Expanded Column2" = Table.ExpandTableColumn(Source, "Column2", {"Col1", "Col2"}, {"Col1", "Col2"})
    in
        #"Expanded Column2"

    Tuesday, September 26, 2017 8:03 PM
    Owner

All replies

  • Just some comments from my side (I can't provide answers to your "Why's").

    Actually I already commented on Chris' blog (it is currently awaiting moderation).

    Interesting to see attention for 'types" from 3 sides: Reza Rad, Chris Webb and now from you.

    1) Table.ExpandTableColumn will keep the table types only if the column has the same type as the nested tables (not just type table). I think the reason behind this is that the column can have nested tables of different types.

    2) Table.TransformColumnTypes performs actual conversions (e.g. if you change the type of a column with decimals to whole numbers, the values will be adjusted). So my interpretation is that this function should primarily be regarded as a conversion function, and not primarily as a function that "just" changes column types (contrary to what the function name suggests...).

    3) I would use "Value.Type" in your first code example (under #3) so you don't need to define your table type twice.

    let
        Source = Table.FromColumns({{"A","B"}, List.Repeat({#table(type table[Col1=text, Col2=number], {{"1",1}, {"2", 2}})},2)}),
        Custom1 = Table.TransformColumns(Source, {"Column2", each Table.FromColumns(Table.ToColumns(_), Table.ColumnNames(_)), Value.Type(Source[Column2]{0})}),
        #"Expanded Column2" = Table.ExpandTableColumn(Custom1, "Column2", {"Col1", "Col2"}, {"Col1", "Col2"})
    in
        #"Expanded Column2"

    No need to divide and re-assemble in your second example. Just do "something neutral" with the value, to have the type applied (each _ is not enough, something silly like each {_){0) will do).

    let
        Source = Table.FromColumns({{"A","B"}, List.Repeat({#table(type table[Col1=text, Col2=number], {{"1",1}, {"2", 2}})},2)}),
        Custom1 = Table.TransformColumns(Source, {"Column2", each {_}{0}, Value.Type(Source[Column2]{0})}),
        #"Expanded Column2" = Table.ExpandTableColumn(Custom1, "Column2", {"Col1", "Col2"}, {"Col1", "Col2"})
    in
        #"Expanded Column2"

    Tuesday, September 26, 2017 10:38 AM
  • Thanks Marcel for the interesting insights:

    3)

    Using Value.Type is nice as it makes the solution dynamic.

    The shortcut {_}{0} also helpful but emphasises my question: Why _ is not enough by itself?

    2)

    Good explanation of what the command actually does, but still strange that is works on the first column:

    Table.TransformColumnTypes(Source, {"Column1", type text})

    but not on the second: Why is the type table not recognized, while type text is?

    1) Good catch. But do you have any idea why this doesn't work then?:

    let
        Source = Table.FromColumns({{"A","B"}, List.Repeat({#table(type table[Col1=text, Col2=number], {{"1",1}, {"2", 2}})},2)}, type table [Column1=text, Column2=table]),
        #"Expanded Column2" = Table.ExpandTableColumn(Source, "Column2", {"Col1", "Col2"}, {"Col1", "Col2"})
    in
        #"Expanded Column2"

    And still: I would very thankful if someone could provide an explanation of this behaviour, as this could either help to understand the M-syntax better or just know about some inconsistencies that should be regarded as exemptions from the rules.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, September 26, 2017 1:57 PM
    Moderator
  • Table.TransformColumnTypes only works with primitive types. We have no notion of enforcing or coercing complex types.

    The "each _" thing looks like a bug to me, but I haven't figured out yet why it happens.

    The reason your last example doesn't work is because Column2 has only been assigned the generic type "table" and not a more specific type. This would work:

    let
        Source = Table.FromColumns({{"A","B"}, List.Repeat({#table(type table[Col1=text, Col2=number], {{"1",1}, {"2", 2}})},2)}, type table [Column1=text, Column2=table[Col1=text, Col2=number]]),
        #"Expanded Column2" = Table.ExpandTableColumn(Source, "Column2", {"Col1", "Col2"}, {"Col1", "Col2"})
    in
        #"Expanded Column2"

    Tuesday, September 26, 2017 8:03 PM
    Owner
  • Thanks Curt, very helpful!

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, September 26, 2017 9:04 PM
    Moderator
  • "Table.TransformColumnTypes only works with primitive types."

    This is news to me. The documentation doesn't describe types like Int64.Type, Percent.Type, etc. as primitive types . In fact, the documentation says nothing at all about these "subtypes"(?). How do we classify these types (given that they apply to table columns only).

    Wednesday, September 27, 2017 2:26 AM
  • "Table.TransformColumnTypes performs actual conversions (e.g. if you change the type of a column with decimals to whole numbers, the values will be adjusted). So my interpretation is that this function should primarily be regarded as a conversion function, and not primarily as a function that "just" changes column types (contrary to what the function name suggests..."

    1) Perhaps the function should have been named "Table.ConvertColumnTypes". However, "Transform" could be interpreted the same as "Convert," even though the term is less clear.

    2) How does one "just" change a column type from one type to another, without also performing a conversion?

    Wednesday, September 27, 2017 3:34 AM
  • 2) How does one "just" change a column type from one type to another, without also performing a conversion?

    Delete all data from the table, transform the column types * and then replace the type of the original table with the type of the empty table:
    Value.ReplaceType(OriginalTable,Value.Type(EmptyTable))

    * Alternatively, if Table.TransformColumnTypes doesn't support the new type, then you can add a new column to the empty table and replace the original column with the new column (delete old column, rename and move new column)


    • Edited by MarcelBeug Wednesday, September 27, 2017 5:43 AM typo
    Wednesday, September 27, 2017 5:32 AM
  • "Delete all data from the table, transform the column types * and then replace the type of the original table with the type of the empty table:
    Value.ReplaceType(OriginalTable,Value.Type(EmptyTable))"

    Therefore, would you agree that Table.TransformColumnTypes must imply type conversion and cannot possibly imply just changing one column type to another, since you don't delete all of the data in the table before applying the function?

    Wednesday, September 27, 2017 7:03 AM
  • Sure, agreed.
    Wednesday, September 27, 2017 8:12 AM
  • The documentation doesn't describe types like Int64.Type, Percent.Type, etc. as primitive types.

    Int64.Type and Percentage.Type are really subtypes or facets of type number. I'm not sure what the documentation says exactly; I assumed it was clear what I meant by "primitive types".

    Perhaps the function should have been named "Table.ConvertColumnTypes". However, "Transform" could be interpreted the same as "Convert," even though the term is less clear.

    We're pretty consistent about "Transform" meaning "change" in other functions like Table.TransformColumns, Table.TransformColumnNames and List.Transform.

    How does one "just" change a column type from one type to another, without also performing a conversion?

    You just assign a new type:

    let
        table1 = #table(type table [C1=any, C2=any], {{"A", 1}, {"B", 2}}),
        table2 = Value.ReplaceType(table1, type table [C1=text, C2=number]),
        table3 = Value.ReplaceType(table2, type table [C1=any, C2=Int32.Type])
    in
        table3

    Look at the column types of each table and see how they change. This is without changing the underlying data one bit.

    Wednesday, September 27, 2017 12:11 PM
    Owner
  • How does one "just" change a column type from one type to another, without also performing a conversion?

    You just assign a new type:

    let
        table1 = #table(type table [C1=any, C2=any], {{"A", 1}, {"B", 2}}),
        table2 = Value.ReplaceType(table1, type table [C1=text, C2=number]),
        table3 = Value.ReplaceType(table2, type table [C1=any, C2=Int32.Type])
    in
        table3

    Actually this replaces the entire table type, not the type of just 1 column.
    See one of my previous posts for a workaround to replace 1 column type (using the same principle of replacing the table type).

    Wednesday, September 27, 2017 2:46 PM
  • let
        Table.ModifyColumnType = (table as table, updatedColumns as type) as table =>
            let
                original = Type.RecordFields(Type.TableRow(Value.Type(table))),
                changes = Type.RecordFields(Type.TableRow(updatedColumns)),
                amended = Record.ReorderFields(original & changes, Record.FieldNames(original)),
                newType = type table Type.ForRecord(amended, false)
            in
                Value.ReplaceType(table, newType),
        table1 = #table(type table [C1, C2], {{"A", 1}, {"B", 2}}),
        table2 = Table.ModifyColumnType(table1, type table [C2=Int32.Type]),
        table3 = Table.ModifyColumnType(table2, type table [C1=text])
    in
        table3

    Note that this will lose the keys on the table type. Fixing this is left as an exercise for the reader.

    Wednesday, September 27, 2017 3:59 PM
    Owner
  • "Int64.Type and Percentage.Type are really subtypes or facets of type number. I'm not sure what the documentation says exactly; I assumed it was clear what I meant by "primitive types"."

    The documentation (at least the formula language documentation - where this should be discussed) is silent about subtypes (if that's the official name). Matt Masson refers to these X.Types as "formats", and refer to them as common examples of ascribed types - confusing, rather than clarifying the issue.

    It wasn't clear that when you said that "Table.TransformColumnTypes only works with primitive types" you were including the subtypes, since Table.TransformColumnTypes works with these subtypes, and the documentation doesn't list these as primitive types. Indeed, unlike "true" primitive types, these subtypes have no scope beyond table columns e.g. can't be used in function declarations, and cannot be used to replace a type in anything other than a table column.

    "You just assign a new type:..."

    Ah yes, thank you. However, your example negates the argument that "transform" has to mean "convert," since it could simply mean transform from one type to another without conversion, as you have aptly demonstrated. Marcel's statement that "Table.TransformColumnTypes performs actual conversions"  therefore has merits, and reinforces the notion (for the record) that the function should have been called "Table.ConvertColumnTypes" to remove all doubt about what is actually occurring. Lastly, I am left wondering why one would assign an initial type, or replace the type of a column when there is no apparent benefit to doing so, e.g. assign an Int64.Type to a column full of decimal numbers, given that the values in the column remain intact.

    I very much appreciate your response, Curt. If I appear to be nitpicking, it's not my intention. If anything, it reflects the frustration that I sometimes experience with a topic that has never been 100% clear to me. :)

    Wednesday, September 27, 2017 4:56 PM
  • Source: https://github.com/Microsoft/DataConnectors/tree/master/samples/TripPin/7-AdvancedSchema

    Here is a quick refresher about types in the M language from the Language Specification:

    A type value is a value that classifies other values. A value that is classified by a type is said to conform to that type. The M type system consists of the following kinds of types:

    • Primitive types, which classify primitive values (binary, date, datetime, datetimezone, duration, list, logical, null, number, record, text, time, type) and also include a number of abstract types (function, table, any, and none)
    • Record types, which classify record values based on field names and value types
    • List types, which classify lists using a single item base type
    • Function types, which classify function values based on the types of their parameters and return values
    • Table types, which classify table values based on column names, column types, and keys
    • Nullable types, which classifies the value null in addition to all the values classified by a base type
    • Type types, which classify values that are types

    Thursday, September 28, 2017 8:49 AM
  • Thank you guys for bringing so many details to light here.

    One last question to @Curt: Just to confirm: What you called as "generic type" is the same than the "abstract types" as a subgroup of the primitive types from the definition that anthony34 has quoted here? Or is this yet another attribute?


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, October 1, 2017 7:28 AM
    Moderator
  • Hi Imke,

    The term "generic type" is not defined in the documentation, but in the given context, it most likely means "primitive type."

    Note that the bulleted list of types after the primary type are custom types ("All types that are not members of the closed set of primitive types are collectively referred to as custom types.")

    Some of the primitive types are "abstract types" (as per explanation in the documentation).

    So "type table" is a primitive (generic) table type, but "type table [Col1=text, Col2=number]" is a custom (complex) table type. Hence the statement "Table.TransformColumnTypes only works with primitive types. We have no notion of enforcing or coercing complex types."

    Frankly, other than custom table types, I have yet to see any practical use of the other custom types.

    Monday, October 2, 2017 4:43 AM
  • "I have yet to see any practical use of the other custom types."

    Custom types are typically used to influence function UI behavior, specifically by adding a metadata record with fields like:

    Documentation.AllowedValues
    Documentation.ExampleValues
    Documentation.FieldCaption

    For example custom type Occurrence.Type has 3 allowed values which show up as a dropdown list when invoking function Text.PositionOf.
    Those values, in turn, have also metadata, so you don't just see the values in the dropdown list, but their names, e.g. Occurrene.Last is just the value 1 with metadata Documentation.Name.

    See picture of screenshots below (in the bottom screenshot I added the value of Occurrence.Last to have it displayed together with its metadata).

    Monday, October 2, 2017 5:29 AM
  • Hi Marcel,

    What I had in mind were custom types defined as such in the documentation:

    1) Custom list type, e.g. type {number}
    2) Custom record type, e.g. type [ Name = text, Age = number ]
    3) Custom function type, e.g. type function (x as text) as number

    Not suggesting that the above custom types are not useful - only that I've not come across any examples that are non-abstract and practical.

    I was not aware that Occurrence.Type came under the umbrella of custom types, since other X.Types are not considered as such ("Int64.Type and Percentage.Type are really subtypes or facets of type number"). Thus in my comment that you quote, X.Types never crossed my mind.

    Monday, October 2, 2017 3:06 PM
  • Nice questions and answers, nice thread. Somehow missed it.

    Maxim Zelensky Excel Inside

    Tuesday, October 3, 2017 3:52 PM