Return a character before a delimiter but only if its a number else just return a 1.

[ruthe23]

I have a column that has product names and sometimes they contain #pk indicating they have a multipack.  To accurately count the # of products in the package I need to parse this column and find the # before "PK" and put it in a new column so I can calculate with it.  If the column contains a letter before PK as it does in some of the other parts of a product name ignore and put a 1 in the new column.  If the column doesn't contain any PK then also put a 1 in the column.  I have tried all sorts of Text.Contains, Value.Is, Text,End, Text.Beforedelimiter but I either get Errors, Or I get whatever is before the PK in some cases it's the # and sometimes it's a letter which screws up the new column.   I could probably do antoher step to get rid of the letters in the new column but I thought they might be away to do it all in one.  

[Anonymous]

Hi @ruthe23 ,

You can try this way:

let 
TextValue = [Column1],
PKIndex = Text.PositionOf(TextValue, "pk"),
CharBeforePK = if PKIndex > 0 then Text.Middle(TextValue, PKIndex - 1, 1) else null,
IsNumber = CharBeforePK <> null and List.Contains({"0".."9"}, CharBeforePK)
in 
if IsNumber then CharBeforePK else "1"

Use this M function to create a custom column:

Here is the whole M function in Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMirIVkhMTFSK1YlWSizIBtMmQDEos6CgIDsbwoSpMoRKGRoaAoUU0mFcI2PFguzEpGQwz9LSEqjTBCQXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
TextValue = [Column1],
PKIndex = Text.PositionOf(TextValue, "pk"),
CharBeforePK = if PKIndex > 0 then Text.Middle(TextValue, PKIndex - 1, 1) else null,
IsNumber = CharBeforePK <> null and List.Contains({"0".."9"}, CharBeforePK)
in 
if IsNumber then CharBeforePK else "1")
in
    #"Added Custom"

NOTE: Since I'm not sure if your data will contain more than one "pk", I'll default to the first one that comes up.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin · Accepted solution]

You were close.  There is a very powerful function that allows you to define your very own transition rules.