PowerBI - Conditional Column exact match

user_15224345 0
Viewed 103

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
powerbi powerquery match conditional-statements
2 Answers

I have a trick that I've previously used in VBA when processing sentences.

  1. Turn the string of text into a list using the Text.Split function with " " (space) as separator. It gives you a list of the words in your string.
  2. Look into this list for either "formal" or "informal" this the List.Contains function.

Here is the bit of code for the column "Formal":

= Table.AddColumn(#"Changed Type", "Formal", each if List.Contains(Text.Split([Action], " "), "formal") then "Y" else null)
user_58872670

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"
user_92642300
Related