Use Copilot with Microsoft Fabric Dataflow Gen2

  • 5 minutes

Data integration helps Contoso Health consolidate information from various sources. Dataflow Gen2 in Microsoft Fabric's Data Factory offers a visual interface for ingesting and transforming data. With Copilot, you can use natural language to generate Power Query M transformation steps.

Imagine preparing synthetic patient satisfaction surveys for analysis. With Copilot in Dataflow Gen2, you describe transformations in natural language, then review the generated M code and results.

Instead of focusing on every button click, this unit explores how the process unfolds conceptually, and why each step is important for building skills in data transformation.

How it works

Think of the process as a cycle of prompt → output → validation → refinement. Each stage builds on the last, and different types of transformations illustrate this cycle:

Diagram of the prompt, output, validation, and refinement cycle.

Data ingestion is the starting point. To connect to a source, open the Copilot pane from the Home tab and select Get data from. Complete the connector's connection and authentication settings, select the required tables, and bring them into the Power Query editor. Alternatively, ask Copilot to generate sample records to experiment with transformations:

Create a new query with 50 synthetic patient records. Include PatientID, Age, Gender, SatisfactionScore, Department, DateOfVisit, and HourOfVisit. Use whole numbers from 18 to 90 for Age and from 1 to 5 for SatisfactionScore. Include ENT as one Department value. Format DateOfVisit as yyyy-MM-dd and HourOfVisit as HH:mm.

Here's an example of what Copilot could generate:

Screenshot of a sample table generated by Copilot in the Dataflow Gen2 editor.

Next, ensure the data is stored in the right formats. Correct data types prevent errors in calculations and make later steps more reliable. If you want, you can combine multiple data type transformations at once, like in the following example prompt:

Change Age and SatisfactionScore to numbers; set Department as text.

Shaping the data often means adding new fields that make the dataset easier to interpret. Derived values like age groups or categories can support business-focused analysis. Here are two example prompts that add a new column, based on some classification rule you define in natural language:

Add a text column AgeRange: 18–24 as Young Adults, 25–34 as Early Career, 35–64 as Adults, and 65–90 as Older Adults. Use Unknown for null or out-of-range ages.

Create a flag column that marks patients with SatisfactionScore below 4 as AtRisk.

Filtering narrows the dataset to a defined analysis scope. These prompts demonstrate filtering syntax, not data quality rules. Removing low satisfaction scores or a department changes the population being analyzed and can bias the results. Apply these filters only when the analysis explicitly requires them:

Remove rows where SatisfactionScore is less than 3.

Exclude records where Department is ENT.

Sometimes, fields need to be combined for analysis. For example, construct a timestamp from separate date and time fields:

Merge DateOfVisit and HourOfVisit into a new column called VisitDateTime of type DateTime.

Finally, iteration is key. After each transformation, review the results and refine your prompts if something looks off. This loop helps build skill in articulating precise instructions and understanding how Copilot interprets them. Sometimes, it can be as easy as telling Copilot what to do, like in the following example prompt. You can also remove a transformation step Copilot created, and resubmit your refined prompt.

The VisitDateTime field didn’t parse correctly—recreate it using the format yyyy-MM-dd HH:mm.

Explain M code

Copilot generates transformation steps and explains the underlying Power Query M code. Compare the explanation with the actual query logic rather than treating it as proof of correctness.

Select the query you want to understand, then ask for an explanation in the Copilot pane:

Explain my current query

Review the Applied steps list, formula bar, and data preview alongside the explanation. Use these surfaces to inspect individual transformations.

Recognize limitations

Copilot operates on one query at a time, not transformations across every query in a single prompt. It doesn't retain previous inputs as conversational context for transformations. Its Undo action can remove the last Copilot change before another edit, but it can't undo an arbitrary history of committed changes. Use the editor to remove unwanted steps or queries.

Copilot can't rearrange query groups or other editor layout elements. Its results can also be inaccurate when a request depends on rows outside the imported data preview. Validate important rules against the full dataset after running the dataflow. For the current list, see Copilot in Data Factory limitations.

Best practices for working with Copilot for Dataflow Gen2

  • Copilot is best equipped to handle data integration topics, so it's best to limit your questions to this area.
  • Start simple. Test one transformation at a time before chaining multiple steps.
  • Include query names, column names, and values in your prompts. Break complex inputs into smaller tasks so Copilot has specific context for each transformation.
  • Validate after each step by reviewing the output table, then check the full dataset after running the dataflow.
  • Iterate incrementally — treat Copilot as a partner you refine with, not a one-shot generator.
  • Frame prompts around clear outcomes, such as add a column that groups ages, instead of vague commands.
  • Use the code explanations to reinforce learning and deepen technical skills.