Need help with statement to return a value based a cell containing multiple values

[MNik]

Hi All,

I'm new to Power BI and I'm looking for help with a formula for a new column that will return a value based on the contents of a current cell. My data had duplicates for the "Components" column, so I grouped the data in a new column titled "All Status". Now I would like to return a value 1 thru 3 based on the criteria below.

If the cells contains only "To do", I would like it to return a value of 1. It doesn't matter how many times "To Do" is repeated as long as it's the only status in the cell.
If the cell contains any combination of "To Do", "Assigned", "In Progress", "Repeat Validation", "Backlog", "Done Review", or "Done", I would like it to return a value of 2.
If the cell contains only "Done", then I would like the value 3 returned. It doesn't matter how many times "Done" is repeated as long as it's the only status in the cell.

Any suggestions on how I could go about this would be greatly appreciated.

Thanks in advance!

 

[JGordon11]

In Power Query click add a custom column and paste the following as the formula

let lst = List.Distinct(Text.Split([All Status],", ")) in if List.Count(lst)  <> 1 then 2 else Number.Abs(List.PositionOf({"To Do",null,"Done"}, lst{0})) +1)

 

[MNik]

In Power Query click add a custom column and paste the following as the formula

let lst = List.Distinct(Text.Split([All Status],", ")) in if List.Count(lst)  <> 1 then 2 else Number.Abs(List.PositionOf({"To Do",null,"Done"}, lst{0})) +1)

Thank you for your response. I am getting a Token Eof expected error when pasting the formula into the custom column formula box. I have attached a screenshot for reference.

 

[JGordon11]

I pasted from the advanced editor so I shouldn't have included the last parenthesis. Try without the final ")"

let lst = List.Distinct(Text.Split([All Status],", ")) in if List.Count(lst)  <> 1 then 2 else Number.Abs(List.PositionOf({"To Do",null,"Done"}, lst{0})) +1

 

[MNik]

I pasted from the advanced editor so I shouldn't have included the last parenthesis. Try without the final ")"

let lst = List.Distinct(Text.Split([All Status],", ")) in if List.Count(lst)  <> 1 then 2 else Number.Abs(List.PositionOf({"To Do",null,"Done"}, lst{0})) +1

It worked perfectly without the extra parenthesis. This saved me a ton of time. Thank you so much for you help!