ChatGPT & Power BI

The introduction of ChatGPT at the end of 2022 led to an abundance of interest and developments. The chatbot now has more than 1.6 billion users, tech giants have invested billions, and a great deal has been written about its possibilities. This could also prove to be a revolutionary development when working with data, but version 3.5 isn't yet capable of creating fully autonomous BI reports. So the question right now is how you can use this chatbot to your advantage. After our article about ChatGPT as a copilot for Qlik Sense, this article focuses on Microsoft Power BI. Our Business Intelligence consultant Martin Memelink finds out for you.

Wouldn't it be handy to sometimes get a quick nudge in the right direction when working with Power BI, M-query, or DAX? This is the power of ChatGPT: for your specific question it can quickly give you new inspiration or a possible solution direction. There's also a lot more possible: you can also ask questions about data strategy, KPIs, data warehousing, and management. In this article we limit ourselves to the day-to-day practice of working with Power BI, where ChatGPT can help you be faster, easier, and thereby more effective.

Power BI features

Before we look at queries and data analysis, let's start with the Power BI UI. Sometimes you run into a practical problem where it can take a long time to find which support forum post proposed the right solution. Before you go and google this, you could also put the question to ChatGPT.

Let's, for example, ask about a well-known trick for sorting a date axis when the month name is displayed as text (so sorting chronologically instead of alphabetically). When we put this to ChatGPT, it points straight to the solution.

Although the panes mentioned have since been given different names, the "Sort direction" dropdown is indeed the way to put the months in the right order.

First aid with queries

Let's move on to look at queries. Quickly generating and analyzing computer code is one of ChatGPT's strong points. As an LLM (large language model), it's ideally suited to interpreting and generating language. For example, it can write an entire game in HTTP, CSS, and Java in seconds, but it's also familiar with DAX and M-query. You can ask ChatGPT to analyze or even generate code.

In the following example, I ask for an explanation of a DAX query that builds a hierarchy column in an employee table. Within a few seconds I get a clear explanation back, where I can read line by line what the DAX query does.

This explanation is complete, understandable, and correct, which is surprising since no context whatsoever was given about the source data. In the past, if you wanted to figure out what a query does, you might have started by googling or consulting DAX wikis, but even then you'd still need to make the translation yourself from what the functions do in general to the context of your specific situation. With ChatGPT you get instant, personalized explanations, specific to your query.

Tip: if the answer isn't quite what you expect yet, you can try phrasing the prompt differently. Right now, for example, the response is a code block with comment lines containing the explanation inserted. If I'd rather have the result in plain text, it's just a matter of adding that request to my prompt. ChatGPT will give different answers when the question is phrased differently.

Generating

Besides helping analyze queries, it's also possible to have ChatGPT generate them. This makes it a very powerful tool for quickly finding a particular solution direction. Where in the past you might have spent some time finding the right formula or syntax, you can now get it handed to you by ChatGPT in a few seconds.

For example, in the following query. Here too there's no need to provide context, since the chatbot makes its own assumptions and explains them.

This can also be done in DAX, see the following example.

This shows how important it is to be precise in how you phrase the question. This DAX expression is suitable for querying in DAX Studio, but can't be used in a measure or table in Power BI. A small adjustment gives a usable result.

Limitations

It's now tempting to think that from this point on we'll never have to write code ourselves again, but this is far from the truth. At first glance, the last query is well structured, but it doesn't work as requested. In the last line, for example, [Year] is used, which should have been YEAR([Date]). In addition, monthly quantities were requested, but the query creates a row per future day, with the last column showing the total revenue over the past year. So this code still needs to be reworked by hand into a working solution. Or could ChatGPT fix this itself?

This DAX query doesn't work at all, because the GENERATE function is used incorrectly. Attempts to have ChatGPT fix the error itself came to nothing in this case, and it still turned out to be necessary to have the required knowledge of DAX yourself to get the query working.

There are more limitations. GPT-3.5, for example, is notoriously bad at algebra, because it applies calculation rules incorrectly, and there are availability issues due to the large number of active users. There are also question marks around data security and privacy. If you're working on a complex problem, it may, for example, be necessary to send along rows of sample data or code to get a useful response. But when you do this, you're effectively handing over company data to OpenAI (the company behind ChatGPT), which may then store and process it.

Analyzing data

Ultimately, the goal of BI is to generate usable insights for questions from the business, so let's finally look at ChatGPT's possibilities for performing analysis. Power BI already has features for performing analysis with machine learning today: the KPI and Q&A visuals, but it will still take some time before Microsoft creates a full integration with the power of ChatGPT. With the announced partnership between Microsoft and OpenAI, it's reasonable to expect an integration with Power BI, similar to the recent copilot features in Power Apps and Azure.

Right now, it's already possible, with a workaround, to have ChatGPT analyze data in Power BI. This can be done by connecting to the ChatGPT API in Power Automate via the premium HTTP connector. ChatGPT can then, through the Power Apps visual, provide a response to selected data, for example to make a statement about anomalies. This solution is, however, still experimental and slow.[1]

The future

The coming months and years promise breakneck-speed developments in AI. The next version, GPT-4, is already available for a fee and reportedly already performs better on professional exams than the majority of human candidates.[2] But we're not yet at the point where ChatGPT can fully independently perform data analyses or build dashboards.

It is, however, a fantastic tool to use as a source of answers: for gathering inspiration on analysis questions or with DAX or M-query code. But given the current limitations, it's not possible to directly use generated code in everyday practice. Still, for those familiar with the limitations, using it remains a valuable asset for anyone who wants to work quickly and effectively in Power BI.

Written by Martin Memelink
Business Intelligence Consultant