PowerQuery Replace Text Values Multiple If statement

0
votes

Using PowerQuery I have a column with long RAG descriptions eg "No Significant Issues [Green]" which I want to replace with R, A or G based on the text or leaves as is if not match found.

The following code works for Green but is not replacing the Amber statement. I have copied the text exactly.

= Table.ReplaceValue(#"Reordered Columns",each[RAGQuality],each if [RAGQuality]="No Significant Issues [Green]" then "G" else each if [RAGQuality]="Significant Issues [Amber]" then "A" else [RAGQuality], Replacer.ReplaceText,{"RAGQuality"})

Any help appreciated.

2
replacepowerquerynested-if

2 Answers

0
votes

I think the else each if might need to be else if. Try:

= Table.ReplaceValue(#"Reordered Columns", each [RAGQuality], each if [RAGQuality] = "No Significant Issues [Green]" then "G" else if [RAGQuality] = "Significant Issues [Amber]" then "A" else [RAGQuality], Replacer.ReplaceText, {"RAGQuality"})
  • I think your code is making a case-sensitive comparison (and therefore won't, for example, replace "No significant issues [Green]" with "G"). This might be exactly what you want, but I mention it in case it's not. Comparer.OrdinalIgnoreCase can be used to make case-insensitive comparisons.
  • There doesn't seem to be an else if clause for R/[Red] in the code, but maybe you haven't gotten that far yet.
  • You might want to look at Record.FieldOrDefault which can reduce the number of if checks you're making (or creating some sort of record or list of the replacements you need to make, and then just loop over it).
0
votes

This has done the trick:

= Table.ReplaceValue(#"Reordered Columns",each[RAGQuality],each if Text.Contains([RAGQuality], "[Red]") then "n" else if Text.Contains([RAGQuality], "[Amber]") then "u" else if Text.Contains([RAGQuality], "[Green]") then "l" else [RAGQuality], Replacer.ReplaceText,{"RAGQuality"})

And is a neater solution than looking for each text to replace as now easy to edit column names for other 5 columns which all have [Red] or [Amber] or [Green] as the key text but differing remaining text