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

Question

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?

Well, it's not a number. You need to split off the zz characters before you can convert the value to a number. — teylyn
– teylyn, Commented Apr 10, 2016 at 22:10
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
– Behnam2016, Commented Apr 11, 2016 at 6:27

ImkeF · Accepted Answer · 2016-04-11 11:57:38Z

3

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 ).

answered Apr 11, 2016 at 11:57

ImkeF

1,5983 gold badges13 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related