Row split - Copilot vs PowerQuery
Last week I posted about how you can use Power Query to split your data by row when multiple values are being stored in a single cell. 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 Power Query interface.
Let's take a look at the walkthrough
Can Copilot in Excel split the data by row?
As in my previous Copilot vs Power Query videos, Copilot completed the task and did a good job too.
Unlike with the Unpivot example, as there are no values, I wasn't able to perform a reconciliation and therefore am left a little worried about whether it split it properly and didn't make anything up.
Which is better?
Team Copilot in Excel
Copilot got the job done, exactly as requested, and was pretty quick.
For Excel users who have not yet learnt Power Query, 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 Power Query
Split by row in Power Query is 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 split by row using Power Query.
The core Power Query skills can be learnt pretty quickly. Learn it once, use it forever.
To create the prompt I needed to know what I wanted to do. In this case split the cells in my data by row. This helped me to ensure that the prompt gave Copilot the instructions and context it needed.
It was much quicker for me to create in Power Query than work through the process of creating the prompt with Copilot and testing it a couple of times.
Once built, the Power Query 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 Power Query for all the reasons listed above.
As with my previous example, I do however see where this could be helpful for an inexperienced Excel user.
But, as before, my advice would be to invest just a little time in learning Power Query. It's a skill that will transform the way you work and repay the time invested 100 fold.
