topic Re: Search for text among numeric values in DAX Commands and Tips

Search for text among numeric values

DimaMD — Tue, 24 May 2022 07:56:31 GMT

Hello community.

I need to solve one task. In collum have data that starts with numbers, then we have text, then again numbers, the goal is to select the text between two numbers and to insert it into seperate collum.

For example we have: 000235123 Example text between numbers 234234424

The goal is that we need to select only text between numbers from this collum and insert into seperate one. Result should be: Example text between numbers

We cannot do this in power querry, because increment update will not work, so we need to use some kind of dax function.

Thank you in advance!

Re: Search for text among numeric values

tamerj1 — Tue, 24 May 2022 08:50:31 GMT

Hi DimaMD
Please clarify further

Re: Search for text among numeric values

DimaMD — Tue, 24 May 2022 09:00:58 GMT

Hi tamerj1 in collum1 we have original data. The goal is to make "result" collum, which will copy text, that is located betweeen numbers in collum 1.

Collum1	result
000235123 Example text between numbers 234234424	Example text between numbers

Re: Search for text among numeric values

tamerj1 — Tue, 24 May 2022 09:49:52 GMT

Hi DimaMD
First step is to create a seperate table containing all the letters that you consider as string

Text Letters = SELECTCOLUMNS ( { "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", " ", "-", "_", ")", "(", "[", "]", ",", ";", ":", "{", "}", "*", "&", "%", "$", "#", "@", "!", "?", "<", ">", "+", "=", "." }, "Letter", [Value] )

Then create new column

Text = VAR ValueLength = LEN ( 'Table'[String] ) VAR T3 = GENERATESERIES ( 1, ValueLength ) VAR T4 = ADDCOLUMNS ( T3, "@StringLetter", MID ( 'Table'[String], [Value], 1 ) ) VAR T5 = ADDCOLUMNS ( T4, "@TexLetters", IF ( [@StringLetter] IN VALUES ( 'Text Letters'[Letter] ), LOWER ( [@StringLetter] ) ) ) RETURN CONCATENATEX ( T5, [@TexLetters] )

Re: Search for text among numeric values

DimaMD — Tue, 24 May 2022 11:06:39 GMT

tamerj1 Hello and thank you, great idea I must say.

But the problem is that sometimes te cell has also text after digits, and we need to consider only part of text that is between digits

For example:

Source	Result
34234 Test text 3434 another text	Test text

If we will use your solution it will take all the text, but we need only the part that is between digits.

Re: Search for text among numeric values

tamerj1 — Tue, 24 May 2022 11:20:43 GMT

DimaMD

Ok this is pretty complex. I'll play with it when I have some extra time. Have a good day

Re: Search for text among numeric values

DimaMD — Tue, 24 May 2022 11:29:50 GMT

tamerj1 , Thank you, I look forward to the good news.

Re: Search for text among numeric values

tamerj1 — Tue, 24 May 2022 13:36:29 GMT

DimaMD

i have an idea for a potential solution. I will post it tomorrow morning.

Re: Search for text among numeric values

tamerj1 — Wed, 25 May 2022 06:09:43 GMT

Hi DimaMD
As promissed, here is the solution (No need for the letters table)

Text = VAR String = 'String Table'[String] VAR StringLength = LEN ( String ) VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Number", [Value] & "" ) VAR T2 = SELECTCOLUMNS ( GENERATESERIES ( 1, StringLength ), "@Index", [Value] ) VAR T3 = ADDCOLUMNS ( T2, "@StringLetters", MID ( String, [@Index], 1 ) ) VAR T4 = ADDCOLUMNS ( T3, "@TexLetters", IF ( NOT ( [@StringLetters] IN T1 ), LOWER ( [@StringLetters] ), "|" ) ) VAR T5 = ADDCOLUMNS ( T4, "Text Letters", VAR PreviousIndex = [@Index] - 1 RETURN IF ( MAXX ( FILTER ( T4, [@Index] = PreviousIndex ), [@TexLetters] ) <> "|", [@TexLetters] ) ) VAR T6 = FILTER ( T5, [Text Letters] <> BLANK ( ) ) RETURN PATHITEM ( CONCATENATEX ( T6, [Text Letters] ), 2 )

Re: Search for text among numeric values

DimaMD — Wed, 25 May 2022 07:32:32 GMT

tamerj1 You are incredible, I would shake your hand. Where are you from?
Greetings from Ukraine

Re: Search for text among numeric values

tamerj1 — Wed, 25 May 2022 07:54:27 GMT

DimaMD
I'm from Jordan but I live in Dubai - UAE

Re: Search for text among numeric values

DimaMD — Wed, 25 May 2022 11:39:11 GMT

tamerj1 Do you understand Russian or Ukrainian

Re: Search for text among numeric values

tamerj1 — Wed, 25 May 2022 12:00:25 GMT

DimaMD
No but my cousin is married to Belarusian and he is staying with his wife here in Dubai.

Re: Search for text among numeric values

DimaMD — Wed, 25 May 2022 12:07:07 GMT

tamerj1 Belarusian is a related language with Russian and Ukrainian

Re: Search for text among numeric values

Amy_Qc — Tue, 11 Oct 2022 18:31:06 GMT

Bonjour,

Si je veux le contraire, extraire les chiffres svp?

If I want the opposite, extract the digits please?

Thanks in advance

Meci d'avance

Amal

Re: Search for text among numeric values

tamerj1 — Tue, 11 Oct 2022 19:07:55 GMT

Hi Amy_Qc

please provide more details perhaps with example.

Re: Search for text among numeric values

Amy_Qc — Tue, 11 Oct 2022 19:17:56 GMT

want extract just numbers with $, thank you tamer in advance 🙂

amal

Re: Search for text among numeric values

tamerj1 — Tue, 11 Oct 2022 19:25:44 GMT

Amy_Qc

It's too late now, I'll work on tomorrow morning. However, I have some questions?

do the numbers come with spaces in between as shown in screenshot?

would you like the output to be of decimal/integer data type (can be used later to perform further calculations) or would you like to have them as is (string type)?

Re: Search for text among numeric values

Amy_Qc — Tue, 11 Oct 2022 19:35:25 GMT

No problem, when you can, you are right preferably to have them decimal/integer and yes the numbers are separated by spaces as shown in the screenshot

thank youuu tamer

Amal

Re: Search for text among numeric values

tamerj1 — Wed, 12 Oct 2022 04:55:26 GMT

Amy_Qc
Hi Amal,

Please refer to attached sample file with the solution. Please let me know if you face any trouble implementing the same on your actual data.

Output = VAR String = 'Table'[Input] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = PATHLENGTH ( Items ) VAR T1 = GENERATESERIES ( 0, 9, 1 ) VAR T2 = GENERATESERIES ( 1, Length, 1 ) VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) ) VAR T4 = FILTER ( T3, VAR T5 = FILTER ( T1, CONTAINSSTRING ( [@Item], [Value] ) ) VAR Condition1 = NOT ISEMPTY ( T5 ) VAR Condition2 = NOT ( CONTAINSSTRING ( [@Item], "-" ) ) VAR Condition3 = NOT ( CONTAINSSTRING ( [@Item], "/" ) ) RETURN Condition1 && Condition2 && Condition3 ) VAR Digits = CONCATENATEX ( T4, [@Item] ) VAR Factor = SWITCH ( TRUE ( ), CONTAINSSTRING ( String, "K$" ), 1000, CONTAINSSTRING ( String, "M$" ), 1000000, CONTAINSSTRING ( String, "B$" ), 1000000000 ) RETURN VALUE ( Digits ) * Factor