If you’re just getting started with Power BI, we put together a helpful overview of the platform and explain how finance and accounting departments can leverage it.
What is Chat GPT?
Chat GPT is a Natural Language AI that’s caught the internet by storm. People are freaking out about the possibilities and promise that it holds. We’ll dive into how you can leverage it today in your work with Power BI as well as look at some of the future possibilities.
Chat GPT (Generative Pre-trained Transformer) is a chat bot developed by the Open AI organization. It’s been trained on massive datasets and allows you to ask questions in natural language and receive a response.
The questions and responses are much more detailed and useful than you would get from asking a digital assistant like Siri or Alexa. A major differentiator of Chat GPT is that it was trained using technical datasets and includes the ability to write Power BI DAX formulas!
Sign up and check it out here:
ChatGPT: Optimizing Language Models for Dialogue (openai.com)
Using Chat GPT to Write DAX Formulas
There are two ways that Power BI can help you write DAX formulas. You can either prompt it with enough information, including actual field names from your data model and have it write the formulas for you. The example below is using ChatGPT-4, the latest version of ChatGPT from OpenAI. We fed it the names of the columns and asked it to write a formula for us. It came back with options for DAX.
The second way you can utilize ChatGPT is by using it as a reference guide. It’s not as fast up front as having ChatGPT generate a formula for you, but it will help you build dashboards faster in the future. The fastest way to use ChatGPT is to not have to use it all.
Ever wonder when to use CALCULATE, FILTER along to calculated various date ranges like QTD, MTD, and YTD? ChatGPT can explain the syntax to you and allows you to ask follow-up questions to understand the reason behind the syntax of the formula.
Using ChatGPT to Write Power Query M
If you go to the Power Query editor in Power BI, you can click on the Advanced Editor. It’s one of the most powerful components of Power BI. When you create steps to transform your data, the steps are saved behind the scenes in text format. The text format of these steps can be accessed by using the Advanced Editor.
We find that making some modifications are relatively easy like updating a data source, but other times can be more difficult. For example, if you want to combine two sets of transformations or transfer half of your data prep to a new dashboard without having to re-create it you can often copy and paste the code from one Power BI report to another.
If you run into an error though, ChatGPT can help you troubleshoot it. A lot of times we find ourselves missing a comma, bracket or other technical part of the code. By copying it into ChatGPT and prompting it to find the error it will recommend a fix for you.
Another capability of ChatGPT is to generate data prep steps from scratch. ChatGPT is fully capable of creating Power Query code, but the more difficult might be describing the steps that you want it to perform.
Using Chat GPT to write SQL Queries
With being trained on such a large dataset, you can use Chat GPT for a number of tasks to help you out. If you’re used to doing all of your data preparation in Power Query, you may not be as familiar with directly writing SQL queries.
Using SQL as a data source is our preferred method for creating Power BI dashboards. Many clients will base reporting dashboards off of Excel files or reports that they can run out of their accounting system. This isn’t a bad option necessarily, and sometimes it is the only option.
However, the most powerful way to use Power BI is when it’s connected to a live data source. Need a report that compares hourly sales against the number of overtime hours worked? It’s a great repot to have whenever the Excel data sources are updated. It becomes exponentially more powerful when you can direct query the SQL database that your accounting software is based on, combine the datasets and make it available in real time to your management team.
Going beyond making it available, Power BI and Power Automate can be combined to send people e-mail or text message notifications when a KPI target is violated or achieved.
If you’re not familiar with writing SQL queries or setting up live data connections to a database, ChatGPT can guide you through the entire process. Begin by explaining the accounting software or system you’re trying to connect to and ask how to establish an ODBC connection. This is the connection type that most SQL sources connect with. ChatGPT will give detailed instructions of how to do it.
Note: SQL data sources can be hosted on premises and may require additional gateway setup
After using ChatGPT to help you setup a SQL connection, you can ask it to generate SQL queries. It’s a great reference to explain how SQL syntax works, but if you feed it different SQL table names and field names it can create simple queries without you even having to know how it works. The generated SQL queries can be copied and put into the Power BI connection editor.
SQL Queries make for a great starting point to a Power BI Dataset that is setup to Direct Query against a SQL database. Sometimes these queries can take a very long time to run. If this happens to you, try asking ChatGPT to optimize your SQL query. It could also make sense to move from performing table merges or joins within Power Query and pushing it back to the SQL data base.
You might also look into Query Folding in Power BI which translates some of the Power Query M language back into SQL and pushes it back into the native SQL databases. Not all databases support this feature, and we would still recommend writing native SQL code for optimal performance.
Use Chat GPT to Integrate Power Automate and Power BI
Power BI and Power Automate work better together. If you’re unfamiliar with all of the exciting things that Power Automate can do, it’s a low code process automation platform from Microsoft that allows you to connect hundreds of different systems together. Once systems are connected it can perform simple to complex tasks.
One example of Power Automate’s usefulness is having it save an Excel spreadsheet to SharePoint every time you receive an e-mail attachment with a specific subject line. A use case might be scheduling a weekly e-mail, picking it up with Power Automate, saving it to SharePoint and then connecting Power BI to that SharePoint folder. It’s a way to fully automate a reporting process when you’re unable to directly connect to a system with a SQL connection.
In our example below, we asked ChatGPT how to setup Power Automate to automatically update a Power BI dataset. It’s a great way to dynamically update Power BI data and not be subject to specific update times.
Microsoft has been hard at work integrating natural language artificial intellgence into the entire Power Platform after investing over $10 billion into OpenAI, the developer of ChatGPT. One of the enhancements is called Describe it to Design it, a way to generate process automation flows by describing a process.
ChatGPT Will Answer Questions about Data
Power BI already has a feature that allows you to ask questions about data. However, the existing feature relies on developers creating a set of hypothetical questions and creating answers that end users will use. There is a feedback mechanism to let the developer know what questions are being asked and they can update the settings behind the scenes to fix future instances. Software like Siri, Google Assistant and Amazon Alexa currently work in a similar way.
With Microsoft’s investment in OpenAI, they have been aggressively integrating the chatbot into all of their products. Microsoft hasn’t announced this feature specifically, but they have shown a video of questions being asked and answered in Microsoft Excel. If it is possible in Excel, then it will only be a matter of time until a similar solution is available in Power BI.
One hesitation we have is that ChatGPT is a large language model and trained on text data. In our testing we have found that it is not very good at math. Even Sam Altman, the CEO of OpenAI recently said something similar. For ChatGPT to be useful in answering questions about data they may need to implement some type of AI Model Fusion where multiple AI models are deployed to convert natural language into numeric insights.
Could Chat GPT Perform Data Integration and Data Preparation?
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.
Chat GPT is Going to be Integrated with Power BI
Currently available as an Experimental Preview Feature of Power BI Desktop, Quick Measure Suggestions will allow you to describe a DAX formula and Power BI will generate suggestions for you.
You can enable it in the Options Menu, Preview Features section of Power BI Desktop. To get it working, you may have to enable live Q&A on the Power BI dataset, through the Power BI Service. This feature is not available in all regions. You may have to turn on Natural Language Processing under the admin settings of the Power BI Service for it to work.
Microsoft has published some more in-depth instructions on setting this preview feature up along with some troubleshooting tips.
Quick measure suggestions (experimental preview) – Power BI | Microsoft Learn
This is what the Quick Measure Suggestion panel currently looks like. Power BI issues monthly updates to Power BI Desktop and the Quick Measures tab has been quickly evolving. This seems like a giant leap in capabilities but may be an intermediary between full AI dashboard creation.
Microsoft Copilot 365 for the Power Platform
Microsoft has announced the integration of Chat GPT version 4 with their Microsoft Office Suite of programs, along with the Microsoft Power Platform. The name of these integrated services are Microsoft 365 Copilot.
The announced new capabilities are very impressive. At this time no specific details have been announced for Power BI, but we can gain some insights into how Microsoft is integrated Chat GPT into Microsoft Excel.
For example, users will be able to use a Chat GPT style prompt to ask questions about their data. They will also be able to ask Excel to make calculations, create charts and graphs. It’s likely that similar capabilities will be integrated into Power BI.
A key differentiator for Microsoft Copilot is that it can be trained on your own dataset by integrating with SharePoint, One Drive, and other business data sources. They have not explained how data governance will be handled, but having a chat bot trained on your company’s data will give it a lot of context to provide insights.
A big challenge at the moment is context. ChatGPT is a contextually aware chat bot, it’s one of its biggest differentiators. If you loaded a large set of numbers into an AI with no context it would not know what to do with it or make any meaningful insights. By gaining general access to a wide variety of business data, it will provide the context needed to provide accurate answers to analytical questions.
Introducing Microsoft 365 Copilot – your copilot for work – The Official Microsoft Blog
Would Chat GPT be useful for Power Automate Desktop RPA?
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 can Synthesize Information but Cannot ask Clarifying Questions
This brings us to our final point. We don’t think you have to worry about your job being completely replaced just yet. At the end of the day, people want to interact with people and people want to communicate with people.
Sometimes it’s important to not just answer questions when asked, but to ask someone why they are asking. There’s a level of intuition that may help you avoid or discover a greater problem.
It can also be incredibly difficult to even explain what a problem is or how to potentially solve it without having any context or background. For now, it’s a fantastic tool and the future is bright.
It will be fun to see how Chat GPT evolves and how quickly functionality will be integrated in with Power BI and the Microsoft ecosystem.
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.
Related Posts
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.