Yesterday Alan Murray posted a tutorial here, and asked the question ‘how would you do it’.
I didn’t use PowerQuery. I would have, if it gave me an advantage over the (robust) techniques Microsoft have already given us.
Breaking the problem down, I see two steps.
- Normalise the source data
- Run a Crosstab and Summary to get the required report
To ‘normalise’ means to put the data into a list such as this.
From that we can do anything. This question requires a ‘crosstab’ type of report. And a summary.
So, Alan’s question breaks down to
- How would you normalise data with Excel?
- How would you do a crosstab and a summary in Excel?
I’ll cover this in an explainer shortly. It’s simple when you reduce the problem to its constituent parts.
TO BE CONTINUED ….