Odd behaviour when setting datatype of custom column

[JohannesBlank]

I'm seeing some odd and inconsistent behaviour when it comes to setting a datatype while creating a custom column vs. creating the column, then changing the datatype.

Specifically, in the example below 

#"Added Custom" = Table.AddColumn(#"Changed Type1", "Rating_Num", each Text.Remove([#"Rating"],{"A",}), Number.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Rating_Num, type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Rating Cluster", each if [#"Rating_Num"] = 1 then "1" else if [#"Rating_Num"] <= 5 then "2 to 5" else "larger than 5")

I would expect the second step to be redundant, as the data type is already set in the first step. However, if I delete the second step, even after refreshing, the third step produces an error. And oddly, the second step is visually different

(after first / after second step, with a different column name than in the example above).

Why would that be? Does setting data type when creating a column even work?

[Anonymous · Accepted solution]

Hi @JohannesBlank ,

You can try this M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlCK1YlWMgSTRhC2I4QDoUwhlBmYNAdyYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Rating = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rating", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Rating_Num", each Number.From(Text.Remove([#"Rating"],{"A"}))),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Rating Cluster", each if [#"Rating_Num"] = 1 then "1" else if [#"Rating_Num"] <= 5 then "2 to 5" else "larger than 5")
in
    #"Added Conditional Column"

But I can't explain why specifying the column type as number in your code doesn't work, because in my test, the data type of the column is indeed displayed as number at the end of the addcolumn step, but Power Query still treats it as text.
This may help you:

Table.AddColumn - Table Function | Power Query M

It is recommended that if you want to change directly to a numeric type next time, use Number.From or Number.FromText.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bhanu_gautam]

@JohannesBlank , Try using below m code

m
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Rating_Num", each Text.Remove([#"Rating"],{"A",}), Number.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Rating_Num", type number}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Rating Cluster", each if [#"Rating_Num"] = 1 then "1" else if [#"Rating_Num"] <= 5 then "2 to 5" else "larger than 5")

[JohannesBlank]

Hi bhanu_gautam,

Isn't that the same code as I posted? I cannot see any difference. My goal would be to remove the second step, without producing errors in the third. The data looks like 1, 2, 1A, 2A etc., so the first step should both remove the letters as well as convert to number type.