Forum Discussion

monojchakrab's avatar
monojchakrab
Icon for Resolver III rankResolver III
3 years ago

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's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity 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}? ??""," "))
  • 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?