I have a column below 'Action' which I want to derive two conditional columns from 'Informal' and 'Formal'. If there is an informal in the Action column then I want a 'Y' in the Informal column and likewise if there is a formal then 'Y' in Formal column.
Using PowerQuery with the following I obtained those columns with this:
Table.AddColumn(#"Filtered Rows", "Informal", each if Text.Contains([#"Action"], "informal") then "Y" else if Text.Contains([#"Action"], "Informal") then "Y" else null)
and
Table.AddColumn(#"Added Conditional Column1", "Formal", each if Text.Contains([#"Action"], "formal") then "Y" else if Text.Contains([#"Action"], "Formal") then "Y" else null)
However as seen there is duplication of the two columns as formal is in informal. How would I do an exact match (not case sensitive) in the PowerQuery environment. Desired table below example table.
Dataset
| Action | Informal | Formal |
|---|---|---|
| Informal discussion | Y | Y |
| formal counselling with formal warning | Y | |
| formal counselling and formal warning | Y | |
| Welfare check. Informal meeting. | Y | Y |
| Informal counselling and mediation | Y | Y |
| formal counselling second formal warning | Y | |
| Informal Counselling | Y | Y |
| Formal counselling - formal warning pending mitigations | Y | |
| Formal meeting, pending mitigations a formal warning | Y | |
| formal counselling + formal warning. Informal counselling for 2 other respondents pending involvement. | Y | Y |
Desired table:
| Action | Informal | Formal |
|---|---|---|
| Informal discussion | Y | |
| formal counselling with formal warning | Y | |
| formal counselling and formal warning | Y | |
| Welfare check. Informal meeting. | Y | |
| Informal counselling and mediation | Y | |
| formal counselling second formal warning | Y | |
| Informal Counselling | Y | |
| Formal counselling - formal warning pending mitigations | Y | |
| Formal meeting, pending mitigations a formal warning | Y | |
| formal counselling + formal warning. Informal counselling for 2 other respondents pending involvement. | Y | Y |
I assume that if Informal is Y then Formal has to be null so why not set the formula for Formal to be
= if [Informal] ="Y" then null else "Y"