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:

‍

Prompt:

“Can I get insights on this dataset?”

‍

Output:

‍

‍

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:

‍

Prompt:

“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?”

‍

Output:

‍

‍

‍

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:

‍

Prompt:

“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.

‍

Output:

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:

‍

Prompt:

“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”

‍

Output:

‍

‍

‍

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:

‍

‍

‍

‍

‍