Conditional Categorization in Power Query

[Darko_Giac]

Hi All,

I'm currently working with a fairly large data set in which I am looking to categorize respondents into certain classes based on their responses.

I have 6 categories that I would like to place respondents in:

1). ADComb

2). ADIna

3). An

4). Dep

5). SA

6). Other

A respondent can choose either any one or combination of the above. If they choose either ADComb or ADIna and any other option (so respondent 1 chose ADComb, An and Dep, they would fall into ADComb. In other words, ADComb and ADInatrump the other response options, and they would only be counted once to whichever of those two they chose).

However, if a respondent chooses a combination of An, Dep, SA and Other (so respondent 2 chooses An, Dep and SA, the count would be in all three for that one individual - if they only chose SA and Dep, they would be counted twice - once to each category)

If "Other" is chosen along with any of the three (An, Dep, SA - they are only counted in each one except for "Other")

If respondent 3 chooses "ADIna, An, Dep and SA, they would only get counted to ADIna, and not the other categories).

I've attached a workbook to hopefully clear up any confusion, as I know there are layered conditions built into the logic.

I would be looking to accomplish this in either Power Query or Power Pivot

Any help is greatly appreciated!

TestWorkbook

Darko

[v-chuncz-msft · Accepted solution]

@Darko_Giac,

Just try to add custom column.

• https://docs.microsoft.com/en-us/powerquery-m/expressions-values-and-let-expression#If
• Operators

[Darko_Giac]

Thanks @v-chuncz-msft, worked like a charm!