Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
In 3 cells i have the following entries
2145,item a,-27,2.1
2187,item b,-32,2.17,2187,item c,-47,2.25
2111,item c,-47,2.23,2122,item a,-27,2.0,2187,item b,-32,2.15,2187,item d,-52,2.8
In each cell, i would like to replace the comma at every instance which is a multiple of 4 with a semi colon. So the commmas at the 4,8,12,16,20 positions should be replaced with a semi colon.
Please help me with the M code for this.
Thank you.
Solved! Go to Solution.
= Table.AddColumn(Source, "Rpl", each let pos = try List.RemoveNulls(List.Zip(List.Split(Text.PositionOf([Column1], ",", 2),4)){3}?) otherwise {} in List.Accumulate(pos, [Column1], (s,c) => Text.ReplaceRange(s,c,1,";")))
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! |
show the result as it should be
Thank you Ahmedx. CNENFRNL has answered my question.
= Table.AddColumn(Source, "Rpl", each let pos = try List.RemoveNulls(List.Zip(List.Split(Text.PositionOf([Column1], ",", 2),4)){3}?) otherwise {} in List.Accumulate(pos, [Column1], (s,c) => Text.ReplaceRange(s,c,1,";")))
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! |
Hi,
Thank you for your help. Could you kinldy clarify the following:
Thank you for devoting your time and shating your knoeledge.
Hi, my friend, now you see how awful the readability of M is ... for this snippet of code
try List.RemoveNulls(List.Zip(List.Split(Text.PositionOf([Column1], ",", 2),4)){3}?) otherwise {}
you can strip it down this way,
let
Source = "2187,item b,-32,2.17,2187,item c,-47,2.25",
#"Pos of All Occurrences" = Text.PositionOf(Source, ",", Occurrence.All),
#"Split by 4" = List.Split(#"Pos of All Occurrences", 4),
Zipped = List.Zip(#"Split by 4"),
#"4th Element of List" = Zipped{3}?, //"?" is "Selection and Projection Operators": https://learn.microsoft.com/en-us/powerquery-m/m-spec-operators
#"Removed Nulls" = List.RemoveNulls(#"4th Element of List")
in
#"Removed Nulls"
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! |
Hi,
Thank you for that explanation.
look i got you right
Thank you for replying. The result does not look correct. I want every 4th comma to become a semi colon. Also, please paste the code - you have only posted the image.
Thank you for your help.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
84 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |