My solution to Alan Murray’s PowerQuery question

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 ….