Fabric tools to extract, transform, and load data
1. Fabric tools to extract, transform, and load data
Previously, you used Copy jobs and pipelines to move data into Fabric. Now you will use Dataflow Gen2 to build repeatable, low-code preparation steps with Power Query, Microsoft's visual data-shaping tool.2. Dataflow Gen2 prepares repeatable queries
Imagine order files that may arrive every week with extra columns, inconsistent types, and only product IDs. Manual cleanup wastes time and gives inconsistent results. Dataflow Gen2 is a Fabric item that turns this recurring work into a reusable process. It connects to sources, ingests and shapes data with Power Query, then loads the prepared output to a destination. Saved queries rerun on refresh, applying the same rules every time.3. Dataflow Gen2 editor
The ribbon on top contains commands for adding data, transforming it, and configuring a destination. The Queries pane lists every query. Select a query to inspect its current result in the data preview or its dependencies in diagram view. You can enable Diagram view, Show data view, or Show schema view using the icons in the bottom-right corner. Query settings lists its Applied steps and destination connections.4. Power Query records each step
Dataflow Gen2 uses Power Query to transform data. Its buttons and menus clean, reshape, and combine data, saving each action as an applied step. Behind each step, Power Query writes code in a language called M. Power Query generates it as you work, so you won't need to write it. For example, the filter you'll use to remove canceled orders becomes Table.SelectRows, which starts from the Source step and keeps every row whose Status isn't Canceled.5. Entering data with a blank table
To begin, select Get data from another source, then Blank table. It provides a grid for entering a small dataset: type the rows, and double-click a column header to rename it. Name each query clearly so it's easy to find later. To add another table, select Enter data, the table icon on the Home ribbon.6. Applied steps
Query settings lists these applied steps in order. Source begins the query, and each later step transforms the previous result. Select a step to inspect the result at that point, or edit, reorder, or delete it. The sequence reruns on every refresh, which makes troubleshooting easier.7. Common transformations
Each common transformation fixes a specific problem in the data. Filter rows drops records that break a business rule. Change type fixes a date or number stored as text, so Power Query interprets it correctly. Add custom column calculates a new value from existing columns, such as revenue. When data is split across two queries, Append queries stacks rows from a query with the same columns, while Merge queries adds columns by matching a shared key. Let's review merge in more detail.8. Merging queries
Use merge when related details live in separate queries. An Orders query might contain quantities and ProductID, while a Products query contains each product's name. An analysis needs both, but repeating product details in every order row duplicates data. With Orders selected, select Merge queries on the Home ribbon, choose Products as the second query, and select ProductID in both previews. The Merge queries label may look slightly different depending on whether the ribbon is expanded or shows only icons. The join kind controls which rows remain. Left outer keeps every order from the first query and adds matching product details from the second. After merging, expand the new column to pick the fields you want, such as ProductName.9. Let's practice!
Let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.