Forum Discussion

solobaz's avatar
solobaz
New Member
1 year ago
Solved

New conditional column based on multiple existing columns

Hello folks, I am trying to achieve below scenario in Power BI (query editor). Column E, is what I would like to get as an answer.
Data has many more columns, hence I would not like to change the structure. 
I have been able to solve 50% of problem, but not the remaining 50%.
Any help would be really appreciated.
Table.AddColumn(#"Added Custom6", "Membership", each if Text.Contains([Gym], "No") and Text.Contains([Swimming], "No") and Text.Contains([Skating],"No") then "No membership"
else .........

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    Hi solobaz 

    Here's one way of adding the column in a single step by combining a few functions.

    = Table.AddColumn(
    #"Added Custom6", 
    "Membership", 
    each Replacer.ReplaceValue(
      Text.Combine(Table.SelectRows(Record.ToTable([[Gym], [Swimming], [Skating]]), each [Value] = "Yes")[Name], ", "), 
      "", 
      "No Membership"
    ), 
    type text
  )

     

    You could change [Value] = "Yes" to Text.Contains( [Value], "Yes" ) if needed.

     

    Does this work for you?

     

     

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    1 year ago

    Hi solobaz 

    Here's one way of adding the column in a single step by combining a few functions.

    = Table.AddColumn(
    #"Added Custom6", 
    "Membership", 
    each Replacer.ReplaceValue(
      Text.Combine(Table.SelectRows(Record.ToTable([[Gym], [Swimming], [Skating]]), each [Value] = "Yes")[Name], ", "), 
      "", 
      "No Membership"
    ), 
    type text
  )

     

    You could change [Value] = "Yes" to Text.Contains( [Value], "Yes" ) if needed.

     

    Does this work for you?