Forum Discussion

logan_logan's avatar
logan_logan
Icon for Helper I rankHelper I
3 years ago
Solved

Split unequal lenght data

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's avatar
    AlanFredes
    3 years ago

    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!

     

     

2 Replies

  • AlanFredes's avatar
    AlanFredes
    Icon for Resolver IV rankResolver IV
    3 years ago

    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!