Forum Discussion
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 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
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.
1 Reply
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.