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 splitAfter split
Ku Bratke 5 934 05 Levice934 05 Levice
Továrenská 49 953 01 Zlaté Moravce953 01 Zlaté Moravce
Budovateľská 6 940 64 Nové Zámky940 64 Nové Zámky
Dopravná 14 934 01 Levice934 01 Levice
Stummerova 5 955 21 Topoľčany955 21 Topoľčany
Priemyselný, Ul. E. Sachsa 4-6  934 01 Kráľová nad Váhom934 01 Kráľová nad Váhom
Priemyselný park, Dolné Hony 24/1085 951 41 Kalná nad Hronom951 41 Kalná nad Hronom

 

Thank you 🙂

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

7 Replies

  • 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"

  • NickTT's avatar
    NickTT
    Helper III
    5 years ago

    Check out "Column From Examples" under the Add Column Tab. Select your source column first. Then click on that button. Start typing how you want your data to look and it "Should" figure it out for you. Give it a shot.

    • Bohumil_Uhrin's avatar
      Bohumil_Uhrin
      Helper II
      5 years ago

      Thanks, I tried that, but no success 😞

      its too complicated for that, I guess