Categorize column if it contains part of text

[webportal]

My table is a bank statement, that looks like this:

Data Mov.	Data Valor	Descrição do Movimento	Valor em EUR
17-02-2017	17-02-2017	CONSTITUICAO DEPOSITO A PRAZO 123/004	-2.000
17-02-2017	17-02-2017	TRF 0000082 CUSTOMER A	368
17-02-2017	17-02-2017	TRF 0000082 CUSTOMER B	66
17-02-2017	17-02-2017	TRF 0000082 CUSTOMER C	98
17-02-2017	17-02-2017	TRF 0000082 CUSTOMER D	861
18-02-2017	18-02-2017	PAYPAL TRANFER CPTTO	1000
20-02-2017	20-02-2017	COMPRA ALGUMA COISA 123	-500

I want to categorize each row according to the text in the description column (Descrição do Movimento).

To do that, I'm trying a SWITCH / CONTAINS (or SEARCH) construct.

If the text contains "TRF", then the column should be: "Transfer"

If the text contains "PAYPAL", then the column should be: "Paypal"

If the text contains "COMPRA", then the column should be: "Expense".

My first attempt is to check wether CONTAINS works, but it doesn't seem to...

Categoria = CONTAINS('ContaBancoX';ContaBancoX[Descrição do Movimento];"TRF")

This is returing FALSE for every row which doesn't seem to make sense to me, so I can't get started.

[mogunhan]

Hi

I have Excel File contains two sheet

Sheet1: contains following and i have more than 200 Sno of Red defects:

Sheet2: contains the Checks and Segments.

I have Power BI Desktop not having Power BI Premium.

I want measure that Logical/Contain Strings/Relative/ Red Defects to be segments from Sheet2 list.

Please help me in detail. I am in early stage of Power Bi.

Thank You.

With Best Regards

E. Mohan

Sheet1:

S.No:	Red Defcts	Segments
1	1- no spare tyre for PM
2	No JP,
3	Transport boxes inside driver cabin
4	RAS expaired for Tanker
5	1-  driver not have DDC3
6	1.Lowbed different tire on same axle
7	1.differenttires on the same axle for low-bed2.no spare wheel for PM 3.certificate for the 5th wheel is not provided4.no high-intensity light 5.The reverse light & audible not working
8	1- reverse Alarm for tanker not working
9	1.Missing one warning triangle. 2. Reverse audible warning not working. 3.High intensity light not working.

Sheet2

S.No	Checks	Segments
1	Prime mover: Front Driver side tyre and wheel	Steer Axle Tyres
2	Prime mover: Front passenger side tyre and wheel	Steer Axle Tyres
3	Prime Mover drive axle rear tyres and wheels	Tyres & Wheel's
4	Trailer driver side tyres and wheels	Tyres & Wheel's
5	Trailer passenger side tyres and wheels	Tyres & Wheel's
6	Spare wheels	Tyres & Wheel's
7	Battery cover	Electrical & Air Systems
8	Electrical and Air Connection	Electrical & Air Systems
9	Prime Mover Brake actuators	Brakes - Air System - Leak
10	Trailer Brake actuators	Brakes - Air System - Leak
11	Footbrake	Brakes - Air System - Leak
12	Handbrake	Brakes - Air System - Leak
13	Brake chambers (trailer)	Brakes - Air System - Leak
14	Brakes Air leaks	Brakes - Air System - Leak
15	Air tank drain valves	Brakes - Air System - Leak
16	Brake function test	Brakes - Air System - Leak
17	Load bed condition (wood)	BODY
18	Prime Mover/trailer General Body Condition	BODY
19	Trailer headboard	BODY
20	Trailer landing gear (legs)	BODY
21	5th wheel and kingpin coupling	BODY
22	Spare wheel carriers	BODY
23	Payload stenciled on trailer	BODY
24	Trailer side stanchions	BODY
25	Suspension, pins and bushes	Suspension
26	Cab locking mechanism	Suspension
27	Headlights	Light's
28	Side marker lamps	Light's
29	Rear running lights	Light's
30	High Intensity Light	Light's
31	Reverse light and audible warning	Light's
32	Direction / hazard indicators lights	Light's
33	Rear amber beacon	Light's
34	Brake lights position and function (prime mover & Trailer)	Light's
35	Rear fog light position and function	Light's
36	Fuel tank	Fuel & Leak's
37	Fuel, oil or water leaks	Fuel & Leak's
38	Windscreen	Mirror's & Windscreen
39	Mirrors	Mirror's & Windscreen
40	Wipers & washers	Driver Vision
41	Seat	Truck(In Cabin)
42	Seatbelt	Truck(In Cabin)
43	Air conditioning	Truck(In Cabin)
44	Music system / Radio	Truck(In Cabin)
45	Sun visor	Truck(In Cabin)
46	Speedometer /speed limiter	Truck(In Cabin)
47	TPMS	Truck(In Cabin)
48	DFMS	Truck(In Cabin)
49	Dashboard warning lights	Truck(In Cabin)
50	Horn/reverse alarm.	Audible Warning
51	PM & Trailer Fire extinguisher	Emergency Equipment
52	First Aid kit PDO specs	Emergency Equipment
53	Warning triangles	Emergency Equipment
54	Driver PPE	PPE
55	Rear hi vis markings	Marking
56	Chain and chain binders (Ratchet) x 4	Lashing Equipment/Loading Tools
57	Webbing belts (2 inch) x 10 Webbing belts (4 inch) x 4	Lashing Equipment/Loading Tools
58	Sufficient dunnage, mats and stoppers	Lashing Equipment/Loading Tools
59	Shipping container locks	Lashing Equipment/Loading Tools
60	Steering assembly(Visual)	Assembly
61	Water Supply & Tank	Water Spray Suppression
62	Other dangerous defects	Major Defect

