Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have a column called State/Country and it contains either a country name (e.g. Korea) or a state code (e.g. CA)
What I need to do is to separate those into 2 columns:
Var = State/Country
If length(Var) > 2 then CountryColumn = Var, StateColumn = “ “
Else CountryColumn=USA, StateColumn = decodeStateAbbr (??)
My questions: how can I write this in DAX and how can I decode state abbriviation?
Solved! Go to Solution.
@Anonymous not sure wht you mean by decode state abbreviation. here is calculated columns looks like
Country = IF( LEN(Table[Country/State]) > 2, Table[Country/State], "USA" ) State = IF( LEN(Table[Country/State]) > 2, "", Table[Country/State])
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous not sure wht you mean by decode state abbreviation. here is calculated columns looks like
Country = IF( LEN(Table[Country/State]) > 2, Table[Country/State], "USA" ) State = IF( LEN(Table[Country/State]) > 2, "", Table[Country/State])
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you! By decoding I mean converting CA to California, NY to New York, etc. May be I should split the column first and then decode the State?
@Anonymous you can add another table in your model with 2 digit state code and full name and then set relatonship with that table to get the full name. Populating that table must be super easy or you can connect to wikipedia using power query to get list of states and abbreviations.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Perfect! Thank you very much!
@Anonymous sure I already did the split for you and from there you can decode to long state name.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |