Data type when adding column

[JuanSombrero]

Hi all,


usually, when I add a column, I make a second step to change the data type of the new column to the required type. Until I found out that the Table.AddColumn function actually has a final argument that lets you decide upon the data type without having to do the additional step. So as a final argument I insert now Int64.Type or type number. When doing so, and I select the column, the data type indicator in my ribbon actually displays 'whole number' or 'decimal number', but the actual data remain outlined to the left (like text). Also, I cannot do any mathematical calculation on it. So all of this implies the new column is actually text, even though the ribbon tells me differently.

What am I missing here?


Regards

Juan

 

[Dan Bliss]

Please provide details. Paste here the exact copy of the Table.AddColumn function expression (from Advanced editor is best) so we can see what you have done.

 

[MarcelBeug]

With the last argument of Table.AddColumn, you specify the data type of the new column, which is independent of the data type of the individual column values.

E.g. this will create a number column with text values:

= Table.AddColumn(Table, "Inserted Prefix", each "1" & [Column1], type number)

So it's up to you to guarantee that the values from the formula comply with the data type of the new column, otherwise you can get issues when loading the data.

It may be better to switch back to your original approach, as Table.TransformColumnTypes will actually convert the individual values to the new type (and return errors if such conversion is not possible).

 

[Dan Bliss]

Exploring AddColumn optional parameter columnType

Hi Marcel,
Any thoughts on why "Inserted Prefix" does not show error values? Using my sample table below, if [Column1]{0} = "Cell1" then [Inserted Prefix]{0} = "1Cell1", which cannot be readily cast to type number.

let    
    Source = #table({"Column1","Column2"},{{"Cell1",4},{"Cell2",5},{"Cell3",6}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    Beug1 = Table.AddColumn(#"Changed Type", "Inserted Prefix", each "1" & [Column1], type number),
    TestNumberType = Table.ColumnsOfType(Beug1,{type number})
in
    TestNumberType

Result: "Inserted Prefix"

Last step added to verify column type. But if "Inserted Prefix" as type number and the values in Step Beug1 are:
"1Cell1", "1Cell2", "1Cell3". why no errors?

Dan Bliss

 

[MarcelBeug]

Actually that is exactly what I tried to explain: with the last argument of Table.AddColumn, the data type of the new column is determined, which doesn't mean that the individual values in that column must be of the same type. So you won't get errors if the output of your formula is text and the column type is number: you can have text values in a column that has data type number.
You would get errors, if you would add a step after "Beug1" in which you change the column type to number, as Table.TransformColumnTypes will actually try to convert the values to numbers.

You may want to check section 5.8 of Microsoft's Power Query Formula Reference about "ascribed types".

Is it strange and confusing? Yes, it is!

I even reported an issue some time ago, because code that was generated from a Power Query User Interface option, created a date or datetimezone value, while the column type was set to datetime. This wouldn't display errors in Power Query, but it would lead to errors when loading data into the data model.
In the meantime, this issue is solved, but it is still possible to create values of one type in a new column with another type. This is by design.

 

[MarcelBeug]

Additional notes: if you remove the last step from the code in post #4, then you'll hit another strange phenomenon: when loading the result into the datamodel, 3 errors are reported (because of the different data types) and a link is provided to display the errors. If you follow the link, you get an empty table.

You can watch it happen in this video.