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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
DimaMD
Solution Sage
Solution Sage

Search for text among numeric values

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!


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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 )

 1.png

View solution in original post

23 REPLIES 23
tamerj1
Super User
Super User

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 )

 1.png

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

Hi @Amy_Qc 

please provide more details perhaps with example. 

Amy_Qc_1-1665515868711.png

 

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

amal

 

@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)?

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

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

1.png

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

 

Salam Tamer,

Thank you very much but i have this error 😞 

Amy_Qc_0-1665579017103.png

 

Salam Amal,

Please search this value in your table and share the line that creates this error as I failed to simulate it.

I don't find this value!

Hi Amal,

Looks like a date which the code has converted to this shape somehow. Unless I see how it originally looks like I won't be able to solve the problem.

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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

@tamerj1 Do you understand Russian or Ukrainian


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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

 

@tamerj1 Belarusian is a related language with Russian and Ukrainian


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com
tamerj1
Super User
Super User

Hi @DimaMD 
Please clarify further

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. 

Collum1result
000235123 Example text between numbers 234234424Example text between numbers

__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

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

1.png

@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:

SourceResult
34234 Test text 3434 another textTest text

 

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


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.