Table.AddColumn(.., .., each _text expression_), type number) doesn't convert text expression

[andreyminakov]

In Excel PQ, Table.AddColumn(.., .., each _text expression_), type number) doesn't convert text expression to number, while the header of the column shows that it has a numeric type. And, if you merge a table with such a column with a table with "pure" numeric column, UI doesn't prevent from doing that, and at the same time the merging provides only nulls in the "right" table for left-outer joins.

So there is the issue is that the type of a column is numeric, and values kept it it are text. That doesn't seem to be logical, and cirtain checks doesn't perfrorm properly, as described above.

[andreyminakov]

The right bracket here "expression_)" should be deleted 😉

[v-yuezhe-msft]

 @andreyminakov,

What is your text expression? Could you please share sample data of your table and post the text expression here? If you want to change the column to whole number, you should use able.AddColumn(.., .., each _text expression, Int64.Type) . There is an example for your reference.



Regards,
Lydia

[andreyminakov]

Lydia, thanks for your answer!

Here is the code with shows the problem:

let
    Source = #table({"text", "number", "number of text type"}, 
	{{"a",1,4},{"b",2,5},{"b",3,6}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,
	{{"number of text type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", 
	"number Int64.Type ?", each [number of text type], Int64.Type),
    #"Added Custom3" = Table.AddColumn(#"Added Custom", 
	"Type of ""Int64.Type"" Column is still text", 
	each Type.Is(Value.Type([#"number Int64.Type ?"]), type text)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom3", "Sum With Number", 
	each (try [#"number Int64.Type ?"] + [number])[Error][Message]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Merging with text", 
	each [#"number Int64.Type ?"] & [text])
in
    #"Added Custom2"

The situation is the same for PowerBI Desktop and Excel:

[andreyminakov]

Something strange happened - I don't see my comment here, so I repeat it again. The problem is that if I set a type of added column, the type applying doesn't take place on the values. The type is just shown in the header in UI, but the values are still of the original type. This is the code which demonstrates the problem:

let
    Source = #table({"text", "number", "number of text type"}, 
	{{"a",1,4},{"b",2,5},{"b",3,6}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,
	{{"number of text type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", 
	"number of Int64.Type ?", each [number of text type], Int64.Type),
    #"Added Custom3" = Table.AddColumn(#"Added Custom", 
	"Type of ""Int64.Type"" Column is still text", 
	each Type.Is(Value.Type([#"number of Int64.Type ?"]), type text)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom3", "Sum With Number", 
	each (try [#"number of Int64.Type ?"] + [number])[Error][Message]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Merging with text", 
	each [#"number of Int64.Type ?"] & [text])
in
    #"Added Custom2"

[v-yuezhe-msft]

@andreyminakov,

This behavior is by design according to PG's response in this similar thread. When you supply a type parameter to Table.AddColumn, you're asserting that you think the value will have that type but nothing enforces it. If you need the enforcement, you can either use "Number.From" in your formula or you can add a subsequent transformation step to type "Whole number".

let
    Source = #table({"text", "number", "number of text type"}, 
	{{"a",1,4},{"b",2,5},{"b",3,6}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,
	{{"number of text type", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "number of Int64.Type ?", each Number.From([number of text type]))
in
    #"Added Custom"

Regards,

Lydia

[andreyminakov]

Thanks a lot for your answer, Lydia!