Extract Numbers List from Strings in M (PowerQuery/PowerBI)

1
votes

I have a column of text values (named "Page") in Power Query, Each row is a combination of text/number separated by "-", like below: 

Page  
movie-batmanseries-240000-movie-100000xx-500 
345000zz-1.5-1000-starwars5-500000-game

Now I want to create a list of values for each row (separator "-") and then extract the first occurrence of a whole number (not decimal) into a new column

Here is what I wrote as the column formula:

List.First(List.Select(Text.Split([Page],"-"), each Number.IsNaN(_) = false))

But I'm receiving "Error" Value with this detail:

"We cannot convert the value "345000zz" to type Number."

Is there any workaround for this?

1
powerbipowerquery
Well, it's not a number. You need to split off the zz characters before you can convert the value to a number. – teylyn
But I want to skip any value which is combination of text & numbers (after splitting with "-" delimiter), keeping only numbers list and then choose the first list member – Behnam2016

1 Answers

3
votes

This should work:

List.First(List.Select(Text.Split([Page], "-"), each Number.Mod(try Number.From(_) otherwise null,1)=0))

As the Text.Split will return in text-format, you need to convert to number before checking if it is a whole number ((Number.Mod,1)=0). In addition to that you need to cater for the errors returned if the transformation to number cannot be made (try ... otherwise ).