Extract code from long description

[Anonymous]

Hi Guys, 

I have a list of assets such as buses, trucks, cranes, etc. Each record has within its description a CATII code (Category Code). The description differs between each asset.

Each CATII code is a letter followed by 2 numbers, i.e. C02, V56, W01, etc. 

Here is an example: 

Franna - C02 - >=16t Franna AT-20 & AT-22

G04 - >100kva

G03 - 50-<100kva

Rough Terrain - C14 - Rough Terrain 70t

A03-Compressor 101-200cfm

Diesel (Shindaiwa) - W01 - Weler - <=400A

Franna - C01 - <=15t Franna AT-15

Bus - V41 - Bus 13-25 Seats

Semi - T02 - Trailer - Semi Drop Deck

Truck (Other) - V91 - Water (Dust Suppression)

G02 - 10-<50kva

Diesel (Lincoln) - W01 - Welder - <=400A

A05-Compressor >300cfm

Utility 4x4 Dual Cab - V23 - Vehicle - Utility 4x4 D/C

Truck 4x2 Single Cab - V50 - Truck SC <=5T C/C

Diesel (Lincoln) - W02 - Welder - 500A

Prime Mover - V92 - Prime Mover

Extendable - T05 - Trailer Extendable

Truck 4x4 Dual Cab - V84 - Truck 4x4 DC >9T C/C

Truck 4x4 Dual Cab - V81 - Truck 4x4 DC <=5T C/C + Crane

Truck 4x4 Single Cab - V75 - Truck 4x4 SC >9T C/C + Crane

What can I use in either Power Query or DAX to extract the CATII from each one of these descriptions? Given that, for instance, splitting text before number will return "Truck 4" | "x4 Single Cab..."

Looking forward to hearing some ideas. 

Cheers, 

[Anonymous · Accepted solution]

Having a list of CATII codes is key. 

You can follow this thread which answers a similar problem: 

https://community.powerbi.com/t5/Desktop/String-lookup-with-0-to-N-partial-matches/m-p/197641

[Anonymous]

Thanks TijnOnline. I got it sorted: 

CATII =

VAR find_catii = MAXX( CATIIControl , SEARCH( CATIIControl[CATII] , RequestswithNoAssetID[Asset Capacity] , 1, 0 ))

VAR return_catii = MID( RequestswithNoAssetID[Asset Capacity] , find_catii , 3 )

VAR result = IF( find_catii = 0, "Not in Fleet" , return_catii )

RETURN result

Cheers!

[v-eachen-msft]

Hi @Anonymous ,

You could accpet the reply you like if your question has been solved.

[Anonymous]

I have a list of all the CATII codes if that makes it easier? 

I've been experimenting with Text.Select but haven't had any luck yet. 

[AnkitBI]

Hi - Please share the list. Will check with and without.