Knowledge Share: Use ChatGPT to Build Power Query Faster and Easier

Another knowledge share for my network! I wish to share a trick which has changed the way I work with Power Query. Use ChatGPT to edit Power Query code instead of writing the applied steps yourself. You wont believe how easy it is.

What is Power Query? Power Query is a powerful tool within Microsoft Excel and PowerBi that allows you to connect, transform, and analyze data from various sources.

Why use Power Query? Power Query can help you automate and simplify the process of getting and transforming data creating repeatable steps for your reporting and analysis.

Just like excel, you are required carry out the steps to filter, lookup and match to produce the report you want. In Power Query, this is done under applied steps. Each time you run this report it will apply the recorded steps to a new data set that you have introduced. This helps save time with repeatable analysis.

Why use AI? Like analyzing data in excel with formulas, filters, and pivot tables, you still need to apply the steps which in power query will run each time for repeatable analysis. Whilst you only need to do this once, sometimes, depending on what you want to achieve these applied steps can be complex. So, is there an easier way?

Use AI to edit the M code! No writing code yourself needed. - So simple. If you can copy and paste, you can do this.

AI can help you save time and be efficient. Instead taking time to figure out the applied steps and apply them, just tell AI what you want to achieve and let AI efficiently write the code for you. This means you don’t have to waste time figuring out the steps, or remembering the syntax and functions. You can just focus on the data analysis and reporting that you need to do.

How? Launch Power Query editor, and navigate to Home > Advanced Editor.

In advanced editor, copy the code and paste it into ChatGPT then tell ChatGPT what you want to achieve. The code will be returned to you modified which you can paste over the old code, then click done! Simple as that!

What if it didn't work? Then tell ChatGPT. Tell it what error code you received, Or, if you didn't manage to achieve what you was looking for, have a natural language conversation and tell ChatGPT what happened. The code will be modified again until it gives you what you are looking for.

Why ChatGPT and not Bing, Bard or a different AI? I only typically use one of those 3, but chatGPT if by far the best for getting code. This is because the format it sends it back to you has a simple "copy button" which allows you to paste the code easier than what is returned with Bing or Bard.

I managed to build a new report that can detect potential duplicate bookings by asking ChatGPT to write a code that will consider a matched "booking type", "traveler name", "destination" to match and then consider a date window. The Report sends back matched bookings that could possibly be duplicates, with two new columns. The first column identifies if there is a potential duplicate booking (and deletes the no line entries) and the second column gives the potential duplicate booking unique ID, these are then presented in the report as a match, for review.