Forum Discussion

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
3 years ago
Solved

Replace comma with semi colon at fixed intervals

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. 

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    3 years ago
    = 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,";")))

8 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    3 years ago
    = 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's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      3 years ago

      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's avatar
        CNENFRNL
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      3 years ago

      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.