Power BI reports are usually used to display numeric data. However it is reasonably common to have text data in a semantic model too and for this to be displayed on a report, and while visuals like the new text slicer allow you apply filters to the text, large amounts of text can still be difficult for end users to make sense of. Recently I was wondering if Power BI Copilot can be used to summarise text data (similar to what is possible with Excel Copilot) and it turns out that the answer is yes – with some limitations.

To test this I created a simple semantic model consisting of a single table containing comments from diners at various restaurants:

I then created a report with (1) a slicer on the Restaurant field, (2) a table visual to display the contents of the Feedback field for the selected restaurant, and (3) a Smart Narrative visual:

For the Smart Narrative visual I used the preview Copilot option, restricted it to look at just the table visual, and used the following prompt:

Under a title, in bold, of "Feedback Summary" provide a brief summary of the comments in the Feedback column in no more than two bullet points. Include some quotes from the Feedback column but do not just repeat what is there.

And guess what, it works:

[Notice that when you change the slicer selection you have to click the Refresh button on the Smart Narrative visual to get it to update]

BUT before you get too excited, there are a few things you need to understand:

• At the time of writing the flavour of Power BI Copilot used by the Smart Narrative visual can only summarise data that is visible on the report page – if it isn’t visible, even if it is present in the semantic model, then it can’t be summarised.
• It can only consider the first 30000 rows from any given table visual, and if it is pointed at multiple visuals then it can only consider 150000 rows in total.
• For each table row, it can only consider the first 100 characters of text – anything after that will be truncated. This will be increased to 500 characters of text soon but even then this could be a big limitation (for example the text “If you want good food you have to pay. That said, it’s very good value for a Michelin-starred restaurant.” is 105 characters long). You could work around it by splitting the text up into multiple rows where the length is less than the limit, but I don’t know what impact this would have on the quality of the results.
• For most scenarios cost would not be a worry (see here for my post on the cost of Copilot; it’s the number of words, not the number of characters, that is significant), but even with the character limits if you had a lot of users trying to analyse a table displaying thousands rows of text values that is filtered/sliced regularly then it could get expensive.

As a result of this you should only consider Power BI Copilot for summarising relatively short pieces of text; you should not store long documents in your semantic model and expect Power BI Copilot to be able to summarise them. If you want to show summaries of longer pieces of text your best bet is to create them upstream before the data reaches Power BI.

I have known for a while that setting the Data Category property on a column can influence the results you get from Power BI Copilot but I have struggled to find a scenario where it actually makes a noticeable difference. My sources on the Power BI Copilot team told me that Copilot does consider geographic categories (which are exposed in the Power BI Desktop UI) and date categories (which are available to set in tools like Tabular Editor but aren’t exposed in the Power BI Desktop UI; the full list of categories is documented here) but it’s only one factor among many. However, when writing my last blog post, I finally found a good example to show you all.

In my last blog post I showed how setting the Sort By Column property on the Launch column in this table:

…means that the following Copilot prompt:

Show Sales Amount by launch

…returns a column chart showing sales by launch year sorted by year in ascending order:

The fact that the column on the x axis is called just “Launch” and doesn’t mention anything about financial years, and that the contents of the column are text and not integers, means that Copilot doesn’t know that the contents of the column are financial years.

However, opening the model in Tabular Editor and setting the Data Category property on the Launch column to “Years”:

…means the same prompt now returns a line chart rather than a column chart:

[Interestingly this doesn’t happen if you set the Data Category to “FiscalYears”]

Although I’ve seen the behaviour change over the last few months, I think it’s the case that setting the Data Category property to one of the geography-related values can influence whether data is displayed using a map visual or not in some cases.

As a Power BI semantic model developer, one of the first things you will learn about is using the Sort By Column property to make sure the values in a column – for example days of the week or months of the year – are sorted correctly. Recently, though, I came across a situation where it was important to set it when you might not expect to have to in order to get good results from Copilot.

Consider the following simple semantic model:

It contains a single table containing sales values broken down by the financial year that products were launched in:

You might think that is isn’t necessary to set the Sort By Column property on the Launch column because, even though it is a text value, sorting it alphabetically gives you the sort order you would expect. Clicking on this column in the Data pane in Desktop to automatically create a visual gives a table with the financial years in the correct order (ie in ascending order by year):

But selecting the Sales Amount measure changes the visual used and, crucially, changes the sort order:

Note that, now, the bar chart is sorted in descending order by the Sales Amount measure, rather than by year.

This type of automatic behaviour is important because it’s something Copilot will rely on. So, for example, the prompt:

