New conditional column based on multiple existing columns

[solobaz]

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 .........

[solobaz]

yes @OwenAuger  it worked perfectly! thank you.

[OwenAuger · Accepted solution]

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?