Wrangling Data Flows vs Mapping Data Flows in ADF

Azure Data Factory offers two ways to transform data without writing Spark code, and their names do not help. Mapping data flows are built as a graph of transformations. Wrangling data flows, which the product now presents as the Power Query activity, are built in the same Power Query editor that Excel and Power BI users know. This guide compares the two and says which suits which job.

What each one is

Mapping data flows are visually designed transformations that run as pipeline activities on Spark clusters the service manages (Microsoft Learn: mapping data flows). You place a source, add transformations such as Join, Aggregate and Conditional split, and end in one or more sinks. Mapping data flows in ADF introduces them.

Wrangling uses Power Query. Data Factory integrates with Power Query Online and makes Power Query M functions available as a pipeline activity (Microsoft Learn: data wrangling). You see your data in a grid, apply steps from a ribbon, and each step becomes a line of M.

The same engine underneath

The important fact is easy to miss: both run on Spark. Data Factory translates the M that the Power Query editor generates into data flow script, and runs that on the data flow Spark environment. Wrangling is, in effect, a different front end for the same engine.

Two consequences follow. A Power Query activity starts a cluster like any data flow, so it is not a lightweight option for tiny jobs. And only the M that can be translated to Spark is allowed, which is the source of most of its limits.

The differences that matter

What Power Query wrangling supports

Sources. Microsoft lists five connectors: Azure Blob Storage, Azure Data Lake Storage Gen1, Azure Data Lake Storage Gen2, Azure SQL Database and Azure Synapse Analytics. For the three storage connectors the formats are CSV, Parquet and Excel.

Functions. The supported list is organised by task (Microsoft Learn: wrangling functions):

• Columns: select, remove, rename, reorder.
• Rows: Table.SelectRows with comparisons on numbers, text and dates, text containment, and combinations with and, or and not.
• New and changed columns: Table.AddColumn, Table.TransformColumns, Table.ReplaceValue, with arithmetic, text and date functions and if expressions.
• Joins: inner, left outer, right outer and full outer.
• Grouping: Table.Group with sum, count, average, min, max, standard deviation, first and last.
• Sorting, and keeping or removing a number of rows from the top.

A script that stays inside that list:

let
    Source = Orders,
    Recent = Table.SelectRows(Source, each [OrderDate] >= #date(2024, 1, 1)),
    WithName = Table.AddColumn(Recent, "Customer", each [FirstName] & " " & [LastName]),
    Totals = Table.Group(WithName, {"Country"}, {{"Revenue", each List.Sum([Total]), type number}})
in
    Totals

This is written from Microsoft's supported list and has not been run in Data Factory.

Where wrangling stops

The editor lets you click steps that the Spark translation cannot run. Microsoft says so directly: not all M functions are supported for wrangling, despite being available during authoring. The error names the cause, either that the Power Query Spark runtime does not support the function, or that the logic "requires dynamic access to rows of data, which cannot be scaled out".

The documented gaps:

Three structural limits apply as well:

• One sink. A Power Query activity needs at least one source and allows only one sink, chosen when you add the activity to a pipeline (Microsoft Learn: wrangling tutorial).
• One query to work in. Source datasets appear as queries, and all transformations must be made on the query named UserQuery. Renaming, adding and deleting queries is not supported.
• A single output file needs a setting. The output is partitioned as Spark writes it. For one file, the sink's partition option has to be set to single partition.

The pattern is consistent: anything that depends on the position of a row, last rows, row counts, transposing, is hard to do when data is spread across a cluster.

Status: check this before starting new work

Microsoft's comparison of Azure Data Factory with Fabric Data Factory lists the Power Query activity, which it also calls the wrangling dataflow, as deprecated on the Azure Data Factory side (Microsoft Learn: differences between Data Factory in Fabric and Azure). The Data Factory pages for the feature itself carried no such notice when this article was written.

The safe reading is this. Pipelines that already use the Power Query activity are still documented. It is not something to choose for new pipelines in Azure Data Factory. New Power Query work belongs in Dataflow Gen2 in Fabric, which is where Microsoft maps it.

Which to use

Microsoft frames wrangling as suited to exploration and less formal analytics, where you are not mapping to a known target, and names data engineers and "citizen data integrators" as its users. That is a fair summary. Power Query is the faster way to understand and tidy data. A mapping data flow is the more complete way to load a warehouse. The transformation reference shows how much more there is on that side.

How the choice carries into Microsoft Fabric

Microsoft describes Data Factory in Fabric as the next generation of Azure Data Factory. There, the standard dataflow, Dataflow Gen2, is authored in the Power Query Online interface, and Microsoft publishes a guide that maps each mapping data flow transformation to its Power Query equivalent (Microsoft Learn: Fabric guide for mapping data flow users): Join becomes Merge queries, Aggregate becomes Group by, Derived column becomes Custom column.

Two points from that guide matter for planning:

• Four transformations have no Dataflow Gen2 equivalent: Assert, Alter Row, Stringify and Window.
• Mapping data flow transforms can also run in Fabric, as a feature the guide describes as in preview, with a migration experience for eligible pipelines.

So Power Query skill is not a dead end, quite the opposite, and a library of mapping data flows is not stranded. If you are learning from the start, the Power Query material in the Power Query tutorial and M language basics applies in both places.

Mistakes to avoid

• Assuming every Power Query step will run. Validate early, by running the activity in a pipeline debug run, before building twenty steps on an unsupported one.
• Using wrangling for a tiny file because it feels light. It starts a Spark cluster. Data flow debug and performance explains the start-up cost.
• Editing the dataset queries. Changes outside UserQuery are not kept.
• Expecting database merges. Microsoft's wrangling documentation describes writing the result to a sink, with nothing equivalent to Alter row.

Azure Data Factory and data transformation tools are covered in the Data Engineering course.

Sources

• Data wrangling in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Data wrangling functions in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Getting started with wrangling data flow in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Mapping data flows in Azure Data Factory (Microsoft Learn) — Microsoft Learn
• Differences between Data Factory in Fabric and Azure (Microsoft Learn) — Microsoft Learn
• A guide to Fabric Dataflows for Azure Data Factory Mapping Data Flow users (Microsoft Learn) — Microsoft Learn