Forum Discussion

BishwaR's avatar
BishwaR
Helper V
5 years ago
Solved

Conditional column with different datatype

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

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    In the formula bar, type:

     

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

     

    --Nate

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    In the formula bar, type:

     

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

     

    --Nate

  • smpa01's avatar
    smpa01
    Community Champion
    5 years ago
    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"
  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    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.