Exploring the Potential and Pitfalls of Microsoft Fabric Copilot: A Practical Analysis

Introduction

Microsoft Copilot is an AI-powered assistant that helps you in completing tasks within the Microsoft Fabric environment. While it might seem a bit abstract at first, don't worry. This insight will illuminate the role of Copilot, and when and how to utilize the tool, all through practical examples. For each applicable tool, we'll showcase an example of Copilot in action, followed by a swift evaluation of its pros and cons. Below, you'll find a comprehensive list of all the tools where Copilot can be employed and the reasons why. Copilot for Power BI has generally been available since June 3rd ’24.

 

Fabric component

Users

Why use Copilot?

Image
Data Factory Logo

Data Factory

-> Dataflow Gen 2

Data Engineers

  • Code generation
  • Code explanation
  • Data exploration
Image
Synapse Data Engineering

Data Engineering

-> Lakehouse
(in notebooks)

Data Engineers

  • Data exploration
  • Code generation
  • Code explanation
Image
PowerBI picto

Power BI

-> Service and Desktop

Data Analysts

  • Create reports automatically 
  • Generate summaries of reports 
  • Generate synonyms for better Q&A capabilities
  • Generate measure description for documentation
  • Write DAX queries
  • Ask questions about the semantic model

In this insight, we will focus on low-code/no-code. As such, we will only address the parts that have an orange font. We will showcase all of these functions by going through one large use case. Therefore, we make use of the Adventure Works dataset (in Microsoft Excel format).

Before we get started, it is important to note that you need Fabric Capacity to be able to use Copilot. For more information about enabling Copilot, we refer to this link.

Copilot for Data Factory

First, we will ingest the Adventure Works Excel file by using Dataflow Gen2. This Excel file contains some the tables of the Adventure Works dataset related to sales.

Tables of the Adventure Works Excel file:

  • Fact Sales
  • Sales Rep
  • Sales Order
  • Sales Territory
  • Reseller
  • Date
  • Product
  • Customer

You'll notice that the tables already constitute a well-structured data model.

Code generation in Dataflow Gen2

As a first example, we will do some data transformations by asking questions to Copilot within the Dataflow Gen2. In order to do so, you simply click on the copilot button in the ‘home’ tab in the upper ribbon.

Image
Enable copilot in gen2 dataflow

If you don’t see this button, make sure that you use a Gen2 dataflow (not a Gen1) and that the tenant settings are correct (see this link for more info).

A window on the right is appearing (Window1). In this window, we asked to create a new column in the fact table. Without hesitance, Copilot created the binary column based on another column in this table. 

Image
Copilot in gen2 - add binary column

A very big advantage of making use of Copilot is that you can still see the code that is generated (see Window 2). That way, if Copilot interpreted something wrong, you can make changes to the code. It’s possible and will happen, that Copilot is not giving you the outcome you want. Nevertheless, the code it generates can be a great starting point.

In the next example, I’m asking Copilot to put the customer names in capital letters. Again, it implemented my request perfectly.

Image
Copilot in gen2 - add capital letters to customer

In another example, I’m asking a difficult (and less meaningful 😉) question (“Change the order of the product table, order the products based on the color of the products (from z to a)”). Here, Copilot is again giving the correct code! It’s quite impressive that it all works out how we want it. 

Image
Copilot in gen2 - sort columns

Nevertheless, if the outcome is not what you want you can easily delete it by clicking on the ‘undo’ button in the Copilot window or by deleting the step in the Query settings pane (next to the Copilot window). As said before, you can also make changes to the code that Copilot generated by changing the code in the coding window above the table.

In the following example, I ask to duplicate a table and call it ‘Supplier’. Copilot can duplicate the correct table but is unable to name the new table correctly. I have to rename the table myself.

Image
Copilot in gen2 - duplicate table

Wrap-up – Code Generation in Dataflow Gen2

Pro's Con's

Very accessible and easy to use

It’s not flawless; always double-check the generated outcome

It works very quickly, one loses little time

 

Easy and fast to delete or change the generated code

 
Overall WINNER

Code explanation in Dataflow Gen2

In the same Copilot window as used before, you can also ask to explain the code. In the first example, we asked how it generated the new column in the Fact Sales. The following answer is given.

