cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Search for number in string or use of regex

I've the challange that i got a string that contains the release number like:

 

core 2.4.23-RC1

hummer 3.0.23-IT1

crasher 1.0.2-RC2

 

I am looking forward to use a measure to extract the release number for further work. My current approach looks like this:

 

 
---------------------------------------------------------------
Release =
var releaseX = SWITCH( TRUE(),
IFERROR(SEARCH("3", [AffectedVersion]),0) > 0 , MID([AffectedVersion],SEARCH("3", [AffectedVersion]),6),
IFERROR(SEARCH("2", [AffectedVersion]),0) > 0 , MID([AffectedVersion],SEARCH("2", [AffectedVersion]),6),
IFERROR(SEARCH("1", [AffectedVersion]),0) > 0 , MID([AffectedVersion],SEARCH("1", [AffectedVersion]),6),
IFERROR(SEARCH("0", [AffectedVersion]),0) > 0 , MID([AffectedVersion],SEARCH("0", [AffectedVersion]),6),
[AffectedVersion])

return SWITCH ( TRUE(),
jira_bug_and_req[proj_key] = "SIMS", releaseX,
... do something else...
)
 
---------------------------------------------------------------
As you might directly see, the issue is that always the 3 is the first character that get noticed... in case of "core 2.3.1..." the extracted value would be 3.1... and not 2.3.1... 
I need a kind of regex that filter the first occuring character.
 
Does someone has a great solution for me 🙂
Thanks in advance!
4 ACCEPTED SOLUTIONS

Accepted Solutions
Resident Rockstar
Resident Rockstar

Hi, @jbruewer , Power Query is way much competent than DAX in such a data extract/cleansing job. You might want to try sucha a pattern,

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JY2xDoMwDER/xcoMFk7SSsydWCu2iCGKXGUopLJB/X0SWN+9uwvBpCIMFj1a179fZJYumHysK4vDocFpvmGSqJkFqOGq2otu/K9th77XHxGUvSp6JcpJeIfyAYfk6THaJwh/OWr7G5DujeUE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Version = _t]),

    #"Release Extract" = Table.AddColumn(Source, "Release",
        each [
                num = List.First(List.Select(Text.SplitAny([Version], Text.Remove([Version], {"0".."9",".","-"})), each Text.PositionOf(_,".",2){1}?<>null)),
                substr = Text.Range([Version], Text.PositionOf([Version], num)),
                release = List.First(Text.Split(substr, " "))
        ][release]
    )
in
    #"Release Extract"

 

 

Screenshot 2020-10-26 230525.png

View solution in original post

this should be valid answer for the first request:

 

 Table.AddColumn(#"Modificato tipo", "Personalizzato", each Splitter.SplitTextByCharacterTransition( {"a".."z"},  {"0".."9"}) ([Colonna1]){1})

 

 

View solution in original post

@jbruewer 

I'm getting a bit confused. I think you should be able to do it with what's been shared so far. Create a custom column with this code:

= 
let
    res1 = Text.Range([Version], Text.PositionOfAny([Version],{"0".."9"})),
    res2 = Text.Start(res1, Text.PositionOf(res1,".",2){1})
in 
    res2

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

View solution in original post

Super User II
Super User II

@jbruewer 

Like I said before, it looks like if the third argument to the function Text.PositionOf(  ,  ,  ) is greater than 1, the function will return a list with the positions of all occurrences.  This is certainly not clear from the function description in the documentation, though. For instance,  

Text.PositionOf( "3.0.23RC11",  ".",  2 )

will return this list:

image.png

 which is the position in the string of the first "." and the position of the second ".". Since we want the second, you have to access the second item on the list, i.e., the item at index 1 on the list. That is why you need the {1}:

Text.PositionOf( "3.0.23RC11",  ".",  2 ){1}

Play a bit with the function yourself to see how it works

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

 

View solution in original post

14 REPLIES 14
Super User II
Super User II

Hi @jbruewer

1. What is the expected result for each of the examples you show?

core 2.4.23-RC1

hummer 3.0.23-IT1

crasher 1.0.2-RC2

2. You talk about a measure. Where are you going to use this? Is in not in a table with many of these cases in the rows? You sure it's not a calculated column what you need?

3.  Do you want this in DAX or Power Query (the latter would probably be better, depending in what the ultimate objective is)

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Resident Rockstar
Resident Rockstar

Hi, @jbruewer , Power Query is way much competent than DAX in such a data extract/cleansing job. You might want to try sucha a pattern,

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("JY2xDoMwDER/xcoMFk7SSsydWCu2iCGKXGUopLJB/X0SWN+9uwvBpCIMFj1a179fZJYumHysK4vDocFpvmGSqJkFqOGq2otu/K9th77XHxGUvSp6JcpJeIfyAYfk6THaJwh/OWr7G5DujeUE", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Version = _t]),

    #"Release Extract" = Table.AddColumn(Source, "Release",
        each [
                num = List.First(List.Select(Text.SplitAny([Version], Text.Remove([Version], {"0".."9",".","-"})), each Text.PositionOf(_,".",2){1}?<>null)),
                substr = Text.Range([Version], Text.PositionOf([Version], num)),
                release = List.First(Text.Split(substr, " "))
        ][release]
    )
