Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Solution Sage
Solution Sage

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"

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Jakinta
Solution Sage
Solution Sage

You can use this step instead.

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors