Create a calculated column based on string start and length

[FrederikAhring]

Hi, i have a bunch of strings (serial numbers) and i want to categorize them accordingly with a new calculated column

The lenght is important to me, only if the serial starts with AA.
Really hope you can help

Input	Desired output
AA12345678910	AA-long
AA1234	AA-Short
AA12345678911	AA-Long
AA1235	AA-Short
BB123212

[v-yiruan-msft · Accepted solution]

Hi @FrederikAhring ,

You can create a calculated column as below to get it:

Desired output =
IF (
    LEFT ( 'Table'[Input], 2 ) = "AA",
    IF ( LEN ( 'Table'[Input] ) <= 6, "AA-Short", "AA-Long" ),
    BLANK ()
)

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @FrederikAhring ,

You can create a calculated column as below to get it:

Desired output =
IF (
    LEFT ( 'Table'[Input], 2 ) = "AA",
    IF ( LEN ( 'Table'[Input] ) <= 6, "AA-Short", "AA-Long" ),
    BLANK ()
)

Best Regards

[FrederikAhring · Accepted solution]

Thanks a lot @v-yiruan-msft.

It seems to work perfectly

[DataInsights]

@FrederikAhring,

Try this custom column in Power Query:

if Text.Start([Input], 2) = "AA" then
  if Text.Length(Text.Middle([Input], 3, Text.Length([Input]))) > 4 then
    "AA-Long"
  else
    "AA-Short"
else
  null