Forum Discussion
extracting numeric values occurring unevenly in the column
Hi all,
I am having a bit of a challenge in dealing with a column of names as below :
I need to extract the size of the pack, like 100, 500 etc and also the size of the pack like 1 or 2. But these dont occur at the same place in the column and also the delimiters are different, while appearing at different places.
Is there a way to extract the numbers appearing before some text like "sachets" or "tablets" and similarly followed by "pack of"?
RemoveTextFromTitle = Table.AddColumn(MultiPackUnit, "Remove Text", each Text.Remove([Title],{"a".."z","A".."Z"})),
PackPosition = Table.AddColumn(RemoveTextFromTitle, "Pack", each List.Select(PackList,
(x)=>
Text.Contains([Remove Text],x,Comparer.OrdinalIgnoreCase)
){0}?),
CorrectPackSize = List.Accumulate({0..Table.RowCount(PackSize)-1},
PackPosition,
(x,y)=>
Table.ReplaceValue(x,
PackSize[OldValue]{y}, PackSize[NewValue]{y},
Replacer.ReplaceValue, {"Pack"})
),I have tried to use the code as above - while its solving part of the problem, it does not solve it all the way thru' and becomes a little cumbersome, adding so many columns.
Is there a simpler way to do this, with less code and lines/columns?
Any help appreciated
2 Replies
- wdx223_Daniel
Community Champion
RemoveTextFromTitle = Table.AddColumn(MultiPackUnit, "Remove Text",each Text.BeforeDelimiter(List.Select(Text.Split([Title],"|"),each List.Contains({"sachets","tablets"},_,(x,y)=>Text.EndsWith(y,x,Comparer.OrdinalIgnoreCase))){0}? ??""," ")) - monojchakrab
Resolver III
Hey wdx223_Daniel - this may not work as all the values of the [Title] column do have a delimiter. I think a better logic would be to find, if there is a way, the numeric value preceding texts like "tablets" or "sachets" and folloing text like "Pack". Does that make sense at all?