Forum Discussion

ashuaswinireddy's avatar
ashuaswinireddy
Icon for Helper I rankHelper I
3 years ago
Solved

Remove leading semicolons from a Column

Hello All,

 

I have an example column like below.

 

emails
;;;;;
;;;;

;;

;;;;;;;;;;;;;[email protected]; [email protected]; [email protected]
;;;;;;;
;;
;;;;[email protected]; [email protected]
;;;;;;;;

 

Thank you!

 

Can you please let me know hwo to remove leading semicolons from the above column in Power Query editor to get result as  below

emails
 
 
 
[email protected]; [email protected]; [email protected]
 
 
[email protected]; [email protected]
 
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ashuaswinireddy ,

    You can achieve it in Power Query Editor by using ReplaceValue function:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsgYBpVgdCAvKgPPhIDEp2QGI9ZLzc60VUlLTEJz0jEwYB1kbuklZ2TkIPUimoVilFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [emails = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"emails", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",";","",Replacer.ReplaceText,{"emails"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".com ",".com;",Replacer.ReplaceText,{"emails"})
in
    #"Replaced Value1"

    Best Regards

  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Replaced Value2" = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"emails"}),
    #"Replaced Value" = Table.ReplaceValue(#"Replaced Value2",";"," ",Replacer.ReplaceText,{"emails"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"emails", Text.Trim, type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Trimmed Text"," ","; ",Replacer.ReplaceText,{"emails"})
in
    #"Replaced Value1"

     Hope this helps.

  • Ahmedx's avatar
    Ahmedx
    3 years ago

    try like this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsgYBpVgdCAvMgPLggnCQmJTsAMR6yfm51gopqWkITnpGJoyDrA3dpKzsHIQeJNNQrFJQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mail = _t]),
    Chang = Table.TransformColumnTypes(Source,{{"Mail", type text}}),
    Mail = Table.AddColumn(Chang, "Custom", each Text.Replace(
    Text.Trim(
        Text.Combine(
            Text.Split([Mail],";")
            ))," ",";"))
in
    Mail

    https://dropmefiles.com/g7gDU

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ashuaswinireddy ,

    You can achieve it in Power Query Editor by using ReplaceValue function:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsgYBpVgdCAvKgPPhIDEp2QGI9ZLzc60VUlLTEJz0jEwYB1kbuklZ2TkIPUimoVilFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [emails = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"emails", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",";","",Replacer.ReplaceText,{"emails"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",".com ",".com;",Replacer.ReplaceText,{"emails"})
in
    #"Replaced Value1"

    Best Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi there,

     

    in the editor, you could select the column and then under "Add column" -> "Extract" choose "Text after delimiter" and set ";" to be the delimiter. This should give you a new column with the format you want.

     

    Edit: Sorry I just realized ";" is also used in between the individual email addresses so my first idea wouldn't work.

     

    Best

    Leo

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

    Hi,

    This M code works

    let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Replaced Value2" = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"emails"}),
    #"Replaced Value" = Table.ReplaceValue(#"Replaced Value2",";"," ",Replacer.ReplaceText,{"emails"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"emails", Text.Trim, type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Trimmed Text"," ","; ",Replacer.ReplaceText,{"emails"})
in
    #"Replaced Value1"

     Hope this helps.

  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    3 years ago

    try like this

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsgYBpVgdCAvMgPLggnCQmJTsAMR6yfm51gopqWkITnpGJoyDrA3dpKzsHIQeJNNQrFJQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Mail = _t]),
    Chang = Table.TransformColumnTypes(Source,{{"Mail", type text}}),
    Mail = Table.AddColumn(Chang, "Custom", each Text.Replace(
    Text.Trim(
        Text.Combine(
            Text.Split([Mail],";")
            ))," ",";"))
in
    Mail

    https://dropmefiles.com/g7gDU