Power Query is an incredible tool. It sits within Microsoft Excel and is often at the heart of data-based Excel models. It is a ETL tool, which means it will Extract, Transform and Load your data directly into an Excel Table within your model.
Power Query is based on M-code language, but most users can perform all of the transformations that they need using the very intuitive user interface, simply by click on the buttons.
Adhering to good practice principles such as using Groups to make the data flow through the queries easier to follow and naming query steps for clarity helps users to better understand the queries. The query dependency viewer helps them to understand how the queries are connected and how the data is flowing through them.
The only challenge is that Power Query can be daunting to the untrained, and a more complicated model can sometimes take some working through. It risks feeling like a "black box" to some users.
The good news is that we can use Copilot to help us to document the model for us, and even use it like a "chat bot" to chat through how the model works.
This turns a potential black box into a tool you can easily navigate through conversation.
Through a straightforward prompt we can ask Copilot to analyse the M-code and create a comprehensive document explaining all the key points and creating diagrams to illustrate the data flows. You can eve ask it to highlight areas for improvement.
And the great thing is that we are not passing any data through to copilot, just the code.
Taking this one step further we can ask Copilot to explain specific elements of the logic or specific queries.
This moves us from a static document that we need to read to an interactive tool that we can have a conversation with.
This is unbelievable useful!
I was really impressed with how Copilot performed on this task. I will certainly be using this going forward.
This method requires the manual step of copying the M-code out of Power Query and performing the AI work in Copilot. It feels like a fairly natural progression for Microsoft to introduce Copilot within the Power Query interface.
We teach Power Query as part of our core Full Stack program.
Our award winning training will guide you through every step of how to use Power Query.
Find out more about our subscription options here