Accelerate Development in Fabric with DataFlows and Copilot

Getting Started with Copilot in Microsoft Fabric

To begin working with Copilot in Microsoft Fabric, you need a paid Fabric capacity provisioned to your workspace. Pearson starts by creating a new data flow within the Microsoft Fabric Workspace. DataFlow Gen 2, a technology closely related to Power Query and Power BI Desktop, is utilized for creating and transforming data flows in this demonstration.

Creating and Transforming Data Flows

Pearson walks through the process of combining two tables—Invoices and Invoice Line Items—into a single table using Copilot. Here’s a breakdown of the steps:

• Create a Data Flow: In the Fabric workspace, Pearson initiates a new data flow named “DataFlow” and selects the DataFlow Gen 2 option.
• Choose Data Sources: The video demonstrates connecting to a Lakehouse, a storage solution within Microsoft Fabric, to gather data. In this case, the Fabricam data is used for simplicity.
• Combining Data Tables: Pearson uses Copilot’s power to combine the Invoices and Invoice Line Items tables into a single table by issuing a prompt, "Combine the invoice line items and invoices table into a single table." Copilot automatically joins the tables using the Invoice ID key, which ensures accurate data integration.

Data Expansion and Enhancements

Once the data is combined, Pearson uses Copilot to expand the combined table to include additional relevant columns. This step is simplified by Copilot, which automatically selects and adds the necessary fields such as:

• Invoice Line ID
• Stock Item ID
• Quantity and Unit Price
• Tax Rate and Tax Amount

Pearson then manually adjusts the data to ensure that only the necessary columns are retained.

Adding Calculated Columns

With the tables combined and expanded, Pearson needs to create a new Line Total column. By instructing Copilot to combine Quantity and Unit Price, Copilot generates the correct formula to calculate the total for each line item. Pearson further refines the output by changing the data type of the Line Total column to Decimal.

Handling Data Types and Cleaning Up Data

During the data cleanup process, Pearson identifies that several date fields, such as Invoice Date, Last Contacted Date, and Confirmed Delivery Date, have a DateTime data type. To standardize the data, Pearson instructs Copilot to replace all DateTime fields with just Date data types. Although Copilot performs this transformation on most fields, one field, Last Edited When, requires manual intervention because of Copilot’s non-deterministic behavior.

Creating Order Size Categories

In a more advanced example, Pearson asks Copilot to create an Order Size column, categorizing orders as Small, Medium, or Large based on quantity. After testing a simple prompt, Copilot successfully generates the desired column by setting the criteria:

• Small: Orders with a quantity less than 50
• Medium: Orders with a quantity less than 100
• Large: Orders with quantities above 100

Pearson shows how easy it is to refine these categories by modifying the generated formula. This highlights Copilot’s flexibility and how users can further tailor its outputs to their specific needs.

Final Adjustments and Removal of Unwanted Columns

Pearson also demonstrates how to remove unnecessary columns with Copilot’s help. Using the prompt, "Remove the column ‘Confirmed Delivery Date’," Copilot quickly performs this action, making the data flow more efficient.

Conclusion and Pragmatic Works Services

By the end of the demo, Pearson emphasizes how Copilot accelerates development in Microsoft Fabric by automating time-consuming tasks, streamlining data integration, and providing quick solutions for common data issues. The video underscores how valuable Copilot can be for both experienced data engineers and beginners, especially in Microsoft Fabric's evolving environment.

For those who are new to Microsoft Fabric or want to explore further, Pearson encourages viewers to explore Pragmatic Works’ consulting services, offering expert assistance for migration, training, and hands-on support in Fabric.

Interested viewers can take advantage of Pragmatic Works’ on-demand learning platform with over 240 courses available to stay up-to-date with the latest technologies, including Copilot, Power BI, AI, and data engineering.

For more information or to schedule a consultation, visit Pragmatic Works and use the code mitchell40 for a 40% discount on courses.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Fabric and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.