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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
monojchakrab
Resolver III
Resolver III

extract text strings from a column using a specified list of strings

Hiya everyone,

 

I have a column which has text and numeric strings as follows. My problem is that I need to extract only the text strings which appear in a separate list provided separately.

 

The column with text is as follows :

Title
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 5
Equal Classic Zero Calorie Sweetener Sweetener 200 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener Sweetener 200 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener Sweetener 200 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener Sweetener 200 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener Sweetener 200 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener Sweetener 200 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 2
Equal Classic Zero Calorie Sweetener, Sugar Free, Calorie Control, 100 Tablets, Pack of 2
Equal Classic Zero Calorie from Sucralose Sachet (100 Each - box (1
Classic Zero Calorie Sweetener - Pack of 2 (300 Tablets Each
Classic 300 Tablets + Cook n Bake Diet Sugar 80 gm Jar

 

What I did first is to remove the numeric values from this column :

Table.AddColumn(MPU, "Remove Text", each Text.Remove([Title],{"A".."Z","a".."z"}))

 

Remove Text
, , , 100 , 5
, , , 100 , 5
, , , 100 , 5
, , , 100 , 5
, , , 100 , 5
, , , 100 , 5
, , , 100 , 5
, , , 100 , 5
, , , 100 , 5
, , , 100 , 5
, , , 100 , 5
, , , 100 , 5
200 , 2
200 , 2
200 , 2
200 , 2
200 , 2
200 , 2
, , , 100 , 2
, , , 100 , 2
, , , 100 , 2
, , , 100 , 2
, , , 100 , 2
, , , 100 , 2
, , , 100 , 2
, , , 100 , 2
(100 - (1
- 2 (300
300 + 80

 

By using the code :

 Now from this column I need to extract all the text strings matching with the list below :

100
110
200
300
330
440
550
80
500
50
600
30
900

Is there a way to use List.Generate or List.Accumulate to acheive this task? I am somehow am unable to get to the right coding with these two functions and I am pretty sure there is no better way to get this done without using one of these two functions.

Thanks in advance for the help.

regds.,

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

I am assuming that for last one you need both 300 and 80

Then use below code

Text.Combine(List.Select(Text.Split(Text.Trim(Text.Combine(List.Transform(Text.ToList([Title]), (x)=> if Value.FromText(x) is number then x else " "), ""))," "), (x)=> Text.EndsWith(x,"0")), ", ")

if you needed only 300

Value.FromText(List.Select(Text.Split(Text.Trim(Text.Combine(List.Transform(Text.ToList([Title]), (x)=> if Value.FromText(x) is number then x else " "), ""))," "), (x)=> Text.EndsWith(x,"0")){0})

View solution in original post

3 REPLIES 3
monojchakrab
Resolver III
Resolver III

@Vijay_A_Verma  - Vijay , thanks for the quick revert. The problem is, there are over 15000 rows of data in this table. I only added a snapshot of the column. Hence I created a separate list, since I know what the pack sizes are. I am looking for a more robust and dynamic solution, which will work with any type of title description and irrespective of whereever the numeric string is placed in the [Title] string. The problem compounds when new rows of data are added and the description in the [Title] column evolves constantly as it updates daily. What is easily possible though is to update the list of the pack size as that is simply determinable. Hence if we approach this with a more general function like List.Accumulate perhaps, it possibly will generate a more general and dynamic solution.

But I will definitely try out the solution recommended by you and update you with the result I get

Thanks again bud!

The solution is fully dynamic and is not dependent upon your text length. List.Accumulate has a performance penalty but in case of 15000 rows, you can tolerate List.Accumulate. But if you need to drive the numbers through another table where Table2 has those numbers and column name is Column1

Text.Combine(List.Select(Text.Split(Text.Trim(Text.Combine(List.Transform(Text.ToList([Title]), (x)=> if Value.FromText(x) is number then x else " "), ""))," "), (x)=> List.Contains(Table2[Column1],x)), ", ")

 

Vijay_A_Verma
Super User
Super User

I am assuming that for last one you need both 300 and 80

Then use below code

Text.Combine(List.Select(Text.Split(Text.Trim(Text.Combine(List.Transform(Text.ToList([Title]), (x)=> if Value.FromText(x) is number then x else " "), ""))," "), (x)=> Text.EndsWith(x,"0")), ", ")

if you needed only 300

Value.FromText(List.Select(Text.Split(Text.Trim(Text.Combine(List.Transform(Text.ToList([Title]), (x)=> if Value.FromText(x) is number then x else " "), ""))," "), (x)=> Text.EndsWith(x,"0")){0})

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors