Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello All,
I have an example column like below.
emails |
;;;;; |
;;;; |
;; |
;;;;;;;;;;;;;abc@abc.com; def@abc.com; ghi@abc.com |
;;;;;;; |
;; |
;;;;jkl@abc.com; abc@abc.com |
;;;;;;;; |
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 |
abc@abc.com; def@abc.com; ghi@abc.com |
jkl@abc.com; abc@abc.com |
Solved! Go to Solution.
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
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.
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
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
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.
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |