Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Conditional colomn in query editor

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 h...
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    3 years ago

    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. 

Sahir_Maharaj's avatar
Sahir_Maharaj
Super User
3 years ago

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.