Is there an ISNUMBER() or ISTEXT() equivalent for Power Query?

Question

I have a column with mixed types of Number and Text and am trying to separate them into different columns using an if ... then ... else conditional. Is there an ISNUMBER() or ISTEXT equivalent for power query?

Answer 1

Here is how to check type in Excel Powerquery

IsNumber =Value.Is(Value.FromText([ColumnOfMixedValues]), type number)

IsText =Value.Is(Value.FromText([ColumnOfMixedValues]), type text)

hope it helps!

Answer 2

Put it in logical test format

Value.Type([Column1]) = type number

Value.Type([Column1]) = type text

The function Value.Type returns a type, so by putting it in equation thus return a true / false.

Also, equivalently,

Value.Type([Column1]) = Date.Type

Value.Type([Column1]) = Text.Type

HTH

Update

Here's the correct way

ISNUMBER() equivalent:

ISTEXT() equivalent:

ISDATE() equivalent:

You can also do on more complex type

All of these are explained in Value.Is

Answer 3

That depends a bit on the nature of the data and how it is originally encoded. Power Query is more strongly typed than Excel.

For example:

Source = Table.FromRecords({[A=1],[A="1"],[A="a"]})

Creates a table with three rows. The first row's data type is number. The second and third rows are both text. But the second row's text could be interpreted as a number.

The following is a query that creates two new columns showing if each row is a text or number type. The first column checks the data type. The second column attempts to guess the data type based on the value. The guessing code assumes everything that isn't a number is text.

Example Code

Edit: Borrowing from @AlejandroLopez-Lago-MSFT's comment for the interpreted type.

let
    Source = Table.FromRecords({[A=1],[A="1"],[A="a"]}),
    #"Added Custom" = Table.AddColumn(Source, "Type", each
      let
        TypeLookup = (inputType as type) as text =>
          Table.FromRecords(
            {
              [Type=type text, Value="Text"],
              [Type=type number, Value="Number"]
            }
        ){[Type=inputType]}[Value]
      in
        TypeLookup(Value.Type([A]))
    ),
    #"Added Custom 2" = Table.AddColumn(#"Added Custom", "Interpreted Type", each
      let
        result = try Number.From([A]) otherwise "Text",
        resultType = if result = "Text" then "Text" else "Number"
      in
        resultType
    )
in
    #"Added Custom 2"

Sample output

Answer 4

ISTEXT() doesn't exist in any language I've worked with - typically any numeric or date value can be converted to text so what would be a false result?

For ISNUMBER, I would solve this without any code by changing the Data Type to a number type e.g. Whole Number. Any rows that don't convert will show Error - you can then apply Replace Errors or Remove Errors to handle them.

Use Duplicate Column first if you don't want to disturb the original column.

Answer 5

I agree with Mike Honey. I have a SKU code that is a mix of Char and Num. Normally the last 8 Char are Numbers but in some weird circumstances the SKU is repeated with an additional letter but given the same EAN which causes chaos. by creating a new temp column using Text.End(SKU, 1) I get only the last character. I then convert that column to Whole Number. Any Error rows are then removed to leave only the rows I need. I then delete the temp Column and am left with the Rows I need in the format I started with.