ChatGPT Power BI integration is coming directly from Microsoft under the Copilot brand name. It will soon be able to answer questions about your data and create entire dashboards with AI. In the meantime, ChatGPT can supercharge Power BI dashboard development to write DAX formulas, Power Query M code.
Microsoft is hard at work integrating the technology behind ChatGPT into Microsoft Office and the entire Power Platform. We’re excited to see some of the ChatGPT style features that have already been released, and are excited to see what Microsoft has in store for the platform as they integrate it deeper into the report creation experience.
Let’s take a look at how to use ChatGPT and Power BI together today, and what features are in coming in the near future!
Table of Contents
Why ChatGPT and Power BI Work so Well Together
ChatGPT enables Power BI developers to create dashboards and reports faster than ever. Because ChatGPT is trained on dozens of different programming languages, it is well versed in generating code that can be used in Power Query and writing DAX formulas.
The biggest value add though, is not just in formula creation, but it will break apart formulas to explain each component so that developers can understand the reasoning why a certain formula or syntax is required.
Power BI users can also use Power BI to generate code to copy and paste into a report. Soon, Power BI will have ChatGPT fully integrated as part of the Microsoft Copilot Brand name making the integration even deeper and more useful.
Using Power BI as a Quick Reference Guide
Our favorite use of ChatGPT in Power BI is explaining which functions are needed to perform a task. For example, if you prompt ChatGPT how to perform a task, it will list out step by step instructions of how to perform the action and complete the task in Power BI.
In the example below, we ask Power BI how to combine two datasets with the same columns.
ChatGPT correctly returns the step-by-step instructions of how to use the append function. While this is a simple example, it’s incredibly useful for people new to the Power BI platform that may not know the language to describe the Append function.
The only drawback to ChatGPT at this time is that it does not display screenshots of specific actions which still maintains some relevance for searching for Power BI tutorials.
Using Chat GPT to Write DAX Formulas
There are two ways that Power BI can help you write DAX formulas. It can either be prompted with the specific table name and field names of your data, or it you can use it as a quick reference guide to learn why a formula is setup the way that it is.
In the example below we feed ChatGPT with information about the Power BI data model that we are using. We include the table name, along with the specific field name for the calculation desired for a Running Total DAX formula.
One of the fields that ChatGPT needs to create a DAX formula was missing. In the formula it uses the place holder date[YourDateColumn]
To copy the formula from ChatGPT to Power BI, click on the Copy Code button at the top right of the ChatGPT response. Paste a copied formula into a New Measure by pressing CTRL + V in the Power BI formula entry bar.
Using ChatGPT to Write Power Query M
While DAX is for formulas and Data Analysis that lets users filter and drill into dashboards and reports, Power Query is used for data preparation and transformation. Many functions can be performed in either DAX or Power BI. Luckily, ChatGPT can write Power Query code and explain how to use it.
The example response below explains how to use Power Query to generate a running total. It provides detailed instructions and goes beyond just writing the code. It also lists additional steps involved, such as adding an index column and a custom column that are part of a larger process.
The only drawback to using ChatGPT to generate formulas and code is that it lacks context to recommend when you should use a DAX formula as part of report building or create it in Power Query. In the case of a Running Total, there is some nuance as to when you should choose one method over the other.
ChatGPT Can Parse Power Query M for Documentation
Another great use of ChatGPT is to copy and paste an existing set of Power Query steps into it and provide a prompt requesting it to Provide easy to read bullet points of what the following query is doing. ChatGPT will take the incoming Power BI data transformation steps, list them out and expand on what exactly each line of code is doing.
While we recommend adding notes and renaming steps in the Power Query Editor to explain why each step exists, sometimes it is not practical or you are inheriting a pre-existing sept of data prep steps.
ChatGPT can parse the steps in Power BI and could even be used a starting point for process audit documentation.
Using ChatGPT to write SQL Queries
ChatGPT is capable of generating basic to advanced SQL queries. It has knowledge of many different SQL syntaxes, and can even convert between the two. The biggest challenge when using ChatGPT to write SQL queries is that it won’t have an understanding of specific SQL table structures that make up your data model. Try feeding it as much information as possible, such as field names and table names to generate a query that requires little adjustment after the fact.
In the example above, we only provided minimal information for our query and would likely need to update the exact table and field names.
You may also run into errors when trying to execute the query above because we did not define which type of SQL database we were connecting to, and the required syntax might differ from what was generated.
Using ChatGPT to Connect Power BI to SQL
Most report dashboards in Power BI are created with Excel or CSV files as the starting point. They are easy to get ahold of and typically export directly from a source system, such as accounting software or an ERP. However, SQL is our preferred data source when available because the data is live and always up to date.
ChatGPT can be a great technical reference as a starting point to help people get systems connected directly to each other.
Try asking ChatGPT how to connect Power BI to SAP, QuickBooks, or another popular accounting system. It will provide general information that can be asked follow-up questions.
Note: ChatGPT may only provide generic information on system integrations as its training only goes up to September 2021. It’s incredibly useful for explaining technical terms, and combined with Google is a great reference for system integrations with Power BI.
Microsoft Copilot will Integrate ChatGPT Directly into Power BI
After Microsoft invested $10 billion into OpenAI, the creators of ChatGPT they have been hard at work integrating ChatGPT into Power BI along with a number of other products. Some features are already available and can be used today, such as an advanced Quick Measure editor that suggests DAX formulas based on natural language prompts, and using AI to generate first drafts of dashboards based on datasets.
The following video from Microsoft shows Microsoft’s vision for ChatGPT and Power BI integration.
The video above highlights a number of exciting features coming to Power BI.
- Creating draft report dashboards based on text descriptions.
- Answering questions about data
- A Copilot Panel to request updates and changes with text prompts
- Integration with other AI Features
We’re very excited to see all of the ways that ChatGPT will be used directly in Power BI and are even more encouraged knowing that this is the starting point. Microsoft aggressively adds new features monthly and likely has even more ways to use AI in Power BI coming in the future.
How to Create a Dashboard with Power BI Copilot
From the Power BI Service, powerbi.com you can select an existing dataset and ask Power BI to generate a report using AI. It will generate visuals, common KPIs, and can be a great starting point to get Power BI dashboard ideas.
To access this feature, go to PowerBI.com, sign in and click on + Create, Pick a Published Dataset, and the choose Auto-Create-Report after selecting some data.
The example below is from a very simple dataset that does not give Power BI a lot of information to work with, so the resulting dashboard is relatively simplistic.
For larger more advanced datasets, you have the ability to Pre-Select Data for AI dashboard creation. The feature limits the amount of data that Power BI feeds into the AI algorithms and will provide a more pointed dashboard to surface insights that you are most interested in.
After the report is generated you can either make changes directly in the Power BI service, or you can export it as a .pbix file to make manual adjustments in Power BI desktop.
As more users try this feature out and provide feedback to the AI model we imagine that reports will get better and better. We are also hoping that Microsoft will let organizations share reports within their domain or tenant so that dashboards will highlight information that is specifically important to an organization.
Using Power BI Copilot to Generate DAX Measures Automatically
Microsoft has already integrated ChatGPT into Power BI to generate suggested DAX formulas. The feature is available under Home, Quick Measure, Suggestions with Copilot. To use it, simply input the calculation that you are trying to achieve, and use the auto-complete guidance when available.
To get the most out of Suggestions with Copilot, here are a few tips that we have found to achieve better results.
- Be as specific as possible.
- Include field names and table names.
- Allow Copilot to Auto-Complete and suggest prompts as it detects field names in the data model.
- Go slower than you would expect. Power BI sends the prompts to the cloud and takes time to respond like ChatGPT does.
The suggested measures can be a big time saver when they work, but we did have some trouble getting it to output the right syntax or identify the right fields at times.
When a suggested measure is complete, you can click a button to add it into your report.
Note: It does help to have some familiarity with writing DAX formulas to tell whether the answer is accurate or not. For this reason, we still like using the ChatGPT interface directly because it can provide more context to why parts of a formula exist and you can ask follow-up questions.
If you don’t see suggestions with Copilot available when you click on Quick Measures, it must be enabled in Power BI Desktop and on the Power BI Service by your Power BI Admin.
Will ChatGPT Perform Data Integration and Data Preparation on its own?
If AI is already capable of writing SQL, DAX, and answering questions about data, what would prevent it from performing the steps itself? At this point in time, not much. OpenAI has announced ChatGPT Plugins that allow it to connect to outside systems.
It’s a revolutionary game changer for an already revolutionary system. ChatGPT plugins allow the chatbot to access information about the internet, and any other system that contains data. It massively increases the datasets available for it to access for answering questions.
If ChatGPT plugins can access information using an API, it’s safe to assume that it’s only a matter of time until it can access other parts of your business systems, like a data warehouse or directly access accounting data to create a dashboard and provide insights.
Currently people spend a massive amount of time pulling in data and performing data preparation, but it’s conceivable that an AI could do this much faster.
Knowing that Power BI already has over 200+ connectors, it’s not unlikely that Microsoft will start making this part of Copilot features in the future.
What are the Limitations of Chat GPT?
As excited as we are about a future of ChatGPT and Power BI integration, it’s important to note that there are some key limitations.
Chat GPT Struggles with Math
ChatGPT itself does not perform mathematical calculations very well on its own. It is an advance language chat bot and has mostly been trained on text data. However, with recent announcements of ChatGPT plugins, an early integration partner is Wolfram Alpha. Wolfram is an advanced computational mathematics platform that might be the missing like to combining text and numeric analytics in a dashboard.
Power BI Dashboards are Visual and Very End User Specific
Along with the other AI powered features that Microsoft 365 CoPilot launched with was a feature to describe a simple business application and have AI automatically generate an application for you. If you don’t like the layout you can have it generate another one until you find one that you like. Power Automate is less visual but works in a similar way with its Describe it to Design it Feature.
While reporting dashboards can be wildly different between industries and use cases there are often a lot of commonalities between the KPIs and reporting visuals that similar companies use. It’s not unrealistic to expect Microsoft to launch a Power BI feature to automatically suggest a draft dashboard as a starting point for developers.
Combine this technology with a deep understanding and integration of company source data, and the possibilities become limitless.
Chat GPT does Not Always Ask Clarifying Questions
It used to be that ChatGPT only provided answers. Now, when using the Advanced Data Analysis feature of ChatGPT it will provide answers, but will also ask you for a response or for clarifying information. When developing a Power BI dashboard, the ability for an AI to ask you for information would be extremely helpful.
Prompts that include questions like, which field are you wanting to place into a measure, or which date range should this be, would be incredibly helpful and avoid a lot of trial and error.
Will ChatGPT Eliminate the Need for Power BI Training?
Even though Chat GPT makes Power BI much easier to use and provides a quick easy resource for people to take full advantage of the platform’s features, it is still not as quick as a developer that has a lot of experience and formal training. We still recommend that people use Chat GPT as a resource on their learning journey but invest in learning as much about the platform as possible.
There are many great Power BI training courses and classes available, and Microsoft themselves publishes a lot of great material through the Microsoft Learn Website. Microsoft also makes certifications available, which you do have to pay to take the test, but the tests follow people’s personal accounts even if they change companies at some point in the future.
Here are some helpful posts that you may find useful as you learn more about the transformative nature of Chat GPT for business, finance, and accounting.