Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Solved! Go to Solution.
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"
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! |
this should be valid answer for the first request:
Table.AddColumn(#"Modificato tipo", "Personalizzato", each Splitter.SplitTextByCharacterTransition( {"a".."z"}, {"0".."9"}) ([Colonna1]){1})
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
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:
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
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:
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
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
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 :
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
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
try this
Table.AddColumn(#"Modificato tipo", "Personalizzato", each Splitter.SplitTextByCharacterTransition( {"a".."z"}, {"0".."9"}) (Text.BeforeDelimiter([Colonna1], ".",1)){1})
this should be valid answer for the first request:
Table.AddColumn(#"Modificato tipo", "Personalizzato", each Splitter.SplitTextByCharacterTransition( {"a".."z"}, {"0".."9"}) ([Colonna1]){1})
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
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!
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"
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 @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
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