This part may be a bit of a stretch. You might say that Dataflows Gen 2 is not part of Power BI, but another experience within Microsoft Fabric – Data Factory. Of course, you are absolutely right. Unfortunately, for now this is the only Power Query Experience, that is enhanced with the Copilot. It is not (or not yet) available for Dataflows Gen 1 or within Power BI Desktop. In one of my previous articles: Excuse me, which way to Fabric? I was describing Dataflows Gen 2 as a first natural step, that Power BI Developer may take towards MS Fabric.
As a reminder, this is a part of Copilot series:
- Power BI Copilot – Introduction
- Copilot in Power BI Desktop
- Copilot in Power BI Service
- Dataflows Gen 2 – current article
- Report summary – next article
What are we going to cover?
- Generate new transformation steps for an existing query.
- Provide a summary of the query and the applied steps.
- Generate a new query that may include sample data or a reference to an existing query.
I will basically explore these options together with you, and I can’t wait to see the end result. This is how it looks in the tool:
As you can see, we have here three mentioned capabilities, but for blank dataflow, it suggests going with Get Data first. This is where we will start this session.
Get Data
Transform Data
#"Custom 1" = let
Source = Custom,
#"Transformed Date" = Table.TransformColumns(
Source,
{
{"ValidFrom", each Date.ToText(_, "yyyyMM"), type text},
{"ValidTo", each Date.ToText(_, "yyyyMM"), type text}
}
)
in
#"Transformed Date"Two things happened here. Copilot found my date type columns and applied efficient formula to change the format of displayed value. We could maybe expect to set the type to integer, but it would require additional transformation to change the text to number. But again, let’s not be picky. I am really surprised with the results so far. Another transformation I would like to apply is related to Location column:
We have here coordinates, that we could use on Map visual in Power BI, but this format is useless for further analysis. What I would like to do here is to extract the numbers from bracket and split it into two columns using space a delimiter. Looking at the range of values, first number should be longitude, second number is the latitude. Of course, a lot depends on how precise the prompt is. Maybe I expected too much or didn’t describe my need very well, but this step failed. Here is the snapshot of my prompt:
The problem is that formula didn’t extract the numbers from brackets first, so first “space” in that column is found after “POINT”, so, we end up with values that can’t be converted to numbers:
Let’s to break it down in two steps. I will ask first to extract the values from the brackets and (in second step) to split the column in two.
As you can see, firs step worked as expected. I will now try to split the columns into Longitude and Latitude using Copilot:
We made it. This looks really good, and not a single line of M code was required (although it’s interesting to see the code generated by Copilot :)). Next transformation I am going to run is presented in MS documentation as another capability:
- Generate a new query that may include sample data or a reference to an existing query.
But there is no option like that available in the chat, so I will try to use “Add step that…” again, to generate such query. I am looking at my data, it seems to me that Country, Continent, Region and Subregion columns contain redundant data. Of course, in real world example you would probably more values here, and business requirement would be to keep them all. However, for the test purpose let’s assume that we could get rid of them.
Based on the action I selected for Copilot to perform (Add step that…), I was actually expecting a new step to be created within the same query. Let’s try something different, I will not use the suggested start for the Copilot, but I will simply write my prompt from scratch:
It’s done but the query is a simple reference, let’s try something more complex and maybe filter data for Query created from reference (I will undo the recent output):
New Query is created, and I confirmed my predictions. Everything is related to a single Country, which could allow me to recommend removing the unnecessary columns. Let’s create another Query but this time with filtering:
It worked again. New Query is created and filtered as expected, even though I deliberately mentioned “State” column, while available column is named “StateProvince”. Last thing I will try to do will be the most complex operation so far. I would like to use the information from fact_sale Query, and calculate Total Sales values by City, using the City name from “dimension_city” Query. This would require Group By operation using CityKey in fact_sale table, and then join the City Name information from “dimension_city” Query (or vice versa):
Unfortunately, my prompt was too generic. Query was created based on “dimension_city” Query only, where another column required for the calculation is simply not present. Let’s try again (I will use “Undo” option”). Unfortunately, this time it didn’t work out. Copilot asked to re-phrase my request. Let’s try simple:
It’s not what I needed. Step is added but simply calculated TotalIncludedTax, which is even funnier, as the value in the column with exact same name is already available. But let’s give it one more try:
Ok, this time it worked. Grouping by is done the right way. Now it’s time to add City name to this picture. Unfortunately, it didn’t work out. Copilot again asked to re-phrase my request:
It worked, even though my prompt was very specific, it is still something I would consider as quite an achievement. What is also worth to mention is that we asked Copilot to do one thing, and there is one more step created on the list that is exposed to end-user. However, if we look at the code generated by Copilot, there are of course two operations done there, merge queries + expand columns. However, for the end-user we see only one operation in the list. I am including that pice of code below:
let
Source = #"Custom",
#"Merged Queries" = Table.NestedJoin(
Source, {"CityKey"},
dimension_city, {"CityKey"},
"dimension_city",
JoinKind.LeftOuter
),
#"Expanded dimension_city" = Table.ExpandTableColumn(
#"Merged Queries", "dimension_city", {"City"}, {"City"}
)
in
#"Expanded dimension_city"On one side, from the perspective of someone who wants to learn, building code in this manner could be harder to understand. On the other hand, for Power Query beginners, approach where we can quickly remove step generated by Copilot, by removing one applied step, is probably easier to handle.
Describe Query
Query descriptions in Copilot focus on technical details, not business context. While this is useful, it’s important to manage expectations. Copilot streamlines query summaries, especially helpful with inherited or poorly documented Power BI solutions. It simplifies understanding complex queries, ideal for generating concise descriptions.
There is not much we can squeeze out of Describe Query feature, so, let’s have a look at the “dimension_city” table as well:
How much does it cost?
- Report generation
- Single session – 0.4 %
- 20 developers’ number – 8 %
- DAX Query View:
- Single session – 0.92 %
- 20 developers’ number – 18.4 %
- Dataflows Gen 2:
- Single session – 1.26 %
- 20 developers’ number – 25.2 %
Total number for 20 developers working with mentioned Copilot features sums up to 51.6 % for P1 Capacity. That’s huge number. Let’s make the number more realistic and assume 10 developers scenario only, the number we get is still 25.8 % of base Capacity. And we are not done yet. What is left is the report summary feature in Power BI Service, that we will cover in next article.
