Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
99 | |
97 | |
88 | |
70 |
User | Count |
---|---|
165 | |
131 | |
129 | |
102 | |
98 |