cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bo_wang5
Helper II
Helper II

Conditional column with Multiple Transformations

I have the following data transformation I need to make.  The source data contains multiple zipcodes from multiple countries.  In the case of PL (Poland), the source zip code has lots of issues. ie leading 0s and spaces in the middle of the zip code.  I know how to clean this up based on multiple columns but I'd like to tidy up the code to something that can be done in one step.  basically how do I nest all these transformations into one AddColumn? thanks.

 

Here are the steps I used to clean this data

 

Table.AddColumn(#"Changed Type", "Remove middle space", each Replacer.ReplaceText([Zipcode]," ",""))

 

Table.AddColumn(#"Added Custom", "Remove Leading 0s", each Text.TrimStart([Remove middle space],"0"))

 

Table.AddColumn(#"Added Custom1", "ZipCode Fixed", each if [Country] = "PL" then [Remove Leading 0s] else [Zipcode])

 

bo_wang5_1-1631741421924.png

 

1 ACCEPTED SOLUTION
Jakinta
Super User
Super User

You can use this step instead.

= Table.AddColumn(#"Changed Type", "ZipcodeFixed", each if [Country] = "PL" then Text.TrimStart ( Replacer.ReplaceText([Zipcode]," ",""), "0") else [Zipcode])

View solution in original post

3 REPLIES 3
CNENFRNL
Community Champion
Community Champion

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvBR0lEyMDBQcHRyVjAKsFSK1YlWcnEFCyr4BHsrGCnFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Zipcode = _t]),
    #"One-Go" = Table.ReplaceValue(Source, each [Zipcode], each if [Country] = "PL" then Text.TrimStart(Text.Remove([Zipcode], " "), "0") else [Zipcode], Replacer.ReplaceValue, {"Zipcode"})
in
    #"One-Go"
Jakinta
Super User
Super User

You can use this step instead.

= Table.AddColumn(#"Changed Type", "ZipcodeFixed", each if [Country] = "PL" then Text.TrimStart ( Replacer.ReplaceText([Zipcode]," ",""), "0") else [Zipcode])

View solution in original post

Thank you very much, this is exactly what I was looking for.  I wasn't sure how to nest the the replacer.replacetext inside the text.trimstart.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors