Forum Discussion
Bohumil_Uhrin
5 years agoHelper 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 split After split Ku Bratke 5 934 05 Levice 934 05 Levic...
- 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" - 5 years ago
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
NickTT
5 years agoHelper III
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_Uhrin5 years agoHelper II
Thanks, I tried that, but no success 😞
its too complicated for that, I guess