Microsoft Copilot is rapidly becoming a core part of the modern data stack. It is integrated across Microsoft Office products, embedded into the Power BI Desktop experience, and deeply connected with Microsoft Fabric. In this post, we focus on how Copilot supports data engineering tasks within Microsoft Fabric, specifically through Dataflow Gen2.
Whether you’re new to Fabric or already using Power BI, understanding how to leverage Copilot will help you work faster and smarter.
Prerequisites for Using Copilot in Microsoft Fabric
To follow along with this walkthrough or begin using Copilot effectively in your own environment, you will need the following:
- An active, paid Microsoft Fabric capacity
- A workspace with Fabric capacity assigned
- Permissions to create and manage Dataflows Gen2
- Familiarity with Power Query is helpful but not required
Step 1: Creating a Dataflow Gen2
Begin by navigating to your Fabric workspace and selecting “New Item” followed by “Dataflow Gen2”. Name the dataflow appropriately and click Create.
This interface uses the same underlying technology as Power Query in Power BI Desktop. If you’re used to building queries or transforming data in Power BI, the transition to Dataflow Gen2 will be straightforward.
Step 2: Connecting to a Lakehouse
The next step is to bring in your data. In this case, the data is stored in a Lakehouse within Fabric. It includes invoice data and line item details.
Instead of pulling from multiple sources, the demo keeps it simple by using an existing Lakehouse containing Fabricam sample data. These two tables Invoices and Invoice Line Items will be joined and cleaned as part of the data engineering process.
Step 3: Joining Tables with Copilot
Copilot simplifies the process of combining datasets. With a simple prompt such as:
“Combine the invoice line items and invoices table into a single table,”
Copilot correctly identifies the shared key (InvoiceID), performs a left outer join, and creates a unified table. The left outer join ensures that all invoice records are preserved, even if there are no matching line items, which is typically the safe choice in reporting scenarios.
This operation is fully automated and does not require specifying join keys or types.
Step 4: Expanding and Selecting Required Columns
After the join, you can choose which columns to keep from the expanded table. By unchecking the “Use original column name as prefix” option, the output remains clean and easier to interpret.
Manually select only the relevant columns such as InvoiceLineID, StockItemID, Quantity, UnitPrice, TaxRate, and TaxAmount.
Step 5: Creating Calculated Columns with Copilot
To enrich the dataset further, you might need calculated columns. For example, to calculate the line total:
“Create a new column for line total that combines unit price and quantity.”
Copilot recognizes the intent and generates the correct multiplication logic. Once created, the data type may be set to “Any” by default. You can fix this by prompting Copilot:
“Change the data type of line total to decimal.”
This ensures the column is ready for accurate aggregations in your Power BI reports.
Step 6: Replacing DateTime with Date
Data often contains timestamp fields that are unnecessary for reporting. To simplify:
“Replace all data types of datetime with date.”
This instructs Copilot to convert timestamp fields to just the date portion. However, this transformation may occasionally be incomplete, so it is important to validate the outcome. AI-generated steps can be inconsistent across runs, making human review a necessary part of the process.
Step 7: Creating Order Size Categories
You can classify records into categories such as small, medium, and large orders based on quantity. Start with a basic prompt:
“Create a new column for order size.”
Initially, Copilot may not produce the desired output due to limited context. Refining the prompt improves results:
“Create a new column for small, medium, and large orders and name it order size.”
In the example, Copilot generated logic that defines:
- Small: quantity < 50
- Medium: quantity < 100
- Large: all others
You can manually adjust these thresholds in the formula bar or give Copilot more specific instructions.
Step 8: Removing Unnecessary Columns
Copilot can also handle simple cleanup tasks. For instance:
“Remove the column returned delivery data.”
Copilot uses the correct Power Query syntax to remove the specified column, even if the prompt is not perfectly formatted.
Step 9: Pointing to a Destination Table
After building and refining the dataflow, select a destination table where the cleaned data will be loaded. This makes it available for reporting in Power BI or other Fabric-connected tools.
Key Takeaways
Copilot offers significant productivity improvements for data engineers working in Microsoft Fabric. It can:
- Join tables based on natural language instructions
- Build calculated columns without writing M code
- Format and clean data types
- Categorize data using conditional logic
- Remove or modify fields quickly
However, Copilot is not always consistent. It is important to validate all transformations and be prepared to manually adjust generated logic.
Need Help Getting Started with Microsoft Fabric?
If you are migrating to Fabric or want help using Copilot effectively, Pragmatic Works offers consulting and support services. Whether you’re just starting or building out enterprise-scale solutions, expert guidance can accelerate your success.
