ChatGPT & Power BI

The introduction of ChatGPT at the end of 2022 has sparked a wave of interest and developments. The chatbot now has more than 1.6 billion users, tech giants have invested billions, and a lot has been written about its possibilities. It could prove to be a revolutionary development for working with data too, but version 3.5 isn't yet capable of creating BI reports fully autonomously. So the question right now is how you can use this chatbot to your advantage. Following our article on 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 get a quick nudge in the right direction now and then when working with Power BI, M-query or DAX? That's the power of ChatGPT: it can quickly give you fresh inspiration or a possible solution for your specific question. And there's much more it can do: 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 work faster, more easily and therefore more effectively.

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 the support forum post that proposes the right solution. Before you start googling, you could also put the question to ChatGPT.

For example, let's 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 renamed, the “Sort direction” dropdown is indeed the way to put the months in the right order.

First aid for querying

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

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

This explanation is complete, understandable and correct, which is surprising given that no context at all was provided about the source data. In the past, if you wanted to find out what a query does, you might have started googling or consulting DAX wikis, but you would still always need to translate what the functions used do in general terms to the context of your specific situation yourself. With ChatGPT, you get a personalised explanation straight away, specific to your query.

Tip: if the answer isn't quite what you expected, try rephrasing the prompt. Here, for example, the response is a code block with explanatory comment lines 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 you analyse queries, ChatGPT can also generate them. This makes it a very powerful tool for quickly finding a possible solution. Where you might previously have spent some time finding the right formula or syntax, ChatGPT can hand it to you in a few seconds.

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

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

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 it can't be used in a measure or table in Power BI. A small adjustment produces a usable result.

Limitations

It's tempting to think that from now on we'll never have to write code ourselves again, but that is far from reality. 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 where it should have been YEAR([Date]). In addition, monthly quantities were requested, but the query creates a row for each future day with the total revenue over the past year in the last column. So you still need to rework this code yourself into a working solution. Or could ChatGPT fix it?

This DAX query doesn't work at all, because the GENERATE function is used incorrectly. In this case, attempts to get ChatGPT to fix the error itself come to nothing, and it turns out you still need a good 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 arithmetic 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, for instance, you may need to send along rows of sample data or code to get a useful response. But when you do this, you are effectively handing company data to OpenAI (the company behind ChatGPT), which is then allowed to store and process it.

Analysing data

Ultimately, the goal of BI is to generate actionable insights for business questions, so finally let's look at the possibilities for ChatGPT to perform analyses. Power BI already has features for performing analyses with machine learning: the KPI and Q&A visuals, but we'll have to wait a little longer for Microsoft to build a full integration with the power of ChatGPT. Given the announced partnership between Microsoft and OpenAI, it seems likely that we can expect an integration with Power BI, like the recent copilot features in Power Apps and Azure.

Right now, it is already possible to have ChatGPT analyse data in Power BI via a workaround. You can do this by connecting to the ChatGPT API in Power Automate using the premium HTTP connector. ChatGPT can then respond to selected data through the Power Apps visual, for example to comment on anomalies. However, this solution is still experimental and slow.[1]

The future

Developments in AI promise to move at lightning speed in the coming months and years. 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 perform data analyses or build dashboards completely independently.

It is, however, a fantastic tool to use as a sounding board: for inspiration on analysis questions or for DAX or M-query code. Given its current limitations, though, generated code can't be used directly in day-to-day practice. Still, for those who are aware of the limitations, using it is an asset for anyone who wants to work quickly and effectively in Power BI.