Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jbruewer
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
CNENFRNL
Community Champion
Community Champion

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

Anonymous
Not applicable

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

AlB
Super User
Super User

@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
AlB
Super User
Super User

@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

 

 

AlB
Super User
Super User

@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

 

 

Anonymous
Not applicable

try this

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

this should be valid answer for the first request:

 

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

 

 

@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

Thanks for compiling this solution, it works.

 

The difference between my first approach and this one is the curly bracked {1}. Can you be so kind and share the link what this {1} is doing?

 

Thanks you so much!

CNENFRNL
Community Champion
Community Champion

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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

Hi @CNENFRNL ,

 

your solution works perfect for me!

 

Thanks a lot!

 

//joerg

AlB
Super User
Super User

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors