It works almost perfect but I still have one mistake.
In the following formula
If(ColumnB = 'Y',If(Len(Trim(ColumnD))=0,'Value7',If(ColumnD='Root','Value8','Value9'))) AS ColumnI,
Column D contains (or not) the word "Root" but it not alone. It means, the content of the cell could be "Root cause: Supplier" or "Root cause: Manufacturing". At the moment, with the formula above, it displays in ColumnI either Value7 or Value9 but never Value8. Can you maybe support here? I tried putting *Root* and it is unfortunately not working.