Solved: extract text strings from a column using a specifi...

monojchakrab · ‎07-21-2023

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 Sweetener 200 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.,

Vijay_A_Verma · ‎07-22-2023

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

monojchakrab · ‎07-22-2023

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

Vijay_A_Verma · ‎07-22-2023

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 · ‎07-22-2023

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

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

Helpful resources

Fabric certifications survey

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024