Long time viewer, first time poster.
I've been learning Power Query as part of Excel 365 and now I've reached another plateau. I can create conditional columns. However, now I'm wondering if there is a better way to approach multi-conditional columns.
I'm not explicitly asking for a solve to the below as a one off, but as a basis to learn a better way.
Purpose: I import csv with a list of users who have a field with the states they use. That field is [State Skills] and the values are as AL, AK, CT, CA, up to 50. I then do a Column Split of State Skills with each state into it's own column. Which creates [State Skills.1] as the first column of many.
Then I create the first conditional column which looks in [State Skills.1] for the State Abbreviation and ENVIRONMENT equals GROUPX then OPTIONX.
Some users will have 1 State and some will have 50. I don't want to create 50 Columns one for Each State because the end result is I copy the values after into another sheet for submission.
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Environment ", "Environment"}}),
SG01 = Table.AddColumn(#"Renamed Columns1", "SG01", each
if [State Skills.1] = "AL" and [Environment] = "GROUP A" then "OPTION1_AL_XXXX" else
if [State Skills.1] = "AL" and [Environment] = "GROUP B" then "OPTION2_AL_XXXX" else
if [State Skills.1] = "AL" and [Environment] = "GROUP C" then "OPTION3_AL_XXXX" else
if [State Skills.1] = "AL" and [Environment] = "GROUP D" then "OPTION3_AL_XXXX" else
if [State Skills.1] = "AK" and [Environment] = "GROUP A" then "OPTION1_AK_XXXX" else
if [State Skills.1] = "AK" and [Environment] = "GROUP B" then "AUTO_CAT_AK_AL_XXXX" else
if [State Skills.1] = "AK" and [Environment] = "GROUP C" then "OPTION3_AK_XXXX" else
if [State Skills.1] = "AK" and [Environment] = "GROUP D" then "OPTION3_AK_XXXX" else
if [State Skills.1] = "AZ" and [Environment] = "GROUP A" then "OPTION1_AZ_XXXX" else
if [State Skills.1] = "AZ" and [Environment] = "GROUP B" then "AUTO_CAT_AZ_AL_XXXX" else
if [State Skills.1] = "AZ" and [Environment] = "GROUP C" then "OPTION3_AZ_XXXX" else
if [State Skills.1] = "AZ" and [Environment] = "GROUP D" then "OPTION3_AZ_XXXX" else
if [State Skills.1] = "AR" and [Environment] = "GROUP A" then "OPTION1_AR_XXXX" else
if [State Skills.1] = "AR" and [Environment] = "GROUP B" then "AUTO_CAT_AR_AL_XXXX" else
if [State Skills.1] = "AR" and [Environment] = "GROUP C" then "OPTION3_AR_XXXX" else
if [State Skills.1] = "AR" and [Environment] = "GROUP D" then "OPTION3_AR_XXXX" else
if [State Skills.1] = "CA" and [Environment] = "GROUP A" then "OPTION1_CA_XXXX" else
if [State Skills.1] = "CA" and [Environment] = "GROUP B" then "AUTO_CAT_CA_AL_XXXX" else
if [State Skills.1] = "CA" and [Environment] = "GROUP C" then "OPTION3_CA_XXXX" else
if [State Skills.1] = "CA" and [Environment] = "GROUP D" then "OPTION3_CA_XXXX" else
if [State Skills.1] = "CT" and [Environment] = "GROUP A" then "OPTION1_CT_XXXX" else
if [State Skills.1] = "CT" and [Environment] = "GROUP B" then "AUTO_CAT_CT_AL_XXXX" else
if [State Skills.1] = "CT" and [Environment] = "GROUP C" then "OPTION3_CT_XXXX" else
if [State Skills.1] = "CT" and [Environment] = "GROUP D" then "OPTION3_CT_XXXX" else
if [State Skills.1] = "DE" and [Environment] = "GROUP A" then "OPTION1_DE_XXXX" else
if [State Skills.1] = "DE" and [Environment] = "GROUP B" then "AUTO_CAT_DE_AL_XXXX" else
if [State Skills.1] = "DE" and [Environment] = "GROUP C" then "OPTION3_DE_XXXX" else
if [State Skills.1] = "DE" and [Environment] = "GROUP D" then "OPTION3_DE_XXXX" else
if [State Skills.1] = "FL" and [Environment] = "GROUP A" then "OPTION1_FL_XXXX" else
if [State Skills.1] = "FL" and [Environment] = "GROUP B" then "AUTO_CAT_FL_AL_XXXX" else
if [State Skills.1] = "FL" and [Environment] = "GROUP C" then "OPTION3_FL_XXXX" else
if [State Skills.1] = "FL" and [Environment] = "GROUP D" then "OPTION3_FL_XXXX" else
if [State Skills.1] = "GA" and [Environment] = "GROUP A" then "OPTION1_GA_XXXX" else
if [State Skills.1] = "GA" and [Environment] = "GROUP B" then "AUTO_CAT_GA_AL_XXXX" else
if [State Skills.1] = "GA" and [Environment] = "GROUP C" then "OPTION3_GA_XXXX" else
if [State Skills.1] = "GA" and [Environment] = "GROUP D" then "OPTION3_GA_XXXX" else
if [State Skills.1] = "HI" and [Environment] = "GROUP A" then "OPTION1_HI_XXXX" else
if [State Skills.1] = "HI" and [Environment] = "GROUP B" then "AUTO_CAT_HI_AL_XXXX" else
if [State Skills.1] = "HI" and [Environment] = "GROUP C" then "OPTION3_HI_XXXX" else
if [State Skills.1] = "HI" and [Environment] = "GROUP D" then "OPTION3_HI_XXXX" else
if [State Skills.1] = "ID" and [Environment] = "GROUP A" then "OPTION1_ID_XXXX" else
if [State Skills.1] = "ID" and [Environment] = "GROUP B" then "AUTO_CAT_ID_AL_XXXX" else
if [State Skills.1] = "ID" and [Environment] = "GROUP C" then "OPTION3_ID_XXXX" else
if [State Skills.1] = "ID" and [Environment] = "GROUP D" then "OPTION3_ID_XXXX" else
if [State Skills.1] = "IN" and [Environment] = "GROUP A" then "OPTION1_IN_XXXX" else
if [State Skills.1] = "IN" and [Environment] = "GROUP B" then "AUTO_CAT_IN_AL_XXXX" else
if [State Skills.1] = "IN" and [Environment] = "GROUP C" then "OPTION3_IN_XXXX" else
if [State Skills.1] = "IN" and [Environment] = "GROUP D" then "OPTION3_IN_XXXX" else
if [State Skills.1] = "KY" and [Environment] = "GROUP A" then "OPTION1_KY_XXXX" else
if [State Skills.1] = "KY" and [Environment] = "GROUP B" then "AUTO_CAT_KY_AL_XXXX" else
if [State Skills.1] = "KY" and [Environment] = "GROUP C" then "OPTION3_KY_XXXX" else
if [State Skills.1] = "KY" and [Environment] = "GROUP D" then "OPTION3_KY_XXXX" else
if [State Skills.1] = "LA" and [Environment] = "GROUP A" then "OPTION1_LA_XXXX" else
if [State Skills.1] = "LA" and [Environment] = "GROUP B" then "AUTO_CAT_LA_AL_XXXX" else
if [State Skills.1] = "LA" and [Environment] = "GROUP C" then "OPTION3_LA_XXXX" else
if [State Skills.1] = "LA" and [Environment] = "GROUP D" then "OPTION3_LA_XXXX" else
if [State Skills.1] = "ME" and [Environment] = "GROUP A" then "OPTION1_ME_XXXX" else
if [State Skills.1] = "ME" and [Environment] = "GROUP B" then "AUTO_CAT_ME_AL_XXXX" else
if [State Skills.1] = "ME" and [Environment] = "GROUP C" then "OPTION3_ME_XXXX" else
if [State Skills.1] = "ME" and [Environment] = "GROUP D" then "OPTION3_ME_XXXX" else
if [State Skills.1] = "MI" and [Environment] = "GROUP A" then "OPTION1_MI_XXXX" else
if [State Skills.1] = "MI" and [Environment] = "GROUP B" then "AUTO_CAT_MI_AL_XXXX" else
if [State Skills.1] = "MI" and [Environment] = "GROUP C" then "OPTION3_MI_XXXX" else
if [State Skills.1] = "MI" and [Environment] = "GROUP D" then "OPTION3_MI_XXXX" else
if [State Skills.1] = "MN" and [Environment] = "GROUP A" then "OPTION1_MN_XXXX" else
if [State Skills.1] = "MN" and [Environment] = "GROUP B" then "AUTO_CAT_MN_AL_XXXX" else
if [State Skills.1] = "MN" and [Environment] = "GROUP C" then "OPTION3_MN_XXXX" else
if [State Skills.1] = "MN" and [Environment] = "GROUP D" then "OPTION3_MN_XXXX" else
if [State Skills.1] = "MS" and [Environment] = "GROUP A" then "OPTION1_MS_XXXX" else
if [State Skills.1] = "MS" and [Environment] = "GROUP B" then "AUTO_CAT_MS_AL_XXXX" else
if [State Skills.1] = "MS" and [Environment] = "GROUP C" then "OPTION3_MS_XXXX" else
if [State Skills.1] = "MS" and [Environment] = "GROUP D" then "OPTION3_MS_XXXX" else
if [State Skills.1] = "MT" and [Environment] = "AWC VirtuMT - Express" then "OPTION1_MT_XXXX" else
if [State Skills.1] = "MT" and [Environment] = "AWC VirtuMT - TotMT Loss" then "OPTION2_MT_XXXX" else
if [State Skills.1] = "MT" and [Environment] = "AWC VirtuMT - Complex" then "OPTION3_MT_XXXX" else
if [State Skills.1] = "MT" and [Environment] = "GROUP D" then "OPTION3_MT_XXXX" else
if [State Skills.1] = "NV" and [Environment] = "AWC VirtuNV - Express" then "OPTION1_NV_XXXX" else
if [State Skills.1] = "NV" and [Environment] = "AWC VirtuNV - TotNV Loss" then "OPTION2_NV_XXXX" else
if [State Skills.1] = "NV" and [Environment] = "AWC VirtuNV - Complex" then "OPTION3_NV_XXXX" else
if [State Skills.1] = "NV" and [Environment] = "GROUP D" then "OPTION3_NV_XXXX" else
if [State Skills.1] = "NH" and [Environment] = "AWC VirtuNH - Express" then "OPTION1_NH_XXXX" else
if [State Skills.1] = "NH" and [Environment] = "AWC VirtuNH - TotNH Loss" then "OPTION2_NH_XXXX" else
if [State Skills.1] = "NH" and [Environment] = "AWC VirtuNH - Complex" then "OPTION3_NH_XXXX" else
if [State Skills.1] = "NH" and [Environment] = "GROUP D" then "OPTION3_NH_XXXX" else
if [State Skills.1] = "NY" and [Environment] = "AWC VirtuNY - Express" then "OPTION1_NY_XXXX" else
if [State Skills.1] = "NY" and [Environment] = "AWC VirtuNY - TotNY Loss" then "OPTION2_NY_XXXX" else
if [State Skills.1] = "NY" and [Environment] = "AWC VirtuNY - Complex" then "OPTION3_NY_XXXX" else
if [State Skills.1] = "NY" and [Environment] = "GROUP D" then "OPTION3_NY_XXXX" else
if [State Skills.1] = "NC" and [Environment] = "AWC VirtuNC - Express" then "OPTION1_NC_XXXX" else
if [State Skills.1] = "NC" and [Environment] = "AWC VirtuNC - TotNC Loss" then "OPTION2_NC_XXXX" else
if [State Skills.1] = "NC" and [Environment] = "AWC VirtuNC - Complex" then "OPTION3_NC_XXXX" else
if [State Skills.1] = "NC" and [Environment] = "GROUP D" then "OPTION3_NC_XXXX" else
if [State Skills.1] = "OK" and [Environment] = "AWC VirtuOK - Express" then "OPTION1_OK_XXXX" else
if [State Skills.1] = "OK" and [Environment] = "AWC VirtuOK - TotOK Loss" then "OPTION2_OK_XXXX" else
if [State Skills.1] = "OK" and [Environment] = "AWC VirtuOK - Complex" then "OPTION3_OK_XXXX" else
if [State Skills.1] = "OK" and [Environment] = "GROUP D" then "OPTION3_OK_XXXX" else
if [State Skills.1] = "OR" and [Environment] = "AWC VirtuOR - Express" then "OPTION1_OR_XXXX" else
if [State Skills.1] = "OR" and [Environment] = "AWC VirtuOR - TotOR Loss" then "OPTION2_OR_XXXX" else
if [State Skills.1] = "OR" and [Environment] = "AWC VirtuOR - Complex" then "OPTION3_OR_XXXX" else
if [State Skills.1] = "OR" and [Environment] = "GROUP D" then "OPTION3_OR_XXXX" else
if [State Skills.1] = "RI" and [Environment] = "AWC VirtuRI - Express" then "OPTION1_RI_XXXX" else
if [State Skills.1] = "RI" and [Environment] = "AWC VirtuRI - TotRI Loss" then "OPTION2_RI_XXXX" else
if [State Skills.1] = "RI" and [Environment] = "AWC VirtuRI - Complex" then "OPTION3_RI_XXXX" else
if [State Skills.1] = "RI" and [Environment] = "GROUP D" then "OPTION3_RI_XXXX" else
if [State Skills.1] = "SC" and [Environment] = "AWC VirtuSC - Express" then "OPTION1_SC_XXXX" else
if [State Skills.1] = "SC" and [Environment] = "AWC VirtuSC - TotSC Loss" then "OPTION2_SC_XXXX" else
if [State Skills.1] = "SC" and [Environment] = "AWC VirtuSC - Complex" then "OPTION3_SC_XXXX" else
if [State Skills.1] = "SC" and [Environment] = "GROUP D" then "OPTION3_SC_XXXX" else
if [State Skills.1] = "TX" and [Environment] = "AWC VirtuTX - Express" then "OPTION1_TX_XXXX" else
if [State Skills.1] = "TX" and [Environment] = "AWC VirtuTX - TotTX Loss" then "OPTION2_TX_XXXX" else
if [State Skills.1] = "TX" and [Environment] = "AWC VirtuTX - Complex" then "OPTION3_TX_XXXX" else
if [State Skills.1] = "TX" and [Environment] = "GROUP D" then "OPTION3_TX_XXXX" else
if [State Skills.1] = "UL" and [Environment] = "AWC VirtuUL - Express" then "OPTION1_UL_XXXX" else
if [State Skills.1] = "UL" and [Environment] = "AWC VirtuUL - TotUL Loss" then "OPTION2_UL_XXXX" else
if [State Skills.1] = "UL" and [Environment] = "AWC VirtuUL - Complex" then "OPTION3_UL_XXXX" else
if [State Skills.1] = "UL" and [Environment] = "GROUP D" then "OPTION3_UL_XXXX" else
if [State Skills.1] = "UT" and [Environment] = "AWC VirtuUT - Express" then "OPTION1_UT_XXXX" else
if [State Skills.1] = "UT" and [Environment] = "AWC VirtuUT - TotUT Loss" then "OPTION2_UT_XXXX" else
if [State Skills.1] = "UT" and [Environment] = "AWC VirtuUT - Complex" then "OPTION3_UT_XXXX" else
if [State Skills.1] = "UT" and [Environment] = "GROUP D" then "OPTION3_UT_XXXX" else
if [State Skills.1] = "VT" and [Environment] = "AWC VirtuVT - Express" then "OPTION1_VT_XXXX" else
if [State Skills.1] = "VT" and [Environment] = "AWC VirtuVT - TotVT Loss" then "OPTION2_VT_XXXX" else
if [State Skills.1] = "VT" and [Environment] = "AWC VirtuVT - Complex" then "OPTION3_VT_XXXX" else
if [State Skills.1] = "VT" and [Environment] = "GROUP D" then "OPTION3_VT_XXXX" else
if [State Skills.1] = "WA" and [Environment] = "AWC VirtuWA - Express" then "OPTION1_WA_XXXX" else
if [State Skills.1] = "WA" and [Environment] = "AWC VirtuWA - TotWA Loss" then "OPTION2_WA_XXXX" else
if [State Skills.1] = "WA" and [Environment] = "AWC VirtuWA - Complex" then "OPTION3_WA_XXXX" else
if [State Skills.1] = "WA" and [Environment] = "GROUP D" then "OPTION3_WA_XXXX" else
if [State Skills.1] = "WY" and [Environment] = "AWC VirtuWY - Express" then "OPTION1_WY_XXXX" else
if [State Skills.1] = "WY" and [Environment] = "AWC VirtuWY - TotWY Loss" then "OPTION2_WY_XXXX" else
if [State Skills.1] = "WY" and [Environment] = "AWC VirtuWY - Complex" then "OPTION3_WY_XXXX" else
if [State Skills.1] = "WY" and [Environment] = "GROUP D" then "OPTION3_WY_XXXX"
else null),
Thanks in advance and look forward to any knowledge shares
I'd recommend loading all this into its own table like the following and then merging that with your query rather than defining all this in a custom column.
| State | Group | SG01 |
|---|---|---|
| AL | GROUP A | OPTION1_AL_XXXX |
| AL | GROUP B | OPTION2_AL_XXXX |
| AL | GROUP C | OPTION3_AL_XXXX |
| AL | GROUP D | OPTION3_AL_XXXX |
| AK | GROUP A | OPTION1_AK_XXXX |
| AK | GROUP B | AUTO_CAT_AK_AL_XXXX |
| AK | GROUP C | OPTION3_AK_XXXX |
| AK | GROUP D | OPTION3_AK_XXXX |
| AZ | GROUP A | OPTION1_AZ_XXXX |
| AZ | GROUP B | AUTO_CAT_AZ_AL_XXXX |
| AZ | GROUP C | OPTION3_AZ_XXXX |
| AZ | GROUP D | OPTION3_AZ_XXXX |
| ... | ... | ... |