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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors