Unpivot - Copilot vs PowerQuery

Last week I posted about how you can use PowerQuery to unpivot your data and in turn use simpler formulas. See the article here.

Whilst I was creating the video I thought it would be interesting to see how well Copilot in Excel could handle this data transformation without using the PowerQuery interface.

Let's take a look at the walkthrough

Can Copilot unpivot the data?

Well, yes. It did a great job of unpivoting the data just as requested.

As you will see in the video I also asked it to create a reconciliation at total, account and date level. It also performed this well.

My only minor concern was the formulas that it created to do the reconciliation, as these were overly complex.

Which is better?

Team Copilot

Copilot got the job done, exactly as requested in around a minute.

For Excel users who have not yet learnt PowerQuery, this is an extremely accessible way to transform your data without the need for formulas or code.

The prompt was created by Copilot itself and could easily be saved down and reused.

Team PowerQuery

Unpivot in PowerQuery is the epitome of a deterministic process. It will always create the same output given the same input. It will always run the exact process laid out in the M code.

I have never once worried about the accuracy of output of an unpivot from PowerQuery. 

PowerQuery, and in particular the unpivot function can be learnt in about 15 mins. Learn it once, use it forever.

To create the prompt I needed to know what I wanted to do. In this case unpivot my data. This helped me to ensure that the prompt gave Copilot the instructions and context it needed. Would an inexperienced user know this?

It was much quicker for me to create the unpivot in PowerQuery than work through the process of creating the prompt with Copilot and testing and refining it over a few iterations.

Once built, the PowerQuery unpivot runs in under a second. Copilot will take around a minute each time, and the result could look different each time, and may not even be accurate!

As always I have concerns of the cost of using AI, both financially and the impact on the environment.

Closing thoughts

Unsurprisingly this is definitely a win for Team PowerQuery for all the reasons listed above.

I do however see where this could be helpful for an inexperienced Excel user. But my advice would be to invest just a little time in learning PowerQuery. You will never look back!