Forum Discussion
extract text strings from a column using a specified list of strings
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})
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)), ", ")