Forum Discussion

FrederikAhring's avatar
FrederikAhring
Frequent Visitor
4 years ago
Solved

Create a calculated column based on string start and length

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

 

InputDesired output
AA12345678910AA-long
AA1234AA-Short
AA12345678911AA-Long
AA1235AA-Short
BB123212 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

3 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      FrederikAhring
      Frequent Visitor
      4 years ago

      Thanks a lot Anonymous.

      It seems to work perfectly