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.
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:
- Extract the last two characters from the source column
- When the extracted characters contain a number, convert them to numeric
- 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:
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.
BI Polar 
Nothing fancy but it works:
Value.Type(19) = type number
LikeLike
Now put it in context.
LikeLike
Pingback: Last Week Reading (2019-07-07) | SQLPlayer
Pingback: IsNumeric in Power Query – Curated SQL
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,
List.Select(
Table.ColumnNames( Source ),
each not Value.Is( Value.FromText( _ ), Date.Type )
), “Date”, “Amount” )
LikeLike
Pingback: Dataflows in Power BI – BI Polar
“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
LikeLike