cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

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 🙂

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

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"

Untitled.png

View solution in original post

Highlighted

Hi@Bohumil_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

View solution in original post

7 REPLIES 7
Highlighted
Helper I
Helper I

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.

Highlighted

Thanks, I tried that, but no success 😞

its too complicated for that, I guess

Highlighted
Memorable Member
Memorable Member

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"

Screenshot 2020-10-14 005751.png

Highlighted

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 🙂

Highlighted

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"

Untitled.png

View solution in original post

Highlighted

Hi@Bohumil_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

View solution in original post

Highlighted

@CNENFRNL , @ziying35 thank you both very much, works great 🙂

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors