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.
Video
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;
https://services.odata.org/Northwind/Northwind.svc/
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.
Summary
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 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: https://radacad.com/blog.