Transforming and loading data with Dataflow Gen2
1. Transforming and loading data with Dataflow Gen2
Now that you can navigate the Dataflow Gen2 editor, you'll prepare an Orders query for the finance team's sales reports. You'll transform it, load it to a destination, choose how updates and schema changes are handled, and schedule refreshes.2. Applying a filter
Every transformation should trace back to a requirement. Here, finance reports sales from completed orders only, but the Orders query also includes canceled ones. To remove them, select the filter arrow in the Status column header in the data preview, then clear Canceled so only completed orders remain.3. Adding a custom column
Next, finance needs revenue for each order. Open Add column, select Custom column, name it Revenue, and multiply Quantity by UnitPrice. As with every transformation, the data preview shows the result, while Applied steps records the action.4. Adding a destination to the query
With the query shaped, the next question is where its output should land. Select the plus sign next to Data destination in the Query settings pane, then choose a target such as a lakehouse, a warehouse, or another supported data store.5. Choosing who controls the destination
After you choose a target, the Use automatic settings toggle decides who manages the destination: Fabric or you. Automatic settings fit a new output table that can be rebuilt, such as a current product snapshot. Fabric manages mapping, replacement, and schema changes. This saves setup, but table recreation can remove relationships or measures added directly to it. Turn automatic settings off when the destination needs a fixed structure. Manual settings let you append daily orders, preserve a fixed schema and its relationships, or map columns to an existing table. Review the configuration when the query changes.6. Deciding what the destination keeps
With manual settings, you also decide what the destination keeps on each refresh: append or replace. Append keeps the earlier rows and adds the current output. This fits accumulating daily orders, but append does not support schema changes. Replace rebuilds rows from the current output, which fits a complete snapshot. With replace, a fixed schema keeps the table's existing columns, while a dynamic schema lets Fabric recreate the table when the columns change. If the source adds a column, review the destination mapping.7. Refresh keeps prepared data current
Loading data once isn't enough, because source data keeps changing and the destination can become stale. A refresh reruns the saved preparation steps: Fabric extracts from the source, reapplies the Power Query steps as the transform, and loads the current result. Refresh repeats the query rather than redesigning it. The resulting run can then be monitored for completion or failure.8. Choosing when and how much to refresh
Refresh timing and refresh amount are separate decisions. Use an on-demand refresh for data needed now. For example, a weekly schedule can refresh the dataflow every Monday at 12:00 PM. You set the schedule from the dataflow's menu in the workspace item list, under Schedule. A full refresh reprocesses the entire source on every run. Use it for a small reference table, a source that publishes a complete snapshot, or data whose historical rows can change without a reliable change-tracking column. Incremental refresh is better for a large time-based table, such as years of orders, when a reliable date-time column can identify the buckets that need processing.9. Let's practice!
Now let's filter orders, add a calculated column, load the result to a lakehouse, and schedule its refresh.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.