ChatGPT for Data Analysts
Prompts

This resource is designed to provide data analysts with a practical roadmap for leveraging ChatGPT effectively in their analytical workflows. Each chapter of this guide is dedicated to a specific aspect of prompt engineering and AI interaction, offering practical examples, and interactive prompts tailored to enhance your data analysis capabilities. Whether you’re new to ChatGPT or looking to deepen your understanding, this guide is your go-to resource. Below, you’ll find an overview of each chapter, a brief overview of the chat’s focus, the ChatGPT model used, sample prompts, and a link to an interactive chat experience for hands-on practice.

Unit 4: Learning Prompt Engineering

Understanding prompt engineering is essential for proficiency in ChatGPT within the domain of data analysis. Effective communication with AI models like ChatGPT is critical for extracting valuable insights and conducting thorough analyses. In this chapter, we will explore the significance of prompt engineering and its practical applications in data analysis. We will investigate various prompt patterns and demonstrate how they can be utilized to fully leverage the capabilities of ChatGPT for data-driven decision-making. Through clear examples and interactive exercises, you will learn how to craft prompts that elicit precise responses, enabling you to harness ChatGPT as a valuable asset in your analytical toolkit.

Persona Pattern:

First Example:

In the first prompt, we ask ChatGPT to pretend it’s a data collector and talk about the problems faced when there are missing pieces of information in data analysis. Then, in the second prompt, we want ChatGPT to imagine it’s a data analyst, discussing similar problems but from a different point of view. Finally, in the third prompt, ChatGPT is asked to think like a 10-year-old kid, explaining the difficulties of dealing with missing data in a simpler way. Each response from ChatGPT is tailored to the specific role it’s asked to take on, offering unique insights based on that perspective.
Model used: ChatGPT-3.5
First Prompt: Act as a Credit Analyst, explain the significance of the debt-to-income ratio in assessing an individual’s creditworthiness. Discuss how this ratio is calculated and how it impacts the ability of an individual to secure a loan. Illustrate your point with a simple example, showing the calculations and the implications of a high debt-to-income ratio.

Second Example:

In this prompt, we task ChatGPT with adopting the role of a credit analyst, highlighting the importance of the debt-to-income ratio in evaluating an individual’s creditworthiness. ChatGPT will explain how this ratio is calculated and its impact on an individual’s ability to obtain a loan. Additionally, ChatGPT will provide a simple example to illustrate the calculations and implications of a high debt-to-income ratio.
Model used: ChatGPT-3.5
First Prompt: Act as a data collection engineer and discuss the challenges of handling missing data in data analysis
Second Prompt: Act as a data analyst and discuss the challenges of handling missing data in data analysis
Third Prompt: Act as a 10-years old child and discuss the challenges of handling missing data in data analysis

Question Refinement:

In the first prompt, we ask about handling skewed data. Then, in the second prompt, we explore effective techniques for addressing skewness in datasets. Transitioning to the third prompt, we introduce a question refinement pattern, where ChatGPT suggests better versions of questions. For example, instead of “How do I model this data?”, ChatGPT could refine the question to specify the desired modeling technique or provide more context about the dataset and its objectives. This encourages clearer communication and more meaningful responses.
Model used: ChatGPT-3.5

First Prompt: How do I deal with skewed data?

Second Prompt: What are the effective techniques and transformations to address skewness in the dataset and ensure that the data distribution is suitable for analytical modeling?

Third Prompt: From now on, whenever I ask a question, suggest a better version of the question to use instead

Fourth Prompt: How do I model this data?

Audience Persona Pattern:

In the first prompt, we ask ChatGPT to explain regression analysis in two sentences, imagining the audience as a C-Level executive at a marketing agency. Then, we shift to the persona of a Chief Executive Officer focusing on long-term business strategy, prompting ChatGPT to explain how artificial intelligence can inform decision-making. Finally, we introduce the persona of Christopher Columbus and ask ChatGPT to explain the principles of digital transformation in a relatable and engaging manner. Model used: ChatGPT-3.5

First Prompt: Explain regression analysis in two sentences, Assume that I am a C-Level executive at a marketing agency.
Second Prompt: Explain how artificial intelligence can inform decision-making to me. Assume that I am a Chief Executive Officer focusing on long-term business strategy.
Third Prompt: Explain the principles of digital transformation to me. Assume that I am Christopher Columbus.

Recipe Pattern:

In this chat, we ask ChatGPT to help us achieve a cleaned dataset, free of missing values and outliers. We’ve already laid out a plan with specific steps: importing the dataset, identifying missing values, removing or imputing them, and visualizing data for outliers. We’ve also asked ChatGPT to provide a complete sequence of steps, fill in any missing ones, and identify any unnecessary steps to ensure we’re on the right track.
Model used: ChatGPT-3.5

First Prompt: I would like to achieve: A cleaned dataset free of missing values and outliers. I know that I need to perform steps: A. Import the dataset B. Identify missing values C. Remove or impute missing values D. Visualize data for outliers.
•Provide a complete sequence of steps for me:
•Fill in any missing steps
•Identify any unnecessary steps

Flipped interaction pattern:

In this chat, we start by asking ChatGPT to assist us with customer segmentation analysis using a flipped interaction pattern. We specify our preference for multiple-choice questions, asking them one at a time until we understand our target consumers. Additionally, we provide context about our business: offering Power BI training services in Australia. As the conversation progresses, we notice a shift: ChatGPT starts asking us questions to gather the necessary information. After completing the analysis, we inquire about three ways to increase customer visibility based on the insights gained. This approach involves active user participation, with ChatGPT dynamically guiding the conversation.



Model used: ChatGPT-3.5

First Prompt: I would like you to help me conduct customer segmentation analysis. Please ask me questions one by one, until we have enough information to understand our target consumers. Ask me questions with multiple choices as answers only and ask one question at a time.

Here’s a brief description for your initial learning: We provide Power BI training services in Australia.

Second Prompt: From the analysis you have learnt, give me 3 way that can significantly increase my customer visibility.

Outline Pattern:

In this chat, we utilize the outline pattern to organize information systematically. The user requests an outline expander, prompting ChatGPT to generate a bullet point outline based on their input. Each bullet point can have up to 3-5 sub-bullets. ChatGPT then asks the user to select a bullet point for expansion, creating a new outline based on their choice. This iterative process continues until the user indicates they have completed outlining. This pattern ensures structured communication and tailored information organization to meet the user’s needs effectively.

