Conditional column with different datatype

[BishwaR]

I am creating a conditional column on numeric  column but the out with a String.

If  Numeric Column A is < 0 then output N/A.

When I tried it it creates the column but then while saving the query it return error datatype mismatch

Please help

Thanks

[v-yingjl]

Hi @BishwaR ,

You can create the custom column like this:

= Table.AddColumn(#"Changed Type", "Custom", each if [Column1] < 0 then "N/A" else [Column1])

The default data type of this column would be 'Any' that includes multiple data types.

Note: 'Any' Type can only be applied and used in power query to do some other queries, when close power query, the data type of the column would be changed to Text. Power BI Table view does not allow multiple data types in one column at the same time.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jVUitWJVgKSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] < 0 then "N/A" else Text.From([Column1]))
in
    #"Added Custom"

[Anonymous · Accepted solution]

In the formula bar, type:

= Table.AddColumn(NameOfPriorStep, each if [Column A] < 0 then "N/A" else [Column A], type text)

--Nate