topic Re: Extract code from long description in Desktop

Extract code from long description

Anonymous — Wed, 20 Nov 2019 07:58:56 GMT

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,

Re: Extract code from long description

Anonymous — Wed, 20 Nov 2019 08:41:32 GMT

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.

Re: Extract code from long description

AnkitBI — Wed, 20 Nov 2019 09:57:31 GMT

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

Re: Extract code from long description

Anonymous — Wed, 20 Nov 2019 10:03:25 GMT

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

Re: Extract code from long description

Anonymous — Thu, 21 Nov 2019 02:46:45 GMT

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!

Re: Extract code from long description

v-eachen-msft — Thu, 21 Nov 2019 09:55:09 GMT

Hi Anonymous ,

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