Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

PowerQuery custom column returns "Active" when [reference column] contains any of the specified word

I have 2 columns

ExtractWork Status
AMService Stabilization
AGService Stabilization
AGIn progress
AGService Delivery

 

Now for the custom column I'm trying to return "Yes" if [Extract] = "AG" and [Work Status] = *to any of these words {Service Stabilization, Service Delivery}

 

ExtractWork StatusActive
AMService Stabilization 
AGService StabilizationYes
AGIn progress 
AGService DeliveryYes

 

How do put multiple words as a criteria on [Work Status]

I tried doing 

if [Extract] = "AG" and [Work Status] = { "Service Stabilization", "Service Deliver"} then "Yes" else""

I also tried

If [Extract] ="AG" and [Work Status] = "Service Stabilization" or "Service Delivery" then "Yes" esle ""

 

both only seems to output "Yes" *ONLY when [Work Status] is Service Stabilization, what might be the correct syntax for it

 

  • You may use List.Contains.

    if [Extract]="AG" and List.Contains({ "Service Stabilization", "Service Delivery"},[Work Status]) then "Yes" else "No"

2 Replies

  • You may use List.Contains.

    if [Extract]="AG" and List.Contains({ "Service Stabilization", "Service Delivery"},[Work Status]) then "Yes" else "No"

    • Anonymous's avatar
      Anonymous
      Not applicable

      Wow thanks i never thought funcitions like that works inside custom columns. Thank you