Forum Discussion

Bohumil_Uhrin's avatar
Bohumil_Uhrin
Helper II
5 years ago
Solved

Split column based on pattern

Hi, Im trying to split column based on pattern: "ddd dd" (3 digits & space & 2 digits) Can someone help, please?   sample: Before split After split Ku Bratke 5 934 05 Levice 934 05 Levic...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Hi, Bohumil_Uhrin , I tweak the code and it solves the issue you mentioned. Pls have another try,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc9NasMwEIbhq3x47aZWIpt6G1IIJC2FpF3EZDEkggRbkpF/wIfoIbT0IeqNfLDKaQqFwmwEr56RsizYNFgaqnOBGGkUw89WtNeTCI5hFux166wRqsqdBU99weDnUFDterxoQ+29XDZn3VItxuHWJkh5hITj1Qs9Ds7KvLuFK136W8o3jCNd8Mn7s3FXN1IK463pQXGMOcNel3ocxk9SP8SbuQrZVaJQ7ivEezHD8ww7Ol0qAn9I8MtujLPjMH0Bis74cPai5T8BJZk8xEr7Q4+1Vh3m/JFFT9N+Bu4dKtTdWButJuP4DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    
    #"Added Custom" = Table.AddColumn(Source, "Custom", each
                [l1 = Text.Remove([Column1], {"0".."9"}),
                l2 = Text.SplitAny([Column1], l1),
                s = List.Last(List.Select(l2, each _<>"" and Text.Length(_)=2))
                ][s]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Middle([Column1], Text.PositionOf([Column1], [Custom], Occurrence.Last)-4)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

  • ziying35's avatar
    ziying35
    5 years ago

    HiBohumil_Uhrin 

    Try this:

    let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("jZBfS8MwFMW/yqHPdSZrWoyPZcJgKsKmD3M+hPWOlbZJSf9AEb+76bKJsCK+hMu9N+d37nn/DFI6GEto6jJvg/tg1SG1qi0IMWQkwGI8Up/vKfgKr5Y3pt91jBG3pJvClxASMo7AOLalak9NiSdjVT8tknaZ6VVLbpNH9COTQAqGROD5DJHY+lFVDFM6C1M7hj5/58Lb53/YX7ddVZF19PHYOMacY2Nq452ML8uUnoS92JyqoaHyxDtkIV7LGR5mWKv9sVEQNwkuBlbWe/Kyl8igVYY3Xx9N9R8IamWLEAvjGy6RpdED5mJ3y9ndeIJL3/FUqX8xltboUf/jGw==", BinaryEncoding.Base64),Compression.Deflate))),
    fx = (slist)=>
         let
            lst = List.Skip(slist, each not Value.Is(Value.FromText(_), type number) and Text.Length(_)<>3),
            sw = if Value.Is(Value.FromText(lst{1}), type number) and Text.Length(lst{1})=2 then Text.Combine(lst, " ")
                 else @fx(List.Skip(lst))
          in
            sw,
    result = Table.AddColumn(Source, "After split", each fx(Text.Split([Before split], " ")))
in
    result
CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

Hi, Bohumil_Uhrin , you may want to try following code,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc9NasMwEAXgqzy8dlMrkUy9DSkE0pZC0i5ishgSQYJtycg/4EP0EFr6EPVGPljlJIVClwNvvjeTpsGmwdJQnUkIJAuOSOBFtpejDA5hGux066yRqsqcBU+QiAUihn1Otevxqg219+SyOemWajkO12yMhEeIOd680GPvbJF11+BKl35L+Qzjt0b2t3FbN0Uhjbemg4TAnGGnSz0O4xepG/FuLrLoKpkr9x3iI5/heYYtHc8VgT/E+GU3xtlxmF6AohM+nT3r4p+AkkwWYqX90GOtVYc5f2TR09TPwL1Duboba6PVZBx+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    
    #"Added Custom" = Table.AddColumn(Source, "Custom", each
                [l1 = Text.Remove([Column1], {"0".."9"}),
                l2 = Text.SplitAny([Column1], l1),
                s = List.Last(List.Select(l2, each _<>"" and Text.Length(_)=2))
                ][s]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Middle([Column1], Text.PositionOf([Column1], [Custom])-4)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

Bohumil_Uhrin's avatar
Bohumil_Uhrin
Helper II
5 years ago

thank you, this worked in 99% cases 🙂

it fails if the 2 digit string is repeating in the text, for example: Dopravná 14 950 50 Nitra

but I can handle that manually, thanks 🙂

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Hi, Bohumil_Uhrin , I tweak the code and it solves the issue you mentioned. Pls have another try,

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc9NasMwEIbhq3x47aZWIpt6G1IIJC2FpF3EZDEkggRbkpF/wIfoIbT0IeqNfLDKaQqFwmwEr56RsizYNFgaqnOBGGkUw89WtNeTCI5hFux166wRqsqdBU99weDnUFDterxoQ+29XDZn3VItxuHWJkh5hITj1Qs9Ds7KvLuFK136W8o3jCNd8Mn7s3FXN1IK463pQXGMOcNel3ocxk9SP8SbuQrZVaJQ7ivEezHD8ww7Ol0qAn9I8MtujLPjMH0Bis74cPai5T8BJZk8xEr7Q4+1Vh3m/JFFT9N+Bu4dKtTdWButJuP4DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    
    #"Added Custom" = Table.AddColumn(Source, "Custom", each
                [l1 = Text.Remove([Column1], {"0".."9"}),
                l2 = Text.SplitAny([Column1], l1),
                s = List.Last(List.Select(l2, each _<>"" and Text.Length(_)=2))
                ][s]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Middle([Column1], Text.PositionOf([Column1], [Custom], Occurrence.Last)-4)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"})
in
    #"Removed Columns"

  • ziying35's avatar
    ziying35
    Impactful Individual
    5 years ago

    HiBohumil_Uhrin 

    Try this:

    let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("jZBfS8MwFMW/yqHPdSZrWoyPZcJgKsKmD3M+hPWOlbZJSf9AEb+76bKJsCK+hMu9N+d37nn/DFI6GEto6jJvg/tg1SG1qi0IMWQkwGI8Up/vKfgKr5Y3pt91jBG3pJvClxASMo7AOLalak9NiSdjVT8tknaZ6VVLbpNH9COTQAqGROD5DJHY+lFVDFM6C1M7hj5/58Lb53/YX7ddVZF19PHYOMacY2Nq452ML8uUnoS92JyqoaHyxDtkIV7LGR5mWKv9sVEQNwkuBlbWe/Kyl8igVYY3Xx9N9R8IamWLEAvjGy6RpdED5mJ3y9ndeIJL3/FUqX8xltboUf/jGw==", BinaryEncoding.Base64),Compression.Deflate))),
    fx = (slist)=>
         let
            lst = List.Skip(slist, each not Value.Is(Value.FromText(_), type number) and Text.Length(_)<>3),
            sw = if Value.Is(Value.FromText(lst{1}), type number) and Text.Length(lst{1})=2 then Text.Combine(lst, " ")
                 else @fx(List.Skip(lst))
          in
            sw,
    result = Table.AddColumn(Source, "After split", each fx(Text.Split([Before split], " ")))
in
    result