Model used: ChatGPT-3.5

First Prompt: Act as an outline expander. Generate a bullet point outline based on the input that I give you and then ask me for which bullet point you should expand on. Each bullet can have at most 3-5 sub bullets. The bullets should be numbered using the pattern [A-Z].[i-v]. Create a new outline for the bullet point that I select. At the end, ask me for what bullet point to expand next. Ask me for what to outline.

Second Prompt: Mastering Power BI

Third Prompt: C

Gameplay for Prompt Engineering:

In this chat, we play a game to evaluate business health metrics. ChatGPT generates a dataset with metrics for 20 businesses, and both the user and ChatGPT analyze the data to determine each business’s health. The challenge is to accurately gauge the true health of each business beyond the numbers. In the second prompt, the user initiates the game by suggesting to start with the first business. This sets the stage for an interactive competition to assess each business’s health accurately.

Model used: ChatGPT-3.5

First Prompt: ChatGPT and I are evaluating business health metrics.

Rule: ChatGPT will generate a simulated dataset consisting of key metrics for 20different businesses, such as revenue, profit margins, customer satisfaction scores, and employee turnover rates. We will both analyze this data to determine the overall health of each business. The one who makes right guess about a business’s health wins.

Our goal is to identify any discrepancies between the metrics and the actual state of the business. Even if two businesses have similar metrics, it doesn’t necessarily mean they are equally healthy. We will explore various businesses and assess their overall health based on these metrics. The challenge is to accurately gauge the true health of each business beyond the numbers.

Second Prompt: Let’s start with first

Template Pattern:

In this chat, we employ the template pattern to create detailed project update emails for clients. Using the ChatGPT-4 model and data from the “AMZ_Project_Data.csv” file, we generate emails with placeholders for client-specific information such as project ID, status, progress, budget, and amount spent. The user, identified as Ali Noorani, principal trainer at AMZ Consulting Pty Ltd, emphasizes a professional and supportive tone. In the second prompt, we compile these emails into a PDF document for easy distribution to each client. This approach ensures consistency and efficiency in client communication.

Model used:  ChatGPT-4

To proceed, please open the “AMZ_Project_Data.csv” file located in the exercise files folder..

First Prompt: Using the data set and suggested place holders. Write emails to <CLIENT NAME> providing a detailed update on Project <PROJECT ID>. Include information about the current <STATUS>, <PROGRESS>% of work completed, and a brief financial overview mentioning the budget of $<BUDGET> and the amount spent so far $<AMOUNT SPENT>. Ensure to maintain a professional and supportive tone throughout the email, addressing any potential concerns the client might have due to the current project status

Second Prompt: Can you create emails for each client and give me all of them in a pdf? Add my name and position as Ali Noorani, principal trainer at AMZ Consulting Pty Ltd

Fact Test:

In this chat using the fact test pattern, ChatGPT-4 verifies facts sourced from the internet. The user presents statements about Starbucks’ expansion, global presence, and revenue figures. ChatGPT categorizes each statement as true, sounds true, sounds false, false, or can’t verify, providing reasoning for its assessment. This fact-checking process helps determine the accuracy of information and assess the reliability of online sources efficiently.

Model used:  ChatGPT-4

First Prompt: Browse the internet, Verify all facts within double quotation categorize using below labels and provide reasoning for it

1-true,

2- sounds true,

3- sounds false,

4- false

5- cant verify

Did you know that Starbucks “opens a new store every 15 hours in China”?

With over “420,000 locations” in more than “80 markets” worldwide, Starbucks is not just a coffee giant; it’s also a data giant.

Founded in “1971”, Starbucks has grown from a single Seattle coffeehouse to a global coffee empire, boasting a revenue of “$2.57 billion in 2021”.

Few Shot Prompts:

In this chat using ChatGPT-4, we educate the model on data preprocessing tasks by providing instructions to open the “Dataset_With_Errors.xlsx” file. In the first prompt, ChatGPT examines and addresses inconsistencies in date format, boolean values, gender representation, and missing data. After completing the actions, ChatGPT provides the updated dataset. In the second prompt, the user asks ChatGPT to check for similar inconsistencies, continuing the model’s education on data preprocessing.

Model used:  ChatGPT-4

To proceed, please open the “Dataset_With_Errors.xlsx” file located in the exercise files folder..

First Prompt: Can you perform the following examination on my data set, perform the action suggested and give me updated data set.
Examine: Inconsistent Date Format in the ‘DoB’ column.
Action: Standardize all dates to a consistent format, such as DD/MM/YYYY.
Examine: Inconsistent Boolean Values in the ‘Paid’ column.
Action: Normalize all values to a uniform Boolean format, such as ‘True’ or ‘False’.
Examine: Inconsistent Gender Representation in the ‘Gender’ column.
Action: Standardize gender representations to a uniform format (e.g., ‘Male’, ‘Female’).
Examine: Missing Data in the ‘Gender’ column.
Action: Fill the missing data with a placeholder such as ‘Null’ or ‘Unknown’.
Second Prompt: Can you check for similar inconsistences in data now?

Alternative Approaches:

In this chat using ChatGPT-4, we explore alternate approaches to analyze and forecast temperature data from the “Monthly_Temperature.csv” file. The user requests forecasts for the same periods next year across various cities, emphasizing the use of different methods. ChatGPT examines various techniques and compares their results, providing insights into the effectiveness of each approach.

Model used:  ChatGPT-4

To proceed, please open the “Monthly_Temperature.csv” file located in the exercise files folder..

Prompt: Can you analyze the data and forecast temperature in the same pattern for these cities same periods next year? Use alternative approaches and compare the results.

Unit 5: ChatGPT for Excel and Google Sheets

This chapter focuses on using ChatGPT within Microsoft Excel and Google Sheets. It explores practical prompts and examples for leveraging ChatGPT’s capabilities directly within these spreadsheet applications. Readers will learn how to utilize ChatGPT for tasks such as data analysis, formula generation, and collaborative work. By following the provided prompts, readers can enhance their productivity and streamline their workflows within Excel and Google Sheets.

Understanding V-LOOKUP:

In this chat using ChatGPT-3.5, we ask for explanations about the VLOOKUP function in Excel. First, we seek a general explanation. Then, we request a kid-friendly explanation. Finally, we ask for an example using a sample dataset. ChatGPT provides clear and accessible responses tailored to each request, ensuring understanding at different levels.

Model used: ChatGPT-3.5

First Prompt: can you explain VLOOKUP function in excel?

Second Prompt: can you explain it to me as if i was 10-years old

Third Prompt: can you give an example with a sample data set

Generating New Formulas:

In this chat with ChatGPT-3.5, we ask for help writing an Excel function. Specifically, we need to extract the Product from the Product type/Product column, based on a provided data sample. The task involves extracting text after the “/” symbol in each cell within the specified range (C2:C119). ChatGPT assists us in crafting an efficient solution to accurately extract the Product information from the given dataset.

To proceed, please open the “Sales_Data_1.xlsx” file located in the exercise files folder..

Model used: ChatGPT-3.5

Prompt: Please write an Excel function to extract the Product (text after the “/” symbol) from the Product type/Product column in C2:C119, based on the data sample below: Lanterns/everglow butane

Lanterns/everglow double

Lanterns/everglow kerosene

Lanterns/everglow lamp

Lanterns/everglow single

Lanterns/firefly 2

Lanterns/firefly 4

Lanterns/firefly extreme

Lanterns/firefly lite

Lanterns/firefly mapreader

Lanterns/firefly multi-light

Lanterns/flicker lantern

Sleeping Bags/hibernator

Sleeping Bags/hibernator camp cot

Sleeping Bags/hibernator extreme

Sleeping Bags/hibernator lite

Sleeping Bags/hibernator pad

Sleeping Bags/hibernator pillow

Sleeping Bags/hibernator self – inflating mat

Automating Tasks through VBA:

In this chat with ChatGPT-3.5, we request assistance in generating a VBA script for an Excel worksheet named “Sales Table.” The task involves autofitting all rows and columns, bolding the first row, highlighting every other row, and saving the sheet as a PDF to a specified location. ChatGPT helps us by providing a VBA script that fulfills these requirements efficiently.

Model used: ChatGPT-3.5

To proceed, please open the “Sales_Data_2.xlsx” file located in the exercise files folder..

Prompt: I have an Excel worksheet named “Sales Table” containing a table with the following fields: Trans Date,            RetCity, Order method type,  Urgent?,          Retailer type, Product Code,  Price, Quantity Sold.

Can you please generate a VBA script to autofit all rows and columns in the sheet, bold the first row, highlight every other row and save it to my given location (C:\Users\munee\OneDrive\Desktop\ChatGPT\Chap 5) as a PDF?

Formulating Complex Formulas:

In this chat with ChatGPT-4, we explore loan repayment calculations using the “Loan_Repayment_Dataset_1.xlsx” file. First, we seek an explanation for a specific calculation “=NPER(B2/12/100, -D2, A2)” to understand its purpose and components.  Then, we request help in writing an Excel calculation to determine the tentative ending date of a loan based on provided data. Lastly, we manually review instances where the calculated repayment date using the EDATE function may have been miscalculated. ChatGPT-4 provides insights and solutions for each prompt, aiding our understanding of loan repayment analysis.

Model used: ChatGPT-4

To proceed, please open the “Loan_Repayment_Dataset_1.xlsx” file located in the exercise files folder..

First Prompt: Can you explain this calculation to me: =NPER(B2/12/100, -D2, A2)

Second Prompt: I have the Loan Start Date in G2:G500 and the Repayment Duration (Months) in H2:H500. can you write an Excel calculation that will help me find the tentative ending date of the loan.

Third Prompt: Manually read the values from I2:500 and list down instances where EDATE(G2, H2) has miscalculated the Repayment date

Generating Sample Data Sets:

In this chat with ChatGPT-3.5, we request assistance as a recently hired Data Analyst for an HR firm. We ask ChatGPT to generate a sample dataset in CSV format with common HR fields for practice. Additionally, we ask ChatGPT to introduce common data quality assurance (QA) issues into the sample dataset and describe the specific changes made. ChatGPT helps by providing a sample dataset with relevant HR fields and simulating QA issues like missing values and inconsistent formatting. This enables us to practice and understand common challenges faced by HR Analysts.

Model used: ChatGPT-3.5

Prompt: I’m a recently hired Data Analyst for an HR firm, looking for sample data that I can use for practice. Please generate a sample dataset in CSV format containing common fields that HR Analysts typically encounter on the job. Please also modify the sample to include some common data QA issues and describe what specific changes you made.

Transitioning from Excel to Google Sheets:

In this chat with ChatGPT-4, we ask for assistance in Excel functions and their equivalents in Google Sheets. First, we request a formula for dynamic array to find unique locations from a specified range. Next, we seek an update to this formula to calculate total spending by each location. Lastly, we inquire about replicating the same functionality in Google Sheets. 

Model used: ChatGPT-4

To proceed, please open the “Customer_Sales_Data_1.xlxs” file located in the exercise files folder..

First Prompt: Write the formula for dynamic array that finds unique locations from the locations present in D2:D501

Second Prompt: Update this formula to find total spending by each location. the spending data is in F2:F501

Third Prompt: How do i get the same functionality in google sheets

Implementing Conditional Formatting

In this chat with ChatGPT-4, we ask for assistance in implementing conditional formatting in Google Sheets. The user provides a Google Sheet titled “Project Management_Tracker_1” and explains the requirement: whenever “high” is selected from a drop-down list in column E (cells E2:E11), the entire row should turn red. ChatGPT-4 guides us through the process step-by-step, helping us set up conditional formatting rules in Google Sheets to achieve the desired outcome. By the end of the chat, we successfully format the sheet to dynamically highlight rows based on the selected priority level.

Model used: ChatGPT-4

To follow along, open Project Management_Tracker_1 Google Sheet.

Prompt: I have a google sheet with a Priority column E2:E11 which is a drop down allowing low, medium or high. I want to conditionally format the sheet so that whenever high is selected, the entire row turns red not only 1 cell

Automation with Google App Scripts:

In this chat with ChatGPT-4, we strategize our approach to automating tasks within Google Sheets using Google Apps Script. We present a prompt requesting assistance in creating a script that automatically generates a timestamp in column J whenever a checkbox in column I is checked. ChatGPT guides us through the process, helping us devise a custom script to achieve the desired automation strategy. By the end of the chat, we successfully implement the script, enabling automatic timestamp insertion upon checkbox activation.

Model used: ChatGPT-4

To follow along, open Project_Management_Tracker_ 2 Google Sheet.

Prompt: can you create an app script for Automatically creating timestamp in the J column when a checkbox  in I column is checked.

Unit 6: ChatGPT for Power BI

This chapter explores the myriad ways in which ChatGPT enhances the Power BI user experience. From crafting comprehensive data dictionaries to optimizing code and solving complex coding problems in M Language and DAX, readers will discover how ChatGPT streamlines various tasks in Power BI. Through practical examples and step-by-step guides, users will learn how to leverage ChatGPT to improve productivity, optimize workflows, and drive actionable insights in Power BI.

Crafting a Comprehensive Data Dictionary:

In this chat with ChatGPT-4, we seek assistance in Power BI for creating a Data Dictionary for the “Work Log” table. We provide sample data for reference, including columns like Employee ID, Date, Hours Worked, Project, and more. ChatGPT-4 guides us through the process, helping us craft a comprehensive Data Dictionary tailored to our table structure. Upon completion of the Data Dictionary, we ask ChatGPT to present it in a tabular format for better visualization and understanding.

Model used: ChatGPT-4

To follow along, open ChatGPT_for_PowerBI_1.pbix from the exercise files folder. 

First Prompt: Can you create a Data Dictionary for “Work Log’ table. I am sharing sample data for reference

 Employee ID  Date    Hours Worked            Project Work Type      Location          Overtime Hours          Remote            Performance Rating

17188  01/01/2021 12:00:00 am         5.56     Project Beta    Administration            Tokyo  1.7       FALSE            Good

17188  02/01/2021 12:00:00 am         6.65     Project Beta    Client Support Berlin  1          FALSE            Excellent

17188  03/01/2021 12:00:00 am         6.59     Project Alpha  Research          New York       1          TRUE  Poor

17188  04/01/2021 12:00:00 am         7.37     Project Epsilon            Development   Tokyo  2          TRUE  Average

17188  05/01/2021 12:00:00 am         7.39     Project Beta    Research          Los Angeles    2.2       TRUE  Below Average

17188  06/01/2021 12:00:00 am         5.16     Project Epsilon            Administration            Beijing 2.3       FALSE            Good

17188  07/01/2021 12:00:00 am         4.44     Project Alpha  Research          Paris    0.4       TRUE  Excellent

17188  08/01/2021 12:00:00 am         7.99     Project Delta   Client Support Mumbai           0.7       FALSE            Good

17188  09/01/2021 12:00:00 am         7.85     Project Delta   Training           New York       1.7       FALSE            Below Average

17188  10/01/2021 12:00:00 am         6.45     Project Alpha  Client Support Los Angeles    0.4       TRUE  Average

Second Prompt: Can you present it in Tabular Format?

Calendar Table Creation

In this chat with ChatGPT-4, we seek assistance in Power BI for writing M code in the Query Editor to create a dynamic calendar table. We already have a “Work Log” table in our Queries, and we want the starting and ending dates for the calendar table to be dynamic, based on the Date column in the “Work Log” table. ChatGPT-4 guides us through the process, helping us write the necessary M code to generate the dynamic calendar table. Through this interaction, we gain valuable insights into leveraging ChatGPT-4 to automate and optimize data processing tasks in Power BI.

Model used: ChatGPT-4

To follow along, open ChatGPT_for_PowerBI_2.pbix from the exercise files folder or continue using the previous file. 

Prompt: Can you help me write M code for Power BI Query Editor. The code should create a dynamic calendar table. The starting and ending dates should be taken dynamically from Date in Work Log table. I have this table already in my Queries.

Incorporating Fiscal Years

In this chat with ChatGPT-4, we’re exploring how to enhance a calendar table code in M within Power BI. The user presents an existing M code for a calendar table and requests assistance in adding a fiscal year column to it. The fiscal year should start from July. ChatGPT-4 provides guidance on modifying the code to incorporate the fiscal year column, ensuring it aligns with the user’s requirements.

Model used: ChatGPT-4

To follow along, open ChatGPT_for_PowerBI_3.pbix from the exercise files folder or continue using the previous file.

Prompt: I have a calendar table code in M. Can you tell me the code to add fiscal year column in this table. The fiscal year starts from july. The code is:  let

    // Retrieve the minimum and maximum dates from the Work Log table

    MinDate = List.Min(Work_Log[Date]),

    MaxDate = List.Max(Work_Log[Date]),

    // Generate a list of dates from MinDate to MaxDate

    DateList = List.Dates(MinDate, Duration.Days(MaxDate – MinDate) + 1, #duration(1,0,0,0)),

    // Convert the list of dates to a table

    Calendar = Table.FromList(DateList, Splitter.SplitByNothing(), {“Date”}),

    // Add additional calendar columns here if needed (like Year, Month, etc.)

    AddYear = Table.AddColumn(Calendar, “Year”, each Date.Year([Date])),

    AddMonth = Table.AddColumn(AddYear, “Month”, each Date.Month([Date])),

    AddDay = Table.AddColumn(AddMonth, “Day”, each Date.Day([Date]))

in

    AddDay

Code Formatting and Optimization

In this chat with ChatGPT-4, we’re focusing on optimizing existing M code within Power BI. The user presents a code snippet for generating a calendar table and requests assistance in optimizing it. Specifically, they seek guidance on naming the query appropriately, adding comments, and providing a description. ChatGPT-4 provides step-by-step instructions on optimizing the code, including proper naming conventions, adding comments for clarity, and incorporating a description.

Model used: ChatGPT-4

To follow along, open ChatGPT_for_PowerBI_4.pbix from the exercise files folder or continue using the previous file.

Prompt: Can you optimize this code by naming this query appropriately. Adding comments and Description.

let

    // Retrieve the minimum and maximum dates from the Work Log table

    MinDate = List.Min(Work_Log[Date]),

    MaxDate = List.Max(Work_Log[Date]),

    // Generate a list of dates from MinDate to MaxDate

    DateList = List.Dates(MinDate, Duration.Days(MaxDate – MinDate) + 1, #duration(1,0,0,0)),

    // Convert the list of dates to a table

    Calendar = Table.FromList(DateList, Splitter.SplitByNothing(), {“Date”}),

    // Add additional calendar columns here if needed (like Year, Month, etc.)

    AddYear = Table.AddColumn(Calendar, “Year”, each Date.Year([Date])),

    AddMonth = Table.AddColumn(AddYear, “Month”, each Date.Month([Date])),

    AddDay = Table.AddColumn(AddMonth, “Day”, each Date.Day([Date])),

    // Custom function to determine Fiscal Year

    FiscalYear = (date) => if Date.Month(date) >= 7 then Date.Year(date) + 1 else Date.Year(date),

    // Add Fiscal Year Column

    AddFiscalYear = Table.AddColumn(AddDay, “Fiscal Year”, each FiscalYear([Date]))

in

    AddFiscalYear

Solving Complex Problems using M and DAX

In this chat with ChatGPT-4, we’re delving into Power BI’s query editor capabilities. In the first prompt, the user requests assistance in writing M-Code for the Power BI query editor. They provide details about a Rate Table with columns such as Employee ID, Start Date, End Date, and Rate. The code should generate a list of dates based on the Start Date and End Date values, with the output containing Employee ID, Date, and Rate. ChatGPT-4 guides the user through crafting the M-Code to fulfill the requirements, ensuring efficient data transformation in Power BI. In the second prompt, the user inquires about achieving the same functionality in DAX. They seek assistance in writing DAX code to replicate the functionality of generating a list of dates based on the Rate Table’s Start Date and End Date columns.

Model used: ChatGPT-4

To follow along, open ChatGPT_for_PowerBI_5.pbix from the exercise files folder or continue using the previous file.

First Prompt: Can you help me write an M-Code for Power BI query editor. The Rate Table has these columns: Employee ID, Start Date, End Date, Rate. Now the code should check the Start Date and End Date value from Rates table and generate a list of dates. The output should contain Employee ID, Date, Rate.

Second Prompt: Can i get the same functionality in DAX? can you give me a code in DAX for the same functionality

Generating Advanced DAX Formulas

In this chat with ChatGPT-4, we’re exploring how to generate invoices for employees in Power BI using DAX. The user describes a scenario where they have an Employees table connected to the Rates table and the Work_Log table via Employee ID in a one-to-many relationship. They seek assistance in writing a DAX code that can generate invoices for employees based on the hours they worked and the applicable rate. ChatGPT-4 provides guidance on crafting the DAX code to fulfill the requirements. The code should check if an employee has worked on a specific date between the starting and ending dates in the Rates table, then apply the corresponding rate. Through this interaction, we gain valuable insights into leveraging DAX to perform complex calculations and generate invoices in Power BI, streamlining the invoicing process for the user.

Model used: ChatGPT-4

To follow along, open ChatGPT_for_PowerBI_6.pbix from the exercise files folder or continue using the previous file. 

Prompt: I have Employees table which is connected with 1 to many with Rates table via Employee ID and Employees table is connected with 1 to many with Work_Log table via Employee ID. Can you help me write a DAX code that can generate invoices for employees based on hours they worked and the rate applicable. The Hours Worked column in Work_Log table contains the hours that employee has worked on a particular date and Rate column in Rates table contains the rate applicable. The Rates table contains Start Date, End Date and Rate so the code should check if employee has worked on some date between the starting and ending date, then apply the rate.

Visualization Tips and Tricks

In this chat with ChatGPT-4, we’re exploring data visualization in Power BI. The user requests assistance in identifying key performance indicators (KPIs) for visualization in Power BI and suggests the best visuals for representing those KPIs effectively. ChatGPT-4 provides guidance on analyzing the dataset manually to identify relevant KPIs based on the user’s specific requirements. It then suggests the best visuals, such as line charts, bar charts, or pie charts, for visualizing these KPIs in Power BI.

Model used: ChatGPT-4

To follow along, open ChatGPT_for_PowerBI_6.pbix from the exercise files folder or continue using the previous file. 

Prompt: Manually analyze the data set, tell me what KPI’s should be used for visualization and suggest me best visuals for those KPIs in Power BI

Unit 7: ChatGPT with Python and SQL

In this chapter, we’ll learn how ChatGPT can help us explore data better. It’s like having a friendly helper to guide us through the data analysis process. ChatGPT can tell us important things about our data, like numbers and patterns. It can even write code for us in Python and SQL, making our work easier and faster. With ChatGPT’s help, we’ll be able to understand our data better and find the answers we need quickly.

Exploring EDA:

In this chat with ChatGPT-4, we engage in exploratory data analysis (EDA) using the Titanic dataset. By manually examining the dataset, we seek to understand its contents and glean insights into the historic catastrophe. Through conversational interactions, ChatGPT assists in generating five powerful visualizations that shed light on key aspects of the Titanic disaster. Additionally, ChatGPT aids in creating a PowerPoint presentation, with each visualization showcased on a separate slide. The narrative text for each visualization is formatted appropriately, ensuring clarity and readability. Through this collaborative process, we gain valuable insights and effectively communicate our findings.

Model used: ChatGPT-4

To proceed, please open the “TitanicDataSet.csv” file located in the exercise files folder..

First Prompt:  Manually, read the data set. Tell me what it is about and perform EDA on the data set. Give me 5 visuals that give powerful insights into the catastrophe

Second Prompt: Can you create a power point presentation. Create a new slide separately for each of these visualizations. There should be one visual on each slide. Update the narrative text for each visualization to be 12pt font and text wrapped it. it should be added to notes. The visualization should be 0.25in from the left of the slide.

Generating Python Scripts

In this chat, we leverage Google Colab’s built-in feature that employs natural language to code efficiently. Through conversational interactions, users can articulate their coding requirements in plain language, allowing Google Colab to generate corresponding code snippets or templates. This feature streamlines the coding process, enabling users to execute tasks more effectively and expediently.

Prompt: Can you perform EDA on TitanicDataSet.csv

Advanced-Data Visualization Techniques in Python

In this interaction with ChatGPT-4, we task the model with creating a 3D scatter plot visualizing data from the Titanic dataset. Through conversational prompts, ChatGPT generates a plot titled “3D Scatter Plot of Titanic Passengers,” showcasing key attributes such as age, fare, passenger class, and survival status. The plot utilizes purple crosses (x) to denote passengers who did not survive and yellow stars (★) to represent survivors, enabling us to discern patterns or correlations among these variables. With ChatGPT’s assistance, we effectively visualize and analyze insights from the Titanic dataset.

Model used: ChatGPT-4  

To proceed, please open the “TitanicDataSet.csv” file located in the exercise files folder..

Prompt: Create a 3D scatter plot titled “3D Scatter Plot of Titanic Passengers” that visualizes data related to the Titanic’s passengers. This plot should have three axes, with the X-axis representing the ‘Age’ of passengers ranging from 0 to 80, the Y-axis showing the ‘Fare’ passengers paid from 0 to over 500, and the Z-axis indicating the ‘Passenger Class’ from 1 to 3. Include two types of data points: purple crosses (x) for passengers who did not survive and yellow stars (★) for survivors. The graph should allow for the interpretation of patterns or correlations among age, fare, class, and survival rates.

Grasping Core SQL Principles

In this chat with ChatGPT-4, we seek assistance in understanding the concept of JOINS in SQL. Through conversational prompts, ChatGPT provides insights into JOINS and shares sample code along with a dataset to illustrate their application. Additionally, ChatGPT suggests online sources for further exploration of the concept, empowering us to deepen our understanding of SQL JOINS effectively. With ChatGPT’s guidance, we enhance our knowledge and proficiency in SQL data manipulation techniques.

Model used: ChatGPT-4  

Prompt:  Can you help me understand the concept of JOINS in SQL. can you share sample code with data set and provide online sources to understand the concept.

From Data Model to SQL Query:

In this chat with ChatGPT-4, we aim to craft a SQL query for analyzing the top 5 customers across various regions on a monthly basis. However, it’s important to note that interactive chat functionality for this type of interaction is not yet supported in ChatGPT. We proceed by providing the model with the necessary details, such as accessing the “DataModel.png” file from the exercise files folder. ChatGPT-4 then assists by generating the SQL query, enabling us to conduct the desired analysis effectively. Despite the lack of interactive chat, ChatGPT remains a valuable tool for automating SQL query generation tasks.

Model used: ChatGPT-4  

To proceed, please open the “DataModel.png” file located in the exercise files folder..

Prompt:   Can you help me write a SQL query that analyzes top 5 customers across different regions and i want the analysis on monthly basis

Unit 8: ChatGPT with Advanced Analytics

In this chapter, we delve into the advanced analytics functionalities of ChatGPT-4, showcasing its versatility in handling various data operations and analytical tasks. From data extraction and cleansing to outlier detection and fraud identification, ChatGPT-4 offers valuable assistance in optimizing data workflows and enhancing analytical insights. Through practical examples and demonstrations, we explore how ChatGPT-4 can be leveraged for efficient data organization, smart categorization, and insightful narrative development.

Data Operations: Extract, Fill, Resolve:

In this chat with ChatGPT-4, we explore the process of filing travel expense claims and ensuring policy compliance. By reviewing the “Travel Expense Claim Form” and “AMZ Travel Policy” documents, we aim to understand the necessary requirements for filing a claim. With detailed information about a recent trip, we ask ChatGPT-4 to create a similar claim form, format it appropriately, and provide us with the file. Additionally, we seek its assistance in comparing two documents to identify the reason behind a claim rejection, ensuring adherence to company policies and procedures.

Model used: ChatGPT-4  

To proceed, please open the “Travel Expense Claim Form.pdf” and “AMZ_Travel_Policy.pdf”  file located in the exercise files folder..

First Prompt:   Please review this document and inform me about the requirements I need to fulfill to file this claim.

Second Prompt:   I’m Ali, an AMZ trainer who traveled on March 25, 2023, flying across various Australian cities. I stayed in Brisbane at Solaris at Azure Heights for 2 nights ($120/night) and had 4 meals ($30 each) along with a $200 conference fee. In Melbourne, I stayed at the Green Inn for 3 nights ($100/night) and had $40 client dinners. My flights and local transport totaled $50 and $20/day, respectively, with $25 spent on laundry. It was a productive and memorable journey.

Third Prompt: Could you create a form using a similar template, incorporating all the necessary information, format the document, and provide me with the file?

Fourth Prompt: I’ve submitted my claim, but it was rejected. Could you compare these two documents and identify the reason for the rejection?

Presentation Crafting Techniques:

In this section, we’ll employ the Presentation.AI tool to create presentations seamlessly using natural language.

Data Operations: Extract, Fill, Resolve:

In this chat with ChatGPT-4, we explore the process of filing travel expense claims and ensuring policy compliance. By reviewing the “Travel Expense Claim Form” and “AMZ Travel Policy” documents, we aim to understand the necessary requirements for filing a claim. With detailed information about a recent trip, we ask ChatGPT-4 to create a similar claim form, format it appropriately, and provide us with the file. Additionally, we seek its assistance in comparing two documents to identify the reason behind a claim rejection, ensuring adherence to company policies and procedures.

Prompt:   Customer loyalty and rewards program for AMZ Consulting Pty Ltd, a company providing Power BI Training and Consulting

Conflict Identification and Resolution:

In this chat session with ChatGPT-4, we explore the contents of an email conversation dataset contained in the “Email_Conversation_Dataset.pdf” file. Our first prompt tasks ChatGPT-4 with reviewing the chain of emails, summarizing the primary issue under discussion, and identifying any conflicts or disagreements that emerge throughout the conversation

Model used: ChatGPT-4  

To proceed, please open the “Email_Conversation_Dataset.pdf” file located in the exercise files folder..

First Prompt:   Could you please review this chain of emails, provide a summary of the main issue being discussed, and point out any conflicts or disagreements that arise within the conversation?

Advanced Data Grouping and Cleaning:

In this chat session, we utilize ChatGPT-4 to analyze the “Training_Participants_Data.xlsx” file. Our first prompt tasks ChatGPT-4 with examining the dataset, identifying any inconsistencies, and generating cleaned groups for cities. Following that, we request ChatGPT-4 to replace the cities with their clean counterparts and provide us with an updated dataset.

Model used: ChatGPT-4  

To proceed, please open the “Training_Participants_Data.xlsx” file located in the exercise files folder..

First Prompt:   Can you examine this data, identify the inconsistencies and generate cleaned groups for cities

Second Prompt:  Replace the cities with their clean counterparts. Give me updated data set.

Detecting Data Outliers:

In this chat session, we engage ChatGPT-4 to analyze the “Retail_Sales_Data.xlsx” file. Initially, we ask ChatGPT-4 to manually review the dataset and illustrate the trend of monthly sales using a line graph. Following that, we request assistance in detecting outliers within the monthly sales trend, calculating upper and lower bounds, and then visually displaying these bounds alongside the outliers as separate points on a line graph. Lastly, we task ChatGPT-4 with analyzing the dataset to propose potential reasons for the presence of outlier data points.

Model used: ChatGPT-4  

To proceed, please open the “Retail_Sales_Data.xlsx” file located in the exercise files folder..

First Prompt: Manually, read this data set, show me the trend of monthly sales in a line graph.

Second Prompt: Can you detect outliers in the monthly sales trend, calculate the upper and lower bounds, and then display these bounds alongside the outliers as separate points on a line graph?

Third Prompt: Can you analyze the data seta and suggest possible reasons for these outlier data points

Graphics-Aided Nutritional Content Display:

In this chat session, we utilize ChatGPT-4 to assist in meal planning. Firstly, we ask for a list of ingredients and their quantities needed to prepare pasta suitable for a single serving for a 35-year-old male. Next, we expand the grocery list to include ingredients for a delicious brownie dessert. Finally, we request ChatGPT-4 to create a graphical representation of the nutritional content of the meal, incorporating both the pasta and the brownie. Throughout the conversation, we explore ChatGPT’s capabilities in automatically calculating and visualizing the nutritional content to ensure a balanced meal.

Model used: ChatGPT-4  

First Prompt:   I would like to make pasta for my dinner tonight. Could you please provide a list of ingredients and their quantities that I need to buy for a single serving, suitable for a 35-year-old male?

Second Prompt:  I would also like to enjoy a delicious brownie for dessert. Could you update the grocery list to include the ingredients for the brownie as well?

Third Prompt: Could you create a graphical representation of the nutritional content of my meal tonight, including both the pasta and the brownie?

Techniques for Fraud Detection:

In this chat, we leverage ChatGPT-4 to analyze a dataset of credit card transactions stored in an Excel file. Initially, we outline criteria for identifying potentially fraudulent transactions, including extreme transaction amounts, unusual locations or times, and rapid transactions. We then request ChatGPT-4 to examine the dataset, flag suspicious transactions, and provide reasons for their classification. Finally, we ask for an updated dataset with the flagged transactions and reasons for further review and analysis. Through this interaction, we explore ChatGPT-4’s capabilities in fraud detection and data manipulation for financial analysis.

Model used: ChatGPT-4  

To proceed, please open the “Credit_Card_Transactions.xlsx” file located in the exercise files folder..

First Prompt:   Could you please analyze the datasets and assist me in identifying fraudulent transactions? I have listed the following reasons, but you are welcome to suggest more:

  1. Extremely high or very low transaction amounts.
  2. Unusual transaction locations.
  3. Transactions occurring at unusual times, such as late at night or in the very early morning.
  4. Rapid transactions, defined as more than three within a 30-minute period.

Additionally, I would like you to create two columns in the dataset: one for flagging the transaction and another for identifying the reason.

Second Prompt:  Can you give me updated data set?

Efficient Data Organization:

In this chat, we engage ChatGPT-4 to help organize a zip folder containing various files. We first request ChatGPT-4 to examine the contents of the folder and provide a summary of its contents. Then, we ask for assistance in renaming each file with more descriptive and clear names, adhering to specific naming conventions. ChatGPT-4 generates an organized list, presenting each file’s original name alongside its proposed new name for easy comparison and improved folder organization. Through this interaction, we explore ChatGPT-4’s capabilities in file management and organization.

Model used: ChatGPT-4  

To proceed, please open the “Folder.zip” file located in the exercise files folder..

First Prompt:  I need assistance with organizing a zip folder. Could you open the folder and provide a concise summary of its contents? For each file, I’d like you to suggest more descriptive and clear names. Please use only lowercase letters (a-z) and numbers (0-9) in the new names, and ensure you retain the correct file extensions. The output should be an organized list, with each file’s original name followed by its proposed new name for easy comparison. This will help me understand the contents and improve the folder’s organization.

Image Editing and Catalog Production:

In this chat, we utilize ChatGPT-4 to process a video file titled “Canvas to Dashboard.mp4.” Initially, we request ChatGPT-4 to generate 10 frames from the video, ensuring they are evenly distributed across time intervals. Following this, we task ChatGPT-4 with resizing the images while maintaining their aspect ratio. Subsequently, we ask ChatGPT-4 to compile an animated GIF using all the modified images, with each frame transitioning every 1 second. Finally, we prompt ChatGPT-4 to document all the steps and operations performed in a Word file, providing a comprehensive step-by-step guide for reference. Through this interaction, we explore ChatGPT-4’s capabilities in video processing, image manipulation, and documentation.

Model used: ChatGPT-4  

To proceed, please open the “Folder.zip” file located in the exercise files folder..

First Prompt:  Generate 10 frames from the video, ensuring they are evenly distributed in terms of time intervals.

Second Prompt: Resize the images while preserving their aspect ratio.

Third Prompt: Create an animated GIF using all the modified images, with each frame transitioning to the next one every 1 second.

Fourth Prompt: Document all the steps and operations performed in a word file for reference with a step by step guide.

Product Categorization with AI:

In this chat, we engage ChatGPT-4 to assist with organizing product data stored in the “Products.xlsx” file. Initially, we request ChatGPT-4 to manually review the Excel file and propose strategies for categorizing products effectively. Following this, we task ChatGPT-4 with creating price range categories for each product to facilitate further analysis. Finally, we prompt ChatGPT-4 to add a Price Category column to the Excel file and provide us with an updated version. Through this interaction, we leverage ChatGPT-4’s capabilities to streamline data organization and analysis in Excel.

Model used: ChatGPT-4  

To proceed, please open the “ Products.xlsx” file located in the exercise files folder..

First Prompt:  Manually, read this excel and suggest ways in which you can categorize products.

Second Prompt:  Can you create a category by price range for each product?

Third Prompt: Can you create a Price Category column in the excel i shared and give me an updated excel.

Developing Smart Narrative:

In this chat, we rely on ChatGPT-4 to compare two stock data reports from December 2022 and January 2023. We task ChatGPT-4 with manually examining the reports to identify patterns and differences, enabling us to craft a coherent narrative around the stock performance during these two periods. By leveraging ChatGPT-4’s analytical capabilities, we aim to gain insights into the stock market trends and potential factors influencing the changes observed between the two months.

Model used: ChatGPT-4  

To proceed, please open the “Stock_Data_December_2022.xlsx” and “Stock_Data_January_2023” file located in the exercise files folder..

Prompt:    Manually examine and contrast these two reports? Identify patterns and craft a smart narrative. 

Temperature Setting in AI Models:

In this chat session utilizing ChatGPT-4, we explore temperature settings in AI models, specifically focusing on analyzing sales data. We open the “Stock_Data_January_2023” file and prompt ChatGPT-4 with different temperature settings to examine unusual patterns in the sales data. By adjusting the temperature parameter, we aim to observe how ChatGPT-4 generates responses at different levels of creativity and variability, providing insights into potential anomalies or trends in the sales data.

Model used: ChatGPT-4  

To proceed, please open the “Stock_Data_January_2023” file located in the exercise files folder..

First Prompt: What are some unusual patterns in our sales data?” (Temperature = 0)

Second Prompt: What are some unusual patterns in our sales data?” (Temperature = 0.5)

Third Prompt: What are some unusual patterns in our sales data?” (Temperature = 1)

Unit 9: ChatGPT Plugins

Explore the capabilities of ChatGPT plugins through a series of prompts designed to demonstrate their functionality. Delve into plugin processes, learn techniques for accessing internet resources, and uncover the role of an AI data analyst in leveraging plugins for tasks such as fetching and analyzing quotes. Dive into current trend visualization to understand how plugins enhance ChatGPT’s capabilities in data analysis and presentation.

Internet Access Techniques:

In this chat with ChatGPT, we explore internet access techniques by asking about the performance of the Australian dollar today. This prompt demonstrates how ChatGPT can utilize plugins to fetch real-time information from the internet, providing timely and relevant responses to user queries.

Prompt:  How well is the Australian $ performing today?

Role of an AI Data Analyst:

In this chat with ChatGPT, we explore the role of an AI data analyst by requesting a list of the top 10 best-performing products from a sales sheet and retrieving their names from a products sheet. By providing a database link, the prompt showcases how ChatGPT can act as an AI data analyst, extracting insights and information from structured datasets to support decision-making processes.

Prompt:   List the top 10 best performing products from my sales sheet and get their names from products sheet. here’s my data base: https://docs.google.com/spreadsheets/d/19F3qL-XwTwQ9rLSJpMLZ9bcH64Jpzk-C/edit?usp=sharing&ouid=111061698495289074485&rtpof=true&sd=true

Fetching and Analyzing Quotes:

In this chat with ChatGPT, we task the model with fetching and analyzing quotes, specifically requesting the 14-day Relative Strength Index (RSI) for Microsoft Corporation (MSFT) over the last three months. This prompt demonstrates how ChatGPT can leverage internet access techniques to retrieve financial data and perform analysis, assisting users in making informed decisions in the financial domain.

Prompt:   I’d like to see the 14-day Relative Strength Index (RSI) for Microsoft Corporation (MSFT) for the last three months.

Current Trend Visualization:

In this chat, we task ChatGPT with visualizing the current trends in AI’s role in data analytics. We request a mind map illustrating the expanding applications of AI, including branches for Automated Data Cleaning, Predictive Analytics, Natural Language Processing, Image Recognition, and Decision Making. This demonstrates how ChatGPT can assist in summarizing complex concepts and trends, providing users with clear and insightful visualizations of important topics.

Prompt:   Create a mind map to illustrate the expanding role of AI in data analytics. Include branches for Automated Data Cleaning, Predictive Analytics, Natural Language Processing, Image Recognition, and Decision Making.

Unit 10: Enhancing interaction: ChatGPT's multimodal features

This unit explores how ChatGPT can do more than just chat. We’ll see how it can browse the internet for the latest info and create detailed 3D graphs to visualize data. With these features, ChatGPT becomes a versatile tool for accessing information and presenting insights in various formats.

Internet Access Techniques:

In this chat, we task ChatGPT-4 with browsing the internet for the latest advancements in artificial intelligence related to data analytics. By leveraging ChatGPT’s capabilities, we aim to stay updated on cutting-edge developments in AI, allowing us to remain informed and knowledgeable about the rapidly evolving field of data analytics.

Model used: ChatGPT-4  

Prompt:   Browse the internet for the latest advancements in artificial intelligence that are related to data analytics

Introduction to DELLE within ChatGPT:

In this chat, we introduce the concept of DELLE (Data Exploration, Learning, and Evaluation) within ChatGPT-4, focusing on its capability to assist in data visualization tasks. We prompt ChatGPT-4 to generate a 3D bar graph illustrating quarterly revenue growth for three products based on the provided dataset. The graph is designed to showcase revenue trends over time, with clear labeling and shading for enhanced visualization. By leveraging DELLE, we aim to streamline the process of data exploration and analysis, making insights more accessible and actionable.

To proceed, please open the “Quarterly_Revenue_Growth.xlxs”  file located in the exercise files folder..

Model used: ChatGPT-4  

Prompt:   Generate a 3D bar graph showing quarterly revenue growth for three products. On the x-axis, place the quarters from ‘Q1 21’ to ‘Q4 22’, ensuring sufficient spacing to avoid overlap. Label the y-axis with ‘Product 1’, ‘Product 2’, and ‘Product 3’, arranging them for clear visibility. The z-axis should represent revenue. Include bars for each product in each quarter to depict an upward trend in revenue growth. Shade the bars for a depth effect. Most importantly, arrange the axis ticks and labels so they are distinct and non-overlapping, ensuring readability and clarity in presentation