Split unequal lenght data

logan_logan — Tue, 18 Jul 2023 20:05:07 GMT

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))

Re: Split unequal lenght data

AlanFredes — Tue, 18 Jul 2023 20:56:16 GMT

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!

Re: Split unequal lenght data

logan_logan — Tue, 18 Jul 2023 21:58:26 GMT

Thanks a lot. Its working

topic Split unequal lenght data in DAX Commands and Tips

Split unequal lenght data

Re: Split unequal lenght data

Re: Split unequal lenght data