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

Hiran de Silva

View all posts

Add comment

Your email address will not be published.