How to remove all leading delimeters infront of a column

[ashuaswinireddy]

Hello All,

I have a column as shown below

Emails

;

;

;

;

; ; ; ; ; ; ; ; ; ; ; ;ashu@gmail.com; kriya@gmail.com; doug@gmail.com

; ; ; ;

; ; ; matthew@gmail.com; mike@gmail.com

; ;

Expected Result:

Emails

ashu@gmail.com; kriya@gmail.com; doug@gmail.com

matthew@gmail.com; mike@gmail.com

Can you please let me know how to remove all delimiters before the column starts with emails.

Note: I need delimiter(;) between email addresses.

Tried below Custom column, but its removing all delimiters

= Table.AddColumn(#"Replaced Value", "Email", each Text.TrimStart(Text.Remove([Emails],";")))

Thank you!

[v-shex-msft · Accepted solution]

HI @ashuaswinireddy,

I checked the sample text and find there are some whitespace characters include text string, I'd like to suggest you nested the Text.Replace function to remove them before Text.TrimStart function:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Transform", each Text.TrimStart(Text.Replace([Emails]," ",""),";"))

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WslaK1cFKKmCFicUZpQ7puYmZOXrJ+bnWCtlFmZWJyAIp+aXpCD6yUUjs3MSSkozUcmR9uZnZqZj6lGJjAQ==", 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}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Transform", each Text.TrimStart(Text.Replace([Emails]," ",""),";"))
in
    #"Added Custom"

Regards,

Xiaoxin Sheng