Image
Copilot in gen2 - code explanation1

The explanation is good but minimal. We can also see that the new column, generated in “2.1 code generation in Dataflow Gen2”, does not yet have a good name.

When we ask how Copilot sorted the product table, Copilot is failing.

Image
Copilot in gen2 - code explanation2

It again fails to explain how the customer column was put into capital letters. When I ask to put it back in lowercase, it fails again.

Image
	Copilot in gen2 - code explanation3

When I close the Dataflow, open it again and ask to put the column to lowercase, it works again. Nevertheless, the explanation of how the code is generated is rather limited.

Image
Copilot in gen2 - code explanation4

As a last example, I ask to explain the last query we created in 2.1 (related to the number of Resellers per city). The outcome is good, but not detailed. 

Image
Copilot in gen2 - code explanation5

In order to improve the explanation of the queries, you can use the ‘describe this query’ button. An example, of the outcome, can be found below. Furthermore, it speeds up the process.

Image
Copilot in gen2 - code explanation6

Wrap-up – Code Explanation in Dataflow Gen2

Pro's Con's

Very accessible and easy to use

It’s not flawless; always double-check the generated outcome

It works very quickly, one loses little time

The outcome is rather poor and limited

Overall LOSER

Data exploration in Dataflow Gen2

It may be controversial to do data exploration in Dataflow Gen2. Nevertheless, we will show you some interesting examples as we find it very efficient.

We can ask a data exploration question to Copilot; ‘Show the number of resellers by city’. Copilot generated a new query with the outcome we expected. After you have done the analysis, you can easily delete the query.

Image
Copilot in gen2 - code explanation7

The result is stunning! It shows that Copilot can also be used as a data exploration tool because after we interpret the results, we can easily delete the query.

Wrap-up – Data exploration in Dataflow Gen2

Pro's Con's

Very accessible and easy to use

It’s not flawless; always double-check the generated outcome

It works very quickly, one loses little time

It’s creating new queries, so don’t forget to delete them once the analysis is done

The outcome is impressive

 
Overall WINNER

 

Copilot for Power BI

After the data cleaning & transforming is done in the Dataflow Gen2, every table is connected to the Lakehouse of the workspace. A semantic model is automatically created. In the following example, we will work on this semantic model.

Write DAX queries

The first thing to do is create some basic measures. Therefore, we will use Copilot in the DAX Query View of the Power BI Desktop. Let’s start very basic and build up the difficulty level. First up, I asked to make a measure that calculates the total revenue. Copilot is giving the correct answer. I can choose to ‘keep it’, ‘delete it’ or ‘retry it’. One level up; let’s ask for the revenue in May 2019. It again generated the correct measure. Furthermore, the name of the measure is good.

Image
Dax query view 1

When we ask for the sales of May 2019 as a percentage of the sales over all years, we get an error in the DAX Query Viewer. This is because Copilot used the wrong year column (the fiscal year instead of the normal year). When We ask Copilot to not use the fiscal year, it gives the measure we want.

Image
Dax query view 2

Lastly, let’s ask for a difficult measure. I want to know the number of white products sold in 2017 in North America as a percentage of the total number of products sold in 2017.

Image
Dax query view 3

First, there is no correct answer but the DAX code is already looking great. Again, Copilot used the fiscal year instead of the normal year. When I mention this, Copilot gives the correct answer.

Image
Dax query view 4

This is truly impressive! Note that I wrote, in my prompt, ‘North America’ and ‘white’. No problem for Copilot, it knows that he needs to convert it to ‘North America’ and ‘White’. This feature is super impressive!

Nevertheless, the author likes to add on that same note that being able to write DAX is still a big advantage. Again, Copilot is an excellent starting point, but it’s not magic! It’s very handy if you can debug the code Copilot generated. Furthermore, always validate the measure Copilot created!

Wrap-up – DAX generation in Power BI

Pro's Con's

Very accessible and easy to use

It’s not flawless; always double-check the generated outcome

It works very quickly, one loses little time

DAX knowledge is still very highly recommended

Copilot can even generate complex DAX

Users still have to validate the measure

Copilot takes feedback into account and reevaluates as expected

 
Overall WINNER

Write measure descriptions for documentation

In order to use this feature, don’t forget to first turn it on in the preview features window of the settings in the Power BI Desktop. Again, we will start with a very simple example. We want to have documentation about the ‘Total Turnover (in EUR)’ measure. In the Model view of the Power BI Desktop, we add a description in by clicking on ‘Create with Copilot’.

Image
Measure description 1

A window appears which is showing a suggestion for a description. It’s nice that we can choose between three options; ‘Try again’, ‘Keep it’ and ‘Discard’. The description is what we expect so we keep it.

Image
Measure description 2

In the description box of the properties window, we can now see the Copilot-generated description.

Image
Measure description 3

For the White Product Percentage measure, Copilot generates the following description: ‘The 'WhiteProductPercentage' measure calculates the percentage of products that are white in color. It does this by dividing the distinct count of white products by the total distinct count of all products.’. This is correct. After trying a few more complex examples, we conclude that Copilot truly generates nice descriptions. However, a major drawback is that we cannot generate descriptions for multiple measures at once.

Wrap-up – Generating measure descriptions in Power BI

Pro's Con's

Very accessible and easy to use

It’s not flawless; always double-check the generated outcome

It works very quickly, one loses little time

We cannot generate descriptions for multiple measures at once

We can choose to keep, retry or delete the generated description

 

Nice outcome, also for more complex measures

 
Overall WINNER

Create reports automatically

When we open an empty Power BI report in the Power BI service (connected to the right semantic model), we first click on the Copilot button.

Image
Copilot in PBI 1

The Copilot window on the right is appearing. After clicking on ‘suggest content for this report’, A list of examples for a report page is given.

Image
Copilot in PBI 2

Wow... Copilot really knows what the data is about?! Exceptional! For my first page, I click on ‘Sales Performance Analysis’ and... BOOOM; this nice report page is generated within a minute!

Image
Copilot in PBI 3

For sure, this is not a final report. But, this is super handy to do some data exploration or to use some of the visuals, get inspiration and save yourself some time. The titles of the visuals are not yet how we would have hoped; let’s hope Microsoft will tackle this quickly. We have a filter for Sales Rep and Product.  However, I’m lacking a Date filter.

For the second page, I’m pressing the ‘Customer Buying Patterns’ button.

Image
Copilot in PBI 4

The layout is nice but we see that two of the visuals have a constant line. This is because Copilot used two dimensions instead of a dimension and a fact. This is unfortunate to see. Especially since our fact table is named 'Fact Sales'. Again, still lots of work to get the visuals right; add correct titles, make the axes readable etc.

You can also write your own text (or start from a suggest and change it) in order to get a new report page. We typed in ‘Create a page to examine the profitability of each product considering the colour of the product’ and got the following.

Image
Copilot in PBI 5

We tried it several times, with different questions, reopening the report etc. and never got a good answer. Copilot is definitely lacking when the users choose the topic themselves.

In the Power BI Desktop, this feature is working in exactly the same manner.

Wrap-up – Report Generation in Power BI

Pro's Con's

Very accessible and easy to use

It’s not flawless; always double-check the generated outcome

It works very quickly, one loses little time

Titles, axes, tooltips etc. have bad naming

It can inspire you on how to visualize certain data 

Copilot is lacking when the user chooses the topics

It can also work as a data exploration feature

Layout is always the same and not in line with the graphical design of your organisation

Overall Winner as starting point for creating reports

Overall Loser as a final report generator

Summarizes data

This section is about explaining an existing report page or visual to the report builder and/or consumer. To explain this to you properly, we make use of the element61 Out-Of-The-Box sales report with test data.

Image
Copilot in PBI 6

When you have a Fabric Capacity and click on the visual ‘Narrative’, the window below is appearing.

Image
Copilot in PBI 7 - narrative

My first prompt, by clicking through, is the following.

Image
Copilot in PBI 8

The summary I get, see picture below, is simply stunning!

Image
Copilot in PBI 9 - summary

The small numbers you see (in the picture above indicated with an orange window), is the source visual of the data. It’s a strong approach that Microsoft is taking by adding these source fields. The user can now more easily validate the statements.

Another strength of this feature is that the user can choose which visuals and pages to include in the summary. It can be simply changed by clicking on ‘reference visuals’ in the Copilot chat.

Image
Copilot in PBI 10 - pages to include

A bulleted list of insights can also be asked with one click, this is working the same way. The last option is to ask Copilot to give important questions ‘leaders’ will ask, with responding questions.

Image
Copilot in PBI 11

The questions make sense but are rather easy and straightforward. The answers are good, with some exceptions (e.g. no answer on the fourth question).

The three options (create a summary, create a summary in bullet points, create a Q&A) are all performing worse when multiple pages are selected.

Lastly, it can be handy to have text instead of interactive visualisations but never forget; “A picture says more than a thousand words”.

Wrap-up – Summarizing data in Power BI

Pro's Con's

Very accessible and easy to use

It’s not flawless; always double-check the generated outcome

It works very quickly, one loses little time

It behaves poorly when we write extras in the chat

We can see which visual(s) the text is based on

It’s not working when we add too many pages and/or visuals

The user can select which page and/or which visual he wants to include in the summary

What is the advantage of having a long text instead of easily interpretable visuals?

It works, but does this have a big added value?

Generate synonyms for better Q&A

Remember the time when Q&A came out as a feature a few years ago? Indeed, the feature that no one used because it was immature. Let’s have a look if the Q&A is improved and if it's worth to add synonyms. Before this feature can be used, make sure to apply the below settings on the semantic model online.

Image
Q&A 1

After that, open a report and create a Q&A visual. Next, click on the gear. In the synonym tab, you can add all the synonyms you want. Copilot is making suggestions. The suggestions look pretty good! You can easily add these suggestions to the synonym list.

Image
Q&A 2

We can add suggestions for every column of every table.

Another nice tab in the Q&A setup is the ‘Review questions’. Here, you can see the questions users asked in the last 28 days. Based on these questions, you can add more precise synonyms.

Let’s now try out Q&A. Copilot is not able to answer the first, very easy, question.

Image
Q&A 3

The answer Copilot is giving below is also not optimal at all.

Image
Q&A 4

After trying many other questions, we can conclude that the Q&A is still not working as it should. Adding synonyms to this is not helping.

Wrap-up – adding Synonyms in Q&A of Power BI

Pro's Con's

The suggestions of Copilot are quite good

Q&A visual is still not working as we expect

We can see the history of questions asked and make synonyms accordingly

 

Overall LOSER

 

Conclusion

In our journey of exploration, it's paramount to acknowledge the nuances and potential drawbacks associated with Copilot. Before delving into the depths of our discussion, it's prudent to first shed light on the inherent disadvantages and crucial considerations that demand our attention. First of all, keep in mind that Copilot for Fabric is still in preview; there might be things that don’t work as expected. On the other hand, as the tool is still in preview it means that the capabilities of Copilot will further improve. Furthermore, it is very important to understand that Copilot (or generative AI in general) is not as creative as humans are. The author would like to emphasise that excessive use of AI tools like Copilot and GPT might stifle your creative thinking. While these tools are incredibly useful, relying on them too heavily could impede your ability to think innovatively and solve problems creatively. Remember to balance technology with your own cognitive abilities to maintain a healthy creative mindset. Furthermore, we noticed that Copilot works best in the English language. Lastly, always keep in mind that Copilot response can include inaccurate, wrong content. Review of outputs should always be done!

That being said, the author is truly surprised by the power of Copilot, especially when it comes to creating complex code! Seasoned business intelligence professionals can use Copilot to scale and create stunning reports faster. Less skilled users can use Copilot to explore their data, create reports, and answer their questions without burdening their data teams. Below, you can find the final summary of every low-code/no-code feature of Copilot. Overall, Copilot is a winner!

Feature of Copilot Winner or Loser?

Code Generation in Dataflow Gen2

Winner

Code Explanation in Dataflow Gen2

Loser

Data Exploration in Dataflow Gen2

Winner

Dax Generation in Power BI

Winner

Measure Description in Power BI

Winner

Report Generation in Power BI

Winner as starting point
Loser as final report

Summarizing data in Power BI

Neutral

Synonyms for Q&A in Power BI

Loser

On top of that, we state that in the future, Copilot will become even stronger! We are already looking forward, with high expectations, to what the future of Copilot will bring.

 

Sources