Show Sales Amount by product launch date

Returns the same bar chart as a response:

While a prompt to create a new report page does something similar:

Show Sales Amount by product launch date as a new page

Note how, on this report page, both the line chart and the column chart are ordered by Sales Amount again. This is almost certainly not what your end users want to see.

So how can you get Copilot to generate visuals with Launch on the x axis sorted in year order? The solution is to add a new column (in this case called Launch YYYY and hidden) and use it to set the Sort By Column property for the Launch column:

Having done this the two prompts above return visuals sorted by the Launch column and not by the Sales Amount measure. So:

Show Sales Amount by product launch date

…returns

…and the prompt:

Show Sales Amount by product launch date as a new page

…returns:

This is a very simple example – in the real world you should have a separate date dimension table and follow all the other best practices for data modelling. The point is, though, that setting the Sort By Column property on a column means that sort order will always be respected in visuals created by Copilot unless you explicitly override it in your prompt.

Continuing my occasional series of ways you can improve the results you get from Power BI Copilot by setting properties on your semantic model, in this post I’ll look at how setting the Summarize By property can have an impact.

Let’s say you have a semantic model that contains the following table of temperature readings at different English seaside resort towns over the summer:

Now let’s say you enter the following prompt in the Copilot pane on a blank report (this forces Copilot to query the semantic model rather than look for answers from visuals in the report):

show temperature by resort

Here’s the response:

As you can see, Power BI Copilot has summed up the temperatures for each resort and this does not make sense. You could argue that the prompt should be more precise and specify the aggregation method to use, but it’s unlikely that most end users would know to do this on their first attempt at prompting or even ever. You could also argue that it would be better to expose an explicit measure and hide the underlying Temperature (C) column in the model – indeed, this is what I would do – but the downside of this is that it may limit the type of analyses that an end user can do.

Let’s say that what you really wanted to see for this prompt is average temperature by resort, and as a semantic model developer you can ensure this happens by setting the Summarize By property on the Temperature (C) column to Average, either on the Column Tools tab in the ribbon or in the Model View:

After making this change, the same prompt now returns the average temperature for each resort:

As with some of the other properties such as Row Label and Key Column I’ve talked about in this series, the Summarize By property is not new but it’s a lot more important to set it when designing models for Copilot.

One last thing to note is that as well as specifying how a column should be aggregated, the Summarize By property can be used to specify that values in a column should never be aggregated at all. This is particularly useful for numeric columns that contain categorical data such as year or zip codes. You can do this by setting Summarize By to None in the model view:

…or, if you’re setting this property on the Column Tools tab in the ribbon, using the Don’t Summarize option:

I’m a big fan of Kurt Buhler‘s work (indeed we like him so much at Microsoft that we get him to help out with our docs) and I have a lot of respect for his opinion. A few weeks ago, in a thread on Twitter/X, he asked an important question about Power BI Copilot: why would someone use it? When I saw this I realised it was something I hadn’t thought about properly but which deserved a detailed answer. Now, after ruminating for a while and talking to a few people (including Kurt), I will put my case for why someone might want to use Power BI Copilot.

[Just to be clear: in this post I am not going to discuss the related question of whether Copilot works well and delivers useful results – even though I think it is pretty good already – because that’s not what Kurt is asking in the thead]

Which Copilot are we talking about here?

Before I carry I need to define which Copilot is being referred to in the question. There are a lot of Copilot experiences in Power BI: the one in DAX Query View, the one that can create new report pages, the one that allows you to summarise and ask questions about data that is visible on report pages, the one that allows you to ask questions about data that is in your semantic model but not necessarily visible in a report, as well as others. Since Kurt and I agree on the usefulness of Copilot in DAX Query View for intermediate developers let’s focus on the last three – which can be summed up as the ability to answer data questions in natural language – which are what most people think about when they think about Copilot for Power BI anyway.

A solution looking for a problem?

To answer the question of why someone would use Power BI Copilot to answer data questions we need to look at who is asking for this functionality. But is anyone actually asking for it? I’ve heard a few people say that Copilot for Power BI is a solution looking for a problem.

Now I can see why you would think that because let’s face it, we can all imagine some VP in Microsoft ordering every program manager to add a Copilot to their product whether they think it’s useful or not because that’s what the market demands. However if you work in the Microsoft field or for a partner then I’m sure you’ll know how much genuine interest there is in Copilot from Power BI customers and users. In my opinion Power BI Copilot is actually the latest attempt to respond to a very real request that I have heard for the whole of my career in BI and which can be summed up as follows:

I want to ask questions in English (or whatever my native language is) about my data rather than have to look at a report someone else has built or build one myself

I’m old enough to remember SQL Server 2000’s English Query feature, which was an attempt to solve the same problem over twenty years ago, and there have been numerous other products and features from Microsoft (including Power BI’s own Q&A) and other BI vendors that have tried to do the same thing in the past. I am not saying that any of them succeeded in their aim because I don’t think any of them did. What I am saying is that they were attempts to do the same thing that Power BI Copilot is trying to do and that therefore Power BI Copilot is not a solution looking for a problem, it’s actually the latest attempt to solve a very real and very old problem.

So what is the problem?

I have never felt the need to ask questions about my data in natural language because I have always found it easy to:

• Look at an existing report or dashboard and interpret what’s in it
• Build my own report/dashboard/queries to get answer to questions that are not in existing reports or dashboards

That’s going to be true of anyone working as a data professional or a business analyst. So if the question is “why would I use it?” then the answer is I wouldn’t.

However I believe the people who are asking for natural language querying of data are people who:

• Find it hard or impossible to interpret data provided in numeric or visual form in a report
• Find it hard or impossible to build new reports or write queries to get answers that aren’t in the reports they do have access to
• Have to ask other people to get the answers they need

It’s tempting to say “read what’s in the report” or “learn Power BI” or “learn SQL” when faced with these people and their requests. But, clearly, they keep asking for an alternative and we’re not going to change their minds.

Think back to maths class at school and it shouldn’t be a surprise that some people just don’t enjoy staring at pages of numbers and graphs or learning database query languages in the same way your or I do. What’s more dyscalculia or “maths dyslexia” means that interpreting data – however well designed your visualisations are – doesn’t come easily to a significant number of people. While the imperative to be “data driven” is widespread in every industry and profession today, not being good with data or not enjoying using it is not a barrier to success in most careers. Power BI Copilot provides a different, and perhaps more approachable, way of getting answers to data questions for many people.

It seems to me that a lot of the excitement for Copilot for Power BI comes from C-level people who love the challenge of management, who need data to make decisions, but who don’t like working with data directly or don’t have the time to do so. Today they need to ask their data questions to a business analyst and this introduces latency: asking someone else to do something is always more time consuming than doing it yourself. What I suspect these C-level folks want is to cut the human out of the loop, get answers to their questions more quickly, while keeping the experience of interacting with a human. Power BI Copilot aims to be that virtual business analyst.

Why is Copilot better or more effective than non-AI approaches?

If the “non-AI” approaches mentioned here involve “reading the report” or “learning Power BI” I believe I’ve already dealt with them above. What about non-AI approaches to natural language querying? Clearly LLMs are vastly better at “understanding” natural language requests than any technology ever seen before but there’s more to say here.

As I already mentioned, while the problem of asking data questions in natural language has been around for a long time, no-one has solved it yet. Why? Thinking back to my experiences with Power BI Q&A the reason is that to get good answers you have to ask good questions. Half the challenge of learning programming or any database query language is understanding what you want to do before you even learn how to express that request in code. To get good results from Q&A you have to construct the English-language equivalent of a DAX or SQL query and that’s why it, like all its predecessors, failed. Users are bad at constructing clear, unambiguous questions.

One way that Copilot is an improvement on Q&A is that it can interpret a wider range of questions. For example, I have a demo with a table of books at their year of publication; I was pleasantly surprised that when I asked “Show me all 19th century literature” it gave me all the books published between 1800 and 1899. Q&A would never have been able to do this.

More importantly, Copilot is getting better at asking for clarifications. Going back to the previous question, when a C-level person asks their business analyst for the answer to a data question it will be something vague like “what was growth like for widgets in the UK last year?”. Part of the job of the business analyst is to know exactly what exact product “widgets” refers to and which particular geographies make up the UK without having to ask. Those are things that can be defined in a good Power BI semantic model. However part of the job of the business analyst is to ask follow-up questions like “do you mean month-on-month growth or year-on-year growth?” to clarify the original request and that’s exactly what you’ll see Copilot doing more of in the future.

The ability of LLMs to conduct conversations in near human-quality natural language and use this ability to turn a vague request into a data question that can be answered is, I think, why AI approaches to the problem we’re talking about are better than non-AI approaches.

Conclusion

I hope I have made a good argument for there being people who want to use Power BI Copilot and the reason why is that they can’t, don’t like or don’t want to learn to use existing methods of interacting with data. Will it succeed where so many others have failed? Let’s see where we are in a year or so’s time but I’m confident that the remarkable advances in generative AI mean that it will.