Conditional colomn in query editor

[Anonymous]

Dear, 

I have an issue where we work with a lot of different zone types. Now i want to narrow these down to just 3 option so i think i have to add an extra colomn in the query editor. However i have no experience with the DAX formula's etc. 

The 2 options are "Koel/vries" and "Ambient"

The following zones should all result in the kolomn calculating "Koel/vries":

+2°C +8°C

-20°C -25°C

-35°C -45°C

-65°C -85°C

-70°C -90°C

BHSC

BHSDGC

Bonded Cool

BRICS

CD2 +2°C +8°C

COOL_AND_FREEZE

CY +2°C +8°C

DG1 +2°C +8°C

DG1 -65°C -85°C

DG3 +2°C +8°C

HS +2°C +8°C

KROON_ZONE

The following zones should all result in the kolomn calculating "Ambient":

+15°C +25°C

BHSA

Bonded Ambient

BONDED_AND_AMBIENT

CD +15°C +25°C

CY +15°C +25°C

DG1 +15°C +25°C

DG2 +15°C +25°C

DG3 +15°C +25°C

HI +15°C +25°C

HS +15°C +25°C

Everything else should result in "onbekend"

Hopefully you can help

BR Kevin

[Sahir_Maharaj · Accepted solution]

Hello @Anonymous,

Can you please add a new column and use the following formula in the "Custom column formula" section:

if Text.Contains([Zone Type], "+2°C +8°C") or 
   Text.Contains([Zone Type], "-20°C -25°C") or
   Text.Contains([Zone Type], "-35°C -45°C") or
   Text.Contains([Zone Type], "-65°C -85°C") or
   Text.Contains([Zone Type], "-70°C -90°C") or
   Text.Contains([Zone Type], "BHSC") or
   Text.Contains([Zone Type], "BHSDGC") or
   Text.Contains([Zone Type], "Bonded Cool") or
   Text.Contains([Zone Type], "BRICS") or
   Text.Contains([Zone Type], "CD2 +2°C +8°C") or
   Text.Contains([Zone Type], "COOL_AND_FREEZE") or
   Text.Contains([Zone Type], "CY +2°C +8°C") or
   Text.Contains([Zone Type], "DG1 +2°C +8°C") or
   Text.Contains([Zone Type], "DG1 -65°C -85°C") or
   Text.Contains([Zone Type], "DG3 +2°C +8°C") or
   Text.Contains([Zone Type], "HS +2°C +8°C") or
   Text.Contains([Zone Type], "KROON_ZONE") 
   then "Koel/vries"
else if Text.Contains([Zone Type], "+15°C +25°C") or
        Text.Contains([Zone Type], "BHSA") or
        Text.Contains([Zone Type], "Bonded Ambient") or
        Text.Contains([Zone Type], "BONDED_AND_AMBIENT") or
        Text.Contains([Zone Type], "CD +15°C +25°C") or
        Text.Contains([Zone Type], "CY +15°C +25°C") or
        Text.Contains([Zone Type], "DG1 +15°C +25°C") or
        Text.Contains([Zone Type], "DG2 +15°C +25°C") or
        Text.Contains([Zone Type], "DG3 +15°C +25°C") or
        Text.Contains([Zone Type], "HI +15°C +25°C") or
        Text.Contains([Zone Type], "HS +15°C +25°C") 
   then "Ambient"
else "onbekend"

This checks each zone type value against the criteria using the Text.Contains function.

Do not hesitate to let me know if you might need further assistance.