in
    #"Release Extract"

 

 

Screenshot 2020-10-26 230525.png

View solution in original post

Hi @AlB ,

 

thanks for your valid quations, here my answers:

 

1. expected results:

core 2.4.23-RC1 --> result: 2.4.23-RC1

 

2. Currently i try out to find a solution as measure but could also be an additional column i update after refresh.

 

3. I don't care if the solution is  DAX or Power Query. If i could get support on both, i would choose the most simple one.

 

regards

joerg

Hi @CNENFRNL ,

 

your solution works perfect for me!

 

Thanks a lot!

 

//joerg

Hi CNENFRNL,

 

thanks for your provided solution! I used it already and understand now better how it works. 

At least i am struggle to do the next step and wonder if you can support here as well. 

I used your example to exctract the release candidates. But now i like to groupe  some together like:

 

hummer3.0.29-RC1

Core3.0.29-RC1

hummer 3.0.29-RC2

hummer3.0.29-RC3

Core3.0.29-IT1

 

my expected results would be just:

3.0.29

 

Would be absolutly great to get some advice

//joerg

Super User II
Super User II

@jbruewer 

Since you already have the column that extracts the release, i.e, 

    3.0.29-RC1    from     hummer3.0.29-RC1 

you can do this last step easily by adding a custom column with this code:

 

=Text.Start([Release], Text.PositionOf([Release],"-"))

 

or you also can apply the above as a last step when creating the Release column

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

 

Thanks AIB for sharing this solution... i've a last question on this.

 

Taking this example:

hummer3.0.29-RC1 

 

=Text.Start([Release], Text.PositionOf([Release],"-",2))

 

I understood that the third parameter on Text.PositionOf can be used to find the second occurance of a character. In my experience it didn't work. Do i am doing wrong?

Can i substruct the "3.0" by :

 

 

@jbruewer 

Yeah it looks like if that third argument is >=2 the function will return a list with all occurrences. Then you can pick the one you are interested in from the list.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

HI AIB,

 

i guess it's my fault... i didn't explain exactly what i am aiming for. Let me try again by hope to get your experienced support.

 

I've this as input:

hummer3.0.29-RC1

hummer3.0.29-IT1

fischi3.0.1.29-RC3

fischi3.0..1-RC3

 

1. I like to ignore the initial release nick name like "fischi" or "hummer"

2. I nee to ignore the endings like -RC1 or -RC3 e.g.

3. Minor release number hase to be cut of like 3.0.xxxxx (cut the xxxx..)

 

I am only need to extract 3.0

Rule:  

Start by the first number "0"..."9"

End before the second "."

 

I was able to use your code snippets but struggle to get this " End before the second "."".

 

The shared function: 

Text.PositionOf([Version],".",2)

Searches for the second "." starting at the end. What result in examples like:

hummer3.0.23-RC1 --->result: "3" and not "3.0"

 

I need this by star counting from the bedinnging. 

Would be great to get some proposals from you experts.

 

//joerg

 

 

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors