Hi @Anonymous
This is a list of the most important differences between DAX calculated columns and Power Query computed columns considering specific scenarios.
- Adding a column to the model
- A new DAX calculated column does not require a full refresh of the table.
- A new Power Query computed column requires a full refresh of the table.
- Full refresh of the table
- The engine processes DAX calculated columns after the data of all the partitions has been loaded and compressed in memory.
- A Power Query computed column is processed like any other column coming from the data source.
- Incremental refresh and partition refresh
- The engine processes the DAX calculated columns for all the rows of the table, even when only a few rows are added to or refreshed in the table.
- The engine only computes values for the Power Query computed columns of the rows that are processed in any partial refresh.
- Comparing compression
- A DAX calculated column does not participate in the evaluation of the best sort order for the compression. As a result, the compression of the column might be far from ideal, especially for columns with a low number of unique values.
- A Power Query computed column is compressed like any other column of the table.
- Processing time
- The cost of processing a single DAX calculated column corresponds to a sequential evaluation of the DAX expression for each row included in a refresh operation. Multiple DAX calculated columns within the same table are processed sequentially, one after the other, with an order compatible with calculation dependencies.
- The cost for processing a Power Query calculated column mostly depends on the data source when the expression is optimized using query folding. For example, if M expressions are translated into equivalent SQL expressions, performance and parallelism entirely depend on the SQL data source.
- Improving the compression of one column might negatively impact the compression of other columns in the data model.
- Query performance
- A column with better compression is smaller in memory and usually provides better performance levels. This is important in filter, group, and aggregation operations involving the column.
- The compression of a DAX calculated column might be lower than that of a Power Query computed column.
- An improvement in the compression of one column might negatively affect the compression of other columns in the data model.
