Copilot in Power Query in Power BI Service and Microsoft Fabric Dataflow Gen2

There has been a lot of hype recently about Generative AI and Copilot in Microsoft. Microsoft Fabric incorporates many of those features, and one of the areas it has been added to is the Dataflow Gen2 in Microsoft Fabric, or we can also call it Power Query in Power BI Service Dataflows. In this article and video, I will describe how the Copilot works with Data Factory Dataflow Gen2, its requirements, and its examples.


Copilot or ChatGPT

If you are new to Copilot, it is an AI-powered digital assistant integrated into many Microsoft tools and services. It has a more technical use than the general chatGPT, especially if you are dealing with Microsoft services such as Microsoft Fabric, Teams, Office 365, etc. You can check this article to learn more about ChatGPT and Copilot’s differences.

Copilot in Data Factory: Tenant Settings

To use the Copilot in Data Factory, you need to have a couple of options enabled in the Tenant settings, which the tenant administrator must enable. The first step is to go to the Admin portal from the Power BI service or Fabric portal.

In the Admin Portal, select Tenant Settings, and then search for OpenAI or Copilot. There will be two options below, which you need to enable;

  • Users can use a preview of Copilot and other features by Azure OpenAI
  • Data sent to Azure OpenAI can be processed outside your capacity’s geographic region, compliance boundary, or national cloud instance

The second option is only needed if your tenant is outside of the US or France.

Licensing: No Trials, Fabric or Premium Capacity Only

Unfortunately, at the time of writing this article, Copilot for Data Factory isn’t available for trial Fabric accounts. You need either Power BI Premium licensing (P1 or above) or Fabric Capacity licensing (F64 or above).

Getting started to use Copilot in Data Factory

At the time of writing this article, Copilot is very new. It is only available in Dataflow Gen2. So, the first step will be creating a Dataflow Gen2 artifact in Fabric.

If you want to learn more about what the Dataflow Gen2 is in Data Factory, use the links provided here.

After creating a Dataflow Gen2, You will see the Copilot in the Power Query Editor online’s home tab;

Let’s start by Get data, which will load the Get data window. I am using an OData source for this example;

This is the OData source link I am using;

Once connected to that data source, I select the Orders table and all related tables to it;

Copilot in Dataflow Gen2 Demo

Let’s now start asking questions from Copilot. I start with simple questions like the following;

I am in the Customers table, and here is the screenshot of the columns and data in that table;

I ask Copilot to keep three columns and remove everything else. My question is:

keep customerid, contact name and country

As you may have noticed, I did not use case-sensitive column names (column and table names are case-sensitive in Power Query), and I even used a space for the ContactName column. Despite that, Copilot managed to find the right columns and apply the transformation.

Not only can I ask the Copilot to do transformations, but I can also ask it to explain an existing query. I type explain, and it would explain all the steps so far.

This is a great option when you are given a Dataflow with queries including many transformations and want to determine what is done in the query quickly.

I then asked Copilot;

show count of customers in each country

The Copilot adds a transformation and does precisely what I asked for. Although It did not use the Group By transformations directly, it used it in a custom function.

You can see how useful Copilot finds and applies the correct transformation.

This also includes steps you cannot find easily using the graphical interface of Power Query Editor. For example, I can use something like the below to change the naming of all columns;

lowercase all column names

The usage is not limited to queries. You can use Copilot to write a Custom Function for you.

create a function that gives all order_details for an ordered

Then, Copilot builds a function with an input parameter for the Order ID and returns the table of order_details. As a result, it is clever, easy, and fast.

You can also use the icon beside the text box to choose what action you want the Copilot to do for you.

The power of AI combined with Power Query makes data transformation and preparation in Microsoft Fabric even easier.


The usage of Microsoft Copilot in the Fabric Data Factory is very new; it was announced recently and is still in the preview stage. However, even considering all of that, it can help make the life of a data analyst easier. You can ask questions and get an explanation about the transformation steps applied in the Power Query, or you can ask Copilot to transform the data for you and build some transformations you would otherwise have to spend more time implementing. The limitation is the licensing though, as Copilot uses extra compute power, it is not available for low F Skus or trial accounts. Copilot is still unavailable for the Data Pipeline, But I am sure that will also come soon.

Are you using Copilot? If so, let me know in the comments below. I’d like to hear about your experience with it.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog:

Leave a Reply