Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
68 | |
64 | |
51 | |
39 | |
26 |
User | Count |
---|---|
84 | |
57 | |
45 | |
44 | |
36 |