Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |