How to Create Power BI Dashboard Using ChatGPT

Building a high-impact dashboard in Power BI can feel like you're manually wrestling complex formulas and code instead of uncovering insights. But by using ChatGPT as an intelligent assistant, you can automate the tricky parts - like writing DAX measures and Power Query scripts - and focus on what the data actually means. This tutorial will walk you through the practical steps to integrate ChatGPT into your Power BI workflow, from generating sample data to crafting sophisticated calculations.

Why Pair ChatGPT with Power BI?

Power BI is an incredible tool for data visualization, but it comes with a steep learning curve. The two biggest hurdles for many users are:

• M Language: The script behind all the data cleaning and transformation in Power Query. It's powerful but not intuitive for Excel users.
• DAX (Data Analysis Expressions): The formula language used to create custom calculations and measures. Crafting complex DAX can be time-consuming and frustrating, even for experienced analysts.

ChatGPT acts as a translator and a co-pilot. You can describe what you need in plain English, and it will generate the M code or DAX formula for you. This dramatically lowers the barrier to entry and speeds up your workflow. You stop fighting with syntax and start getting answers.

Step 1: Generate Sample Data with ChatGPT

Before you build a dashboard, you need data. If you don't have a dataset handy or just want to practice, you can ask ChatGPT to create one for you. The key is to be specific about the columns, formats, and number of rows you want.

Let's say we want to create a dashboard for an online store that sells coffee beans. We can ask ChatGPT to generate a sample sales dataset in CSV format.

Example Prompt for Generating Data:

Generate a sample dataset of 50 rows in CSV format for coffee bean sales. Include the following columns:
- OrderID (a unique ID)
- OrderDate (a date in YYYY-MM-DD format from the last 12 months)
- Product (e.g., "Ethiopian Yirgacheffe," "Colombian Supremo," "Sumatra Mandheling")
- Category (e.g., "Single Origin," "Blend")
- Region (e.g., "North America," "Europe," "Asia")
- Quantity (a number between 1 and 5)
- UnitPrice (a price between 12.00 and 22.00)

ChatGPT will generate a perfectly formatted CSV block that you can copy and paste directly into a plain text file (like Notepad) and save with a .csv extension. Now you have a clean dataset ready to be loaded into Power BI.

Step 2: Get Your Data into Power BI

With your sample data ready, the next step is to load it into Power BI Desktop.

1. Open Power BI Desktop.
2. On the Home ribbon, click Get Data.
3. Select Text/CSV from the list.
4. Navigate to your saved .csv file and click Open.
5. A preview window will appear. The data should look clean. Click Transform Data to open the Power Query Editor. This is where we'll start shaping our data.

Always double-check that Power BI has detected the data types for each column correctly (e.g., OrderDate as a date, UnitPrice as a decimal number). You can change the data type by clicking the icon next to the column header in the Power Query Editor.

Step 3: Supercharge Power Query with M Code from ChatGPT

Power Query is where you clean, shape, and prepare your data before building visualizations. While its user interface handles many common tasks, sometimes you need a custom transformation that requires M code.

Let's imagine our OrderID is in a format like NA-2024-1001 for North America, EU-2024-2001 for Europe, etc. Extracting the "NA" or "EU" abbreviation would give us a useful region code. Instead of clicking through menus, we can just ask ChatGPT for the M code.

Example Prompt for M Code:

I have a column in Power BI's Power Query called "OrderID" with values like "NA-2024-1001". Write the M code to create a new column named "RegionCode" that extracts an abbreviation (the part before the first hyphen).

ChatGPT's Generated M Code:

Table.AddColumn(#"Previous Step Name", "RegionCode", each Text.BeforeDelimiter([OrderID], "-"), type text)

Important Note: You'll need to replace #"Previous Step Name" with the actual name of your previous applied step in Power Query, which is usually "Changed Type". You can see this in the "Applied Steps" pane on the right-hand side.

How to Use the M Code in Power BI:

1. In the Power Query Editor, go to the View tab and click on Advanced Editor.
2. The M code for all your transformations will appear. Carefully find the last step (e.g., "Changed Type").
3. Add a comma at the end of the last step's line, press Enter, and paste the code generated by ChatGPT on the new line, updating the step name carefully.
4. Finally, update the final in statement at the end of the script to reference your newly added step's name.
5. ...or easier, you can add a new custom column and paste the core logic Text.BeforeDelimiter([OrderID], "-") there.

This speeds up custom data transformations and helps you learn how the M language works just by observing the patterns.

Step 4: Let ChatGPT Write Complex DAX Formulas

This is where ChatGPT becomes truly invaluable. Writing DAX is the most powerful - and often most difficult - part of building analytical dashboards. You use DAX to create measures (like calculating total revenue) and calculated columns.

When prompting ChatGPT for DAX, a best practice is to always specify your table and column names in the format 'TableName'[ColumnName]. This helps the AI generate accurate, ready-to-use code.

Example 1: A Basic Revenue Measure

First, let's create a calculated column for total revenue per order, which is Quantity * UnitPrice.

Prompt:

Write a DAX formula for a calculated column named "Revenue" in my 'Sales' table. It should multiply the 'Sales'[Quantity] column by the 'Sales'[UnitPrice] column.

ChatGPT's Generated DAX:

Revenue = 'Sales'[Quantity] * 'Sales'[UnitPrice]

You can create this by selecting "New column" in the report view and pasting in the formula.

Example 2: A Time Intelligence Measure (Year-to-Date Sales)

Time intelligence calculations are essential for business reporting but can be a headache in DAX. Let’s create a YTD revenue measure.

Note: For time intelligence DAX functions, you almost always need a separate "Date" table in your data model, marked as a date table, and related to your sales table.

Prompt:

Write a Power BI DAX measure for Year-to-Date revenue. My total revenue is a measure named [Total Revenue], and my date table is named 'Date' with a column 'Date'[Date].

ChatGPT's Generated DAX:

YTD Revenue = TOTALYTD([Total Revenue], 'Date'[Date])

With just one short English sentence, you've generated a sophisticated DAX formula that would otherwise have required looking up syntax and functions.

Example 3: A Complicated RANKX Formula

What if you want to rank products by their sales revenue? The RANKX function can be tricky to get right.

Prompt:

I have a table called 'Sales' and a calculated measure named [Total Revenue]. Write a DAX measure that ranks each product in the 'Sales'[Product] column based on [Total Revenue] in descending order.

ChatGPT's Generated DAX:

Product Rank = RANKX(ALLSELECTED('Sales'[Product]), [Total Revenue], , DESC, Dense)

ChatGPT not only provides the formula but also correctly uses ALLSELECTED() to ensure the ranking works correctly within filters on the report page. This saves a huge amount of testing and debugging.

Step 5: Get Help with Dashboard Design and Insights

Beyond writing code, ChatGPT can function as a brainstorming partner. If you're stuck looking at a blank canvas, you can ask for suggestions on which visualizations would tell the most compelling story.

Example Prompt for Visualization Ideas:

I'm building a sales dashboard in Power BI with data on revenue, order date, product category, and region. Suggest 5 key charts and visuals to include and what KPI each one should highlight.

Example Output:

• Total Revenue and Orders Over Time (Line Chart): Tracks overall performance and identifies trends or seasonality.
• Revenue by Product Category (Bar Chart): Quickly shows which product categories are the most profitable.
• Sales by Region (Map Visual): Provides a geographical overview of where sales are concentrated.
• KPI Cards: Shows high-level metrics like Total Revenue, Average Order Value, and Total Customers for at-a-glance monitoring.
• Product Performance Table: A detailed view showing revenue, quantity sold, and product rank for individual products.

This gives you a clear roadmap for adding visuals to your report page, helping you build a comprehensive and easy-to-understand dashboard.

Final Thoughts

Integrating ChatGPT into your Power BI process helps bridge the gap between the insights you want and the technical steps required to get there. It demystifies DAX and M, automates repetitive coding tasks, and even helps brainstorm the story your data is telling. It empowers you to build more effective dashboards, faster.

While using ChatGPT with Power BI is a great way to speed things up, it still involves switching between tools and manually building the dashboard. When you're ready to remove all that friction, we designed Graphed to feel like the next step. We've built an AI-native analytics platform where you simply describe the dashboard you want in plain English, and have it built for you in seconds using your live data - without ever writing a line of DAX or M code yourself.