Split unequal lenght data

[logan_logan]

Hi,

I have product like 'abc:def:ghi:jkl' .I want to take right most product (jkl) after the colon. I am using this but it works only if  i have two products (abc:def). Also keep in mind, my product length is not same always. There might be any number of ':' in between.

Col1 = RIGHT(TABLE[PRODUCT],LEN(TABLE[PRODUCT])-SEARCH(":",TABLE[PRODUCT],,0))

[AlanFredes · Accepted solution]

Hi Logan,

This can be done best in the Power Query editor by using the below function to identify the last occurrence of ":".

Text.PositionOf(TABLE[PRODUCT], ":", Occurrence.Last)

This past post can help you with the answer: https://community.fabric.microsoft.com/t5/Desktop/Search-from-right-to-left/m-p/952873#M456539

I built an example table. here is the Power Query Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYw7DoAwDEPv0pmhYkHyVUqHgBt+G4H7E6hALJb9LDul0J8xtp0MI1gU07xg3UJuUjChgGqiUJq8DGKswSEf89VegbeIE3Day1Fn/mTOf7N8AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [products = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"products", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"products", "Product"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Find Last", each Text.PositionOf([Product],":",Occurrence.Last)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "product Name", each if Text.PositionOf([Product],":",Occurrence.Last)=-1 then [Product] else Text.Middle([Product],Text.PositionOf([Product],":",Occurrence.Last)+1,Text.Length([Product])-Text.PositionOf([Product],":",Occurrence.Last)))
in
    #"Added Custom1"

Did I answer your question? Mark my post as a solution!

[logan_logan]

Thanks a lot. Its working