Forum Discussion
Counting Unique Values Across Multiple Columns
OliviaBurglund As a variant, perhaps you want to consider PowerQuery. Connect to the table with activities, flatten it with "unpivot" and group (count) by activity. Just a few clicks. No complicated formulae needed. See attached.
- Could you provide step by step instructions for this?
OliviaBurglund If you are totally new to PowerQuery, the link below could be a good start. Chapter 13 deals with unpivoting data. In Chapter 19, you'll learn about grouping and summarising.
It's a bit of a learning curve, but it's worth it. I promise.
Thanks for the link to "Off the Grid" material. It looks well presented with good material. I am reasonably familiar with the PQ user interface and can modify the M-code. Despite that, I have the feeling that I am merely paddling in the shallows. There seem to be whole raft of list and table methods available through the use of the Advanced Editor that I have only the sketchiest idea of how they may be exploited.
Unpivot other columns is tailor-made for the job! The grouping also works effectively.
I will have to think further on the questions of 'Does a complicated formula cease to be complicated merely because one can select it at the click of a button?' I can see arguments both ways!
I actually have Charles Williams's FastExcel add-in, so I could have gone for
= LET( fullList, INDEX(UNPIVOT(data,,1,,,0),,3), COUNTIFS(ActivityGrid, SORT(UNIQUE(fullList))))Despite that, I think I have yet to find the definitive dynamic array solution for unpivotting that does not rely upon add-ins. Playing with indices works but is somewhat inelegant.
