Replace comma with semi colon at fixed intervals

[Ashish_Mathur]

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. 

[Ahmedx]

show the result as it should be

[Ashish_Mathur]

Thank you Ahmedx.  CNENFRNL has answered my question.

[CNENFRNL · Accepted 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,";")))

[Ashish_Mathur]

Hi,

Thank you for your help.  Could you kinldy clarify the following:

1. What is the result of List.Zip in the second row - 2187,item b,-32,2.17,2187,item c,-47,2.25
2. What is the {3}? doing there.  I read up on the ducumentation of List.Zip but did not find these inputs explained there.

Thank you for devoting your time and shating your knoeledge.

[CNENFRNL]

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"

[Ashish_Mathur]

Hi,

Thank you for that explanation.

[Ahmedx]

look i got you right

[Ashish_Mathur]

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.