Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.