Forum Discussion
Create Custom Column based on specific combinations of values from another column
Hello, How can a new Task Status column be created based on the table and criteria below:
| Task | Region | Country | Task Status | NEW DESIRED COLUMN |
| 1 | EMEA | Finland | Complete | In Progress |
| 1 | EMEA | Sweden | Issues | In Progress |
| 1 | EMEA | Germany | Not Started | In Progress |
| 2 | EMEA | Finland | Complete | In Progress |
| 2 | EMEA | Sweden | Issues | In Progress |
| 2 | EMEA | Germany | Not Started | In Progress |
| 2 | EMEA | Spain | Complete | Completed |
| 3 | EMEA | Sweden | Complete | Completed |
| 3 | EMEA | Spain | Complete | Completed |
I need to create a new coulmn to show me for each TASK:
If Task Status column contains "Not Started", "Issues" & "Completed" then = "In Progress"
If Task Status column contains "Not Started" & "Issues" then = "In Progress"
If Task Status column contains "Not Started" & "Completed" then = "In Progress"
If Task Status column contains "Issues" & "Completed" then = "In Progress"
If Task Status column contains "Not Started" ONLY then = "Not Started"
If Task Status column contains "Issues" ONLY then = "Issues"
If Task Status column contains "Completed" ONLY then = "Completed"
End Result needed to Display:
| Task | Region | New Task Status |
| 1 | EMEA | In Progress |
| 2 | EMEA | In Progress |
| 3 | EMEA | Completed |
= Table.Group(Source,{"Task","Region"},{"Task Status",each let a=List.Distinct([Task Status]) in if a{1}?=null then a{0} else "In Progress"})
2 Replies
= Table.Group(Source,{"Task","Region"},{"Task Status",each let a=List.Distinct([Task Status]) in if a{1}?=null then a{0} else "In Progress"})