cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DimaMD
Responsive Resident
Responsive Resident

Separate the text value in the column

Hi Community

Please help, I need to separate the text value, the text value can be of different lengths
Separate values ​​is required when searching for a numeric value. 
As a result, I have to get text values ​​excluding numeric values.
I know that this can be done in Power Query, but with this separation I do not perform Incremental Update

 

ValuesResult
a s d 321 asda s d
z x c 654 zxcx z c 
q w e 963 qweq w e
a w s d 325 asdfa w s d

 

thanks for the help, 
Greetings from Ukraine

1 ACCEPTED SOLUTION
tamerj1
Community Champion
Community Champion

Hi @DimaMD 
One way to do that: New column >

Result = 
VAR NumberSeries =
    GENERATESERIES ( 0, 9, 1 )
VAR LocationTable =
    ADDCOLUMNS ( NumberSeries, "@Location", FIND ( [Value], Data[Values], 1, 10000 ) )
VAR StringEnd =
    MINX ( LocationTable, [@Location] ) - 1
RETURN
    LEFT ( Data[Values], StringEnd )

1.png

View solution in original post

8 REPLIES 8
tamerj1
Community Champion
Community Champion

Hi @DimaMD 
One way to do that: New column >

Result = 
VAR NumberSeries =
    GENERATESERIES ( 0, 9, 1 )
VAR LocationTable =
    ADDCOLUMNS ( NumberSeries, "@Location", FIND ( [Value], Data[Values], 1, 10000 ) )
VAR StringEnd =
    MINX ( LocationTable, [@Location] ) - 1
RETURN
    LEFT ( Data[Values], StringEnd )

1.png

Such an ingenious solution! Your level of technological prowess in this technology is truely tremendous. 

😍

DimaMD
Responsive Resident
Responsive Resident

HI, @tamerj1
I am very grateful to you, your DAX worked, and that made it easier for me to work with the update

 

Aditya_Meshram
Super User
Super User

Hi @DimaMD ,
is the length of numeric part fixed at 3 characters?

Hi @Aditya_Meshram  no, it can be whole text words 

for example:
No War 321 No War, 
We are from Ukraine 321654, We are from Ukraine
As a result, I have to get in the column
No War
We are from Ukraine

If yes, you can use this DAX 

Aditya_Meshram_0-1651476265444.png

Text part only = 
var _numpart = RIGHT('Table'[Values],3)
return
SUBSTITUTE('Table'[Values],_numpart,"")


Regards

@Aditya_Meshram 
Thanks for the help, well, this DAX does not work with my data, there may be several text values

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors