NoCode and AI
Excel and VBA
Power Query

ChatGPT for Oil and Gas - Part 4: Data Analytics and PowerQuery

Alan Mourgues
March 7, 2024

Let’s continue with the ChatGPT series. So far, in the previous installments of the series, we’ve explored the language model, including text generation, proofreading, translation, and summarization. Then, we moved on to exploring ChatGPT's advanced coding capabilities, from creating Excel VBA snippets to writing and troubleshooting Python scripts.

Now, let’s turn our attention to another exciting aspect of ChatGPT: its data analytics capabilities. With its advanced data analytics plugin, ChatGPT can process and analyze large datasets, offering insights far beyond what traditional Excel data analytics can provide. This powerful feature enables users to gain deeper and more nuanced understandings from their data.

Whether you're dealing with complex statistical information or large quantities of unstructured data, ChatGPT can swiftly sort, analyze, and interpret it, transforming raw data into valuable insights. This makes it an invaluable tool for anyone looking to make data-driven decisions or seeking to uncover hidden patterns and trends within their datasets.

Let’s consider this example: I want to test whether ChatGPT can identify poro-perm relationships on a per facies (or rock type) basis. To do this, I will create a synthetic dataset myself. The way I do this is, surprise-surprise, using ChatGPT, where I create four different trends for four different facies. Then, I add some noise to these trends to make the dataset less clean. Now, I will open a new chat to forget where the data came from, and I'm just going to throw the full dataset, reordered randomly, at ChatGPT and ask for insights. This is what the dataset looks like:

So, I’ll take a screenshot of this table, paste it into the ChatGPT interface, and ask:


Can I get insights on this dataset?


So, having passed an image as a prompt, ChatGPT managed to read the data and identify the four trends corresponding to each facies type. Furthermore, we can ask:


“Can you find a distinct poro-perm relation per facies type and produce a K vs poro graph with each facies in a different color and Y-axis in log scale?”


Arguably, this dataset is too clean and not very challenging, but remember, we're just pasting an image without doing any data preparation beforehand. So, the fact that ChatGPT matched my design starting point is pretty impressive.

You can experiment with more complex datasets and see how you can leverage this data analytics capability in your favor. Also, bear in mind that this is very much the first iteration of this capability, so just imagine where this will be in the not-too-distant future.

Let's now move on to one of my personal favorite tools, Power Query.

Power Query is a remarkably user-friendly tool in Excel that simplifies gathering and shaping data from different sources. Despite its efficiency in organizing and preparing data for analysis, it's surprising how few people are aware of this powerful feature. Ideal for users of all skill levels, Power Query is a must-have for effortlessly managing data tasks.

Now, the purpose of this newsletter issue isn't to delve into the details of Power Query, but if you're interested in learning more—and I strongly suggest that you should—I have prepared a short seven-part video tutorial. In it, I show you how to create a Data Model from an existing Excel dataset and then how to pull data from the data model to build pivot charts and tables. The result is a graph and a table that can be easily updated interactively by simply clicking on the slicer buttons. Here, you can watch a brief demo introducing the tutorial and access the working file:

If you're interested, you can access the tutorial, for free, here.

What I do want to show now, though, is that you can get step-by-step instructions on how to update a data model without affecting your source dataset.

Say you have a dataset with a gas rate column expressed in MMscfd units. I want to add a column that multiplies the MMscfd column by 1000, so I end up with the rate now expressed in Mscfd. The key here is that I will not add anything to the Excel source data. Everything is done inside Power Query. That’s the beauty of it.

So, what I can do is ask ChatGPT how to edit the Power Query code to perform that extra task:


“Add a step to this code to add a new column which multiplies the gas rate”

And I will paste the entire block of code extracted from Power Query.


What ChatGPT does is add a snippet to the code with the extra step. You can then paste this code back into Power Query, and that’s it. The additional column has been added.

You can understand this better by seeing how it's done in this LinkedIn clip I posted, here.

Whether it's Excel, VBA, Power Query, Python, HTML, JavaScript, or any other programming language, the bottom line is that you can leverage ChatGPT to get step-by-step instructions, generate and troubleshoot code, perform data analytics, and more. Do invest time in exploring the full range of possibilities with this tool. It’s worthwhile, I promise!

One final use case before signing off: automating report generation. Let’s say you have a Word template and you need to fill it with data stored in an Excel table (or any other database, for that matter). For this simplistic example, let’s use a table with core descriptions from 4 wells.

I want to automate the generation of a Word document with the above information. So, I’ll grab a screenshot of this table, paste it into ChatGPT, and ask:


Create an excel macro to generate a word document with 4 sections, one per row in the table. You can see from the image the row numbers and the columns the data is in. Row 3 has the headers, which you should use to write appropriate labels in the report. Add a couple of blank lines between sections in the report


All you need to do is simply copy the code and follow the instructions provided by ChatGPT to run the Excel macro.

Watch this clip to see how it looks: here.

That's it, folks. I hope you've enjoyed this 4-part ChatGPT series and now feel equipped and energized to continue exploring on your own. I remind you, everything I've shared through the series was demonstrated on-screen during a webinar I gave on November 16, 2023. You can watch it here:

More Tools

Explore a curated collection of valuable resources in our Store, all designed to help you upskill. Currently, you can take advantage of three exclusive, limited-time offers, each crafted to aid in funding our initial marketing efforts.

Alan is a Consulting Petroleum Reservoir Engineer with 20+ years of international industry experience. Alan is the founder of CrowdField, a marketplace that connects Oil & Gas and Energy businesses with a global network of niche talent for task-based freelance solutions. His mission is to help skilled individuals monetize their knowledge as the Energy transition unfolds, by bringing their expertise to the open market and creating digital products to sell in CrowdField's Digital Store.


All blog posts

"O&G AI Wave"

Subscribe to O&G AI Wave – your indispensable guide to the evolving world of AI and NoCode and their practical applications for Oil & Gas professionals.

Our newsletter brings you the latest trends and insights in AI and NoCode technologies directly impacting the O&G sector.

Every issue is packed with expert analyses and practical tips to help you navigate and leverage these transformative technologies.

Don’t miss out on the opportunity to stay ahead of the trend and future-proof your career in this incredibly dynamic field.

How we can help

At CrowdField, our mission is to empower YOU—helping you showcase your skills in the open market and monetize them effectively. Here's how you benefit from being with us:

👉 Make your mark and find freelance opportunities by listing yourself in our freelancer directory to get noticed. Join us for free here.

👉 Turn your skills into digital products that sell. We'll help you polish, launch, and list them in our Store, promote them on our LinkedIn page, to our email subscribers, and feature your success in a case study on our Blog, amplifying both your product and personal brand. Whether you're at the idea stage, midway through implementation, or nearing completion, if you see potential for monetization, we're interested. Take the first step by getting in touch to start a conversation at

👉 Discover bargains in our digital store with heavily discounted prices during our market discovery period. Take advantage of these limited-time offers as we expand our network. Dive in now and find your gem! Got to Store