Quick Tip: Implementing “IsNumeric” in Power Query

I’ve had many people tell me that they have a blog so they will have a place to look to find the information that they need, and answers to their own future questions. This isn’t typically the case for me… but this post is an exception.

Every few months I find myself searching for an IsNumeric function[1] in Power Query. Each time I end up spending a few frustrating hours before I find the solution I’m looking for, because the search terms I’m using aren’t how people who have already solved the problem have shared it.

Last night, when I was once again failing to find the solution I needed I reached out to Twitter for help, and Imke Feldman delivered:.

If you’re not sure how you’d use this solution, consider the problem I was working on last night. My source data includes a “release-date” column that can include a wide range of values, and because I do not have any influence over the source system, I need to work with what it contains. Specifically, I need to find records that contain a year, and I need to extract that year into a new numeric column.

01 data

There are a few more pattern variations in the data including two-digit years, but this sample shows the basic problem I need to solve. And in order to solve it, I need to be able to determine if the two characters at the end of the text represent a number.

Using Imke’s approach, this is what I ended up doing:

  1. Extract the last two characters from the source column
  2. When the extracted characters contain a number, convert them to numeric
  3. Standardize the resulting value to represent the correct year

This last step is specific to my particular “date as year encoded in different text formats” problem, but I’m including it here in case someone else may find it useful.

It looks like this:

02 solution

Thanks to Imke for her quick response. Hopefully there are people out there other than me who will find this useful… but I know that I will.

[1] This is how I think of a function that accepts a text value returns true if that value contains a number, and false if it does not. This is probably because of my decades-long love affair with Transact-SQL, since T-SQL has an ISNUMERIC function built in.

7 thoughts on “Quick Tip: Implementing “IsNumeric” in Power Query

  1. Pingback: Last Week Reading (2019-07-07) | SQLPlayer

  2. Pingback: IsNumeric in Power Query – Curated SQL

  3. Value.Is(Value.FromText([Column1]), Number.Type)

    It’s useful in more advanced techniques like a dynamic unpivot of a crosstab report:

    #”Dynamic Unpivot” =
    Table.UnpivotOtherColumns( Source,
    Table.ColumnNames( Source ),
    each not Value.Is( Value.FromText( _ ), Date.Type )
    ), “Date”, “Amount” )


  4. Pingback: Dataflows in Power BI – BI Polar

  5. Bill Szysz

    “Every few months I find myself searching for an IsNumeric function[1] in Power Query.”
    Nothing hard ;-))

    try Number.From([YourTextValue]) is number otherwise false

    The above can also replace Imke’s solution


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s