Conditionally format cells in column based on text value of another column

[Anonymous]

Hello,

I have the following table and i want to conditionally format the "Action date" column cells based on the text in "Category" column.

1. If text "Late", then add red circle icon next to value.

2. If text "On time", then add green circle icon next to value.

3. If text "No action", then add yellow circle icon next to value.

4. If text "Undefined", then add dash icon next to value.

Any help is much appreciated!

Document ID	Start date	End date	Action date	Stock	Returns	Defective	Category
3998	01/05/2021	01/06/2022	01/07/2022	6	9	17	Late
4050	27/05/2022	27/06/2022	27/06/2022	2	5	10	On time
4050	07/08/2022	07/09/2022	07/08/2022	5	6	15	On time
4052	14/07/2022	11/08/2022		5	6	15	Undefined
4060	08/08/2022	08/09/2022		3	4	8	No action
4060	12/08/2022	20/09/2022	22/09/2022	2	2	10	Late
4139	17/08/2022	27/09/2022	23/08/2022	7	8	22	On time
4145	20/08/2022	10/10/2022		3	3	13	No action
4149	22/08/2022	02/09/2022	22/09/2022	6	3	18	Late
4157	29/08/2022	09/10/2022	02/10/2022	3	3	14	On time
4158	18/09/2022	15/10/2022		3	3	14	Undefined
4160	07/09/2022	17/10/2022		2	7	17	No action
4160	16/09/2022	06/10/2022	19/09/2022	3	7	22	On time
4164	20/10/2022	23/11/2022	15/11/2022	9	3	16	On time
4166	23/11/2022	02/12/2022	23/12/2022	7	5	20	Late
4187	11/12/2022	15/01/2023		9	8	18	No action
4187	04/01/2023	04/03/2023	17/02/2023	6	7	16	On time
4187	17/02/2023	13/03/2023	01/03/2023	7	14	34	On time
4189	11/12/2022	15/01/2023		9	8	18	No action

[jsaunders_zero9 · Accepted solution]

I would create a conditional column in Power Query to assign a number to each "status" (conditional formatting likes numbers).

Then you can apply conditional formatting based on the number value

Giving this result.