Conditonal Column

[amandabus21]

(Last conditon) 

Is there a way I can make it that the word "PED" is its own word. When using "contains" in my conditional column, I keep getitng a result where PED is in a word and not its own word. 

Example I dont "WIPED" as a result.

Last part of editor:

else if Text.Contains([Case Description.Case Description Level 01], "PED") then "Pedestrians" else "N/A")

[DemoFour]

@amandabus21 

There was only one example in the sample , but if the logic of the text works the same for each cell, this should help. 
If the logic is different, then you may have to do different transformations to get all of them but it is possible. 

#"Capitalized Each Word" = Table.TransformColumns(#"PREVIOUS STEP",{{"Case Description", Text.Proper, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Capitalized Each Word"," Ped "," Pedestrian ",Replacer.ReplaceText,{"Case Description"})
in
    #"Replaced Value"

[DemoFour]

@amandabus21 Can you post up some sample data and I can have a look at what can be done to help. 

[amandabus21]

Date	Bus ID	Reason (0CSM_RCOD)	Case Description	Medics	Tow	Significant Events
12/7/2022 0:00	1707	TURNING FRONT SIDEWAYS	PAX FELL OUT OF SEAT - TRANSPORTED	Yes	No	Ambulance
12/17/2022 0:00	1811	ALIGHTING MIDDLE / REAR STEP DOWN EXIT	PAX FELL ON REAR STEP EXITING BUS - TRANSPORT	Yes	No	Ambulance
12/26/2022 0:00	1807	TURNING FORWARD BARRIER	PAX FELL OFF SEAT - TRANSPORT	Yes	No	Ambulance
1/1/2023 0:00	1810	ROADWAY INTERSECTION	CAR RAN RED HIT ANOTHER CAR & BUS, TRANSPORT & TOW	Yes	Yes	Ambulance
1/15/2023 0:00	1126	SLIP WEATHER	PAX SLIPPED ON WET FLOOR AS BUS LEFT STOP - TRANSPORT	Yes	No	Ambulance
1/20/2023 0:00	1308	BRAKING STANDING PAX	STANDING PAX FELL AFTER CAR CUT OFF BUS - TRANSPORT	Yes	No	Ambulance
1/27/2023 0:00	1304	EQUIPMENT W/C LAP BELT NOT USED	W/C PAX FELL OFF CHAIR AS BUS CAME TO A STOP - TRANSPORT	Yes	No	Ambulance
12/8/2022 0:00	1705	FIXED OBJECT	BUS HIT BIKE LANE DELINEATOR	No	No	Bicycles/Scooters
12/12/2022 0:00	1409	FIXED OBJECT	BUS HIT BIKE LANE DELINEATORS	No	No	Bicycles/Scooters
12/16/2022 0:00	1725	FIXED OBJECT	BUS RAN OVER BIKE LANE DELINEATOR NO DAMAGE	No	No	Bicycles/Scooters
1/11/2023 0:00	718	BOARDING RAMP ENTER	PAX FELL OFF SCOOTER USING W/C RAMP	No	No	Bicycles/Scooters
1/19/2023 0:00	1603	EQUIPMENT W/C LAP BELT NOT USED	SCOOTER TIPPED WHEN BUS MADE TURN	No	No	Bicycles/Scooters
1/21/2023 0:00	807	BRAKING FRONT SIDEWAYS	PAX FELL AFTER TRUCK TURNED IN FRONT OF BUS IN BIKE LANE	No	No	Bicycles/Scooters
12/2/2022 0:00	1710	VEHICLE REVERSE	TRUCK BACKED INTO STOPPED BUS	No	No	Pedestrians
12/3/2022 0:00	810	PEDESTRIAN	BUS COLLIDED WITH PED IN CROSSWALK NO INJURY	No	No	Pedestrians
12/6/2022 0:00	1124	WITH PARKED VEHICLE	BUS SIDESWIPED PARKED RV	No	No	Pedestrians
12/24/2022 0:00	1820	VEHICLE OVERTAKING BUS	CAR CA LP #8VDW923 SIDESWIPED BUS HIT & RUN	No	No	Pedestrians
12/26/2022 0:00	1711	ALIGHTING RAMP EXIT	W/C TIPPED OVER WHILE EXITING W/C RAMP	No	No	Pedestrians
12/27/2022 0:00	1122	VEHICLE OVERTAKING BUS	TRUCK BACKED INTO SIDE OF STOPPED BUS	No	No	Pedestrians
1/5/2023 0:00	801	VEHICLE REVERSE	TAXI BACKED INTO STOPPED BUS	No	No	Pedestrians
1/11/2023 0:00	704	ALIGHTING RAMP EXIT	PAX TRIPPED OVER EDGE OF RAMP EXITING BUS	No	No	Pedestrians
1/20/2023 0:00	1206	VEHICLE OVERTAKING BUS	CAR SIDESWIPED STOPPED BUS, HIT & RUN	No	No	Pedestrians
1/24/2023 0:00	814	BOARDING RAMP ENTER	W/C PAX TIPPED BACKWARDS ON W/C RAMP	No	No	Pedestrians
1/27/2023 0:00	1212	BUS REAR ENDS ANOTHER VEHICLE	BUS REARENDED STOPPED CAR - NO REPORTED DAMAGE	No	No	Pedestrians
1/30/2023 0:00	1803	VEHICLE OVERTAKING BUS	SUV MAKING U-TURN HIT SIDE OF STOPPED BUS	No	No	Pedestrians
1/30/2023 0:00	202	VEHICLE HIT BUS SERVICING STOP	SUV HIT BACK OF STOPPED BUS	No	No	Pedestrians
12/20/2022 0:00	1303	VEHICLE OVERTAKING BUS	CAR SIDESWIPED BUS ON FWY - TOW	No	Yes	Tow Truck

[Peter_Beck]

I see. You could consider altering the Text.Contains clause to include spaces on either end of the word, so you are testing for " PED " (note the spaces before P and after D.

You could then add additional clauses to capture cases where the line begins with "PED " or ends with " PED"

Paste this code into the Advenced Editor to see what I mean.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYxRDsIwDEOvYvWbS3RrWAMlQU2mClW7/zUolQb82Xp+7j0su2HVUjhRQmPPeI7AgrWqWYvlDtHRb3t9hePSg+mDPLNs+KXGQ5p0dIMKgSTZ92+i+WuIDs+EhTYW+ch6xWmdu1hgHqvbHzne", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Col1], " PED ") then "Pedestrian" else if Text.StartsWith([Col1], "PED ") then "Pedestrian" else if Text.EndsWith([Col1], " PED") then "Pedestrian" else null)
in
#"Added Conditional Column"

Hope this helps!

Peter

[amandabus21]

Thanks! But using this is giving me the same output as just using "contains"

[DemoFour]

Hi @amandabus21 
Can you not use the UI and Replace Values to change the word Ped before adding your conditional column? 

[amandabus21]

I tried doing that but now I find values in the text like 

"PAX TRIPPedestrian BOARDING BUS"

[Peter_Beck]

Hi -

Can you not use the "equals" operator, instead of "contains"?

Peter.

[amandabus21]

No because its a word in a whole sentence for example "BUS COLLIDED WITH PED IN CROSSWALK NO INJURY"