[Sean]

Category DAX =
SWITCH (
    TRUE ();
    SEARCH ( "TRF"; 'ContaBancoX'[Descrição do Movimento];; 0 ) = 1; "Transfer";
    SEARCH ( "PAYPAL"; 'ContaBancoX'[Descrição do Movimento];; 0 ) = 1; "Paypal";
    SEARCH ( "COMPRA"; 'ContaBancoX'[Descrição do Movimento];; 0 ) = 1; "Expense";
    "N/A"
)

[cherrypbi]

this didn't work for me, why are you using semicolon?

[webportal]

I was just beggining to explore the SWITCH(TRUE())  

Great tip, it works!

Thank you

[Sean · Accepted solution]

@webportal

You can again use the Query Editor to create a Conditional Column or the DAX Measure above

Hope this helps!

[webportal]

Great stuff, this conditional column is perfect for categorizing rows based on text!

[ImkeF]

I would also prefer the solution in the query-editor, but beware of the different handling of case-sensitivity:

While the DAX-expression isn't case sensitive, the conditional column is by default. But you can change that:

http://www.thebiccountant.com/2016/10/27/tame-case-sensitivity-power-query-powerbi/

[webportal]

The disadvantage of the conditional column is not accepting AND conditions.

[webportal]

Just one more thing.

The formula:

Category DAX =
SWITCH (
    TRUE ();
    SEARCH ( "TRF"; 'ContaBancoX'[Descrição do Movimento];; 0 ) = 1; "Transfer";
    SEARCH ( "PAYPAL"; 'ContaBancoX'[Descrição do Movimento];; 0 ) = 1; "Paypal";
    SEARCH ( "COMPRA"; 'ContaBancoX'[Descrição do Movimento];; 0 ) = 1; "Expense";
    "N/A"
)

Should be replaced by:

Category DAX =
SWITCH (
    TRUE ();
    SEARCH ( "TRF"; 'ContaBancoX'[Descrição do Movimento];; 0 ) >= 1; "Transfer";
    SEARCH ( "PAYPAL"; 'ContaBancoX'[Descrição do Movimento];; 0 ) >= 1; "Paypal";
    SEARCH ( "COMPRA"; 'ContaBancoX'[Descrição do Movimento];; 0 ) >= 1; "Expense";
    "N/A"
)

Because the text to search isn't always in the 1st position. 

[ImkeF]

It accepts and, you just have to hand-code it. Pls let me know if you need any help for that.

[webportal]

@ImkeF

That would be helpful, thanks!

[ImkeF]

Please check out this formula:

Table.AddColumn(Source, "Custom", each if (Text.Contains([Descrição do Movimento], "TRF") and Text.Contains([Descrição do Movimento], "Customer A", Comparer.OrdinalIgnoreCase)) then "TransferA" else if Text.Contains([Descrição do Movimento], "PAYPAL") then "Paypal" else null )

It contains 2 conditions for the first field returned (and an optional condition for a case-insensitive match, otherwise you would have had to write "CUSTOMER A" instead).

Is that what you need?

[baraak]

@ImkeFyou the best. you saved my life!