Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
You can again use the Query Editor to create a Conditional Column or the DAX Measure above
Hope this helps!
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. |
|
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 | 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 |
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" )
this didn't work for me, why are you using semicolon?
I was just beggining to explore the SWITCH(TRUE())
Great tip, it works!
Thank you
You can again use the Query Editor to create a Conditional Column or the DAX Measure above
Hope this helps!
Great stuff, this conditional column is perfect for categorizing rows based on text!
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/
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
The disadvantage of the conditional column is not accepting AND conditions.
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.
It accepts and, you just have to hand-code it. Pls let me know if you need any help for that.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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?
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |