Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 Levice |
Továrenská 49 953 01 Zlaté Moravce | 953 01 Zlaté Moravce |
Budovateľská 6 940 64 Nové Zámky | 940 64 Nové Zámky |
Dopravná 14 934 01 Levice | 934 01 Levice |
Stummerova 5 955 21 Topoľčany | 955 21 Topoľčany |
Priemyselný, Ul. E. Sachsa 4-6 934 01 Kráľová nad Váhom | 934 01 Kráľová nad Váhom |
Priemyselný park, Dolné Hony 24/1085 951 41 Kalná nad Hronom | 951 41 Kalná nad Hronom |
Thank you 🙂
Solved! Go to Solution.
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 🙂
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
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
Thanks, I tried that, but no success 😞
its too complicated for that, I guess