Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to replace/remove text between delimiters when there are multiple delimiters ?

Hello, 

 

How to replace/remove text between delimiters when there are multiple delimiters ?

 

Case : 

 

value on the cell : 

 

aaa

bbb

ccc

eee

aaa

bbb

ccc

eee

aaa

bbb

ccc

xxx

eee

aaa

bbb

ccc

eee

aaa

bbb

ccc

xxx

eee

....

 

I am looking to remove only the values in red.

 

Thanks!

 

 

Need Help

6 Replies

    • wdx223_Daniel's avatar
      wdx223_Daniel
      Community Champion
      3 years ago

      guess!!!

      NewStep=Table.Combine(Table.Group(PreviousStepName,"Column1",{"n",each Table.RemoveLastN(_,each [Column1]<>"bbb")},0,(x,y)=>Byte.From(y="xxx"))[n])

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hello Daniel,

         

        Thanks for your try.

         

        This doesn't seem to work it erases all the value 😞

         

        let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxMjMmLyUtKSgJRycnJICo1NRVEkSxXUVFBHe1KsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Colonne 1" = _t]),
        #"Type modifié" = Table.TransformColumnTypes(Source,{{"Colonne 1", type text}}),
        #"Colonnes renommées" = Table.RenameColumns(#"Type modifié",{{"Colonne 1", "Column1"}}),

        NewStep=Table.Combine(Table.Group(#"Colonnes renommées","Column1",{"n",each Table.RemoveLastN(_,each [Column1]<>"bbb")},0,(x,y)=>Byte.From(y="xxx"))[n])
        in
        NewStep