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

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