Create a column with new text based on character length of another column

[veronica_zappp]

Hello,

I'm having trouble finding an efficient way of setting the following command, and as new Power BI user with limited M and DAX experience, not sure how to best go about this.

In short, I'd like to create a new column of string values, based on length of string in another column.

More specifically, in my original column (LocationTag), I have acronyms for locations.

The locations are either 3 or 4+ length character acronyms.

I'd like to have a new column with information based on the length of the string in my original column.

• If the location is 3 characters, its string in the new column will be "Home"
• If the location is 4+ characters, its string in the new column will be "Away".
• There are never less than 3 character tags

LocationTag	New column
ABCD	Away
BCDEF	Away
OSS	Home
OMA	Home

Manual replace values in the GUI is not an option as I have hundreds of different types of location tags. I'd also like to avoid searching/typing specific text and assigning it a new text (in the new column). I need to create the new column based on length of text string in the original column (LocationTag).... does Replace.Value based on character string length exist?

My thinking is something related to this:

if string is 3 characters in LocationTag 

then, "Home"

if string is equal to or more than 4 characters

then, "Away"

Thank you very much for your help!

[v-henryk-mstf · Accepted solution]

Hi @veronica_zappp ,

The function used is the same as the one provided by @rsbin , which is to return the length of a given string. Need a judgment condition has been added to this.

result =
IF (
    LEN ( 'Table'[LocationTag] ) < 3,
    "error",
    IF (
        LEN ( 'Table'[LocationTag] ) = 3,
        "home",
        IF ( LEN ( 'Table'[LocationTag] ) > 3, "away" )
    )
)

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rsbin]

@veronica_zappp ,

Using DAX, create a new Calculated Column.  You are on the right track with your IF statement, but 

I now prefer using the SWITCH function instead of the usual IF Statement.

You just need to be made aware of the LEN function.

NewColumn = SWITCH(
                TRUE(),
                LEN(YourTable[LocationTag]) = 3, "Home",
                "Away")

 This is my source for the comprehensive list of DAX functions:
https://docs.microsoft.com/en-us/dax/

Hope this helps.

Regards,