Forum Discussion
Making changes in Power Query vs Data Tab with DAX influence on file size
I was curious if making similar changes to a table results in a similar increase in file size. Or if changes to data through Power Query vs Data Tab result in a greater file size increase?
For example:
Combining first name and last name columns to make a full name column. I can do "Merge Column" in Power Query or I can concatenate with a DAX statement in the Data Tab. Both doing the same thing, would one result in a larger file due to how the full name column is made?
In general, DAX calculated columns have the potential for worse compression than "native columns" (columns in the table loaded from Power Query). This is because calculated columns do not participate in determining a table's optimal sort order for compression purposes.
See this article:
https://www.sqlbi.com/articles/storage-differences-between-calculated-columns-and-calculated-tables/
For this reason, it's best to push any transformations as far upstream as possible. In your case, the first preference would be to derive the column in the data source itself, and the second would be Power Query.
There can certainly be situations where a DAX calculated column is more convenient, such as when the column depends on the data model in some way (eg measures or relationships). In your case though, a concatenation of other columns is a clear candidate for Power Query (if it not further upstream).
Regards
1 Reply
In general, DAX calculated columns have the potential for worse compression than "native columns" (columns in the table loaded from Power Query). This is because calculated columns do not participate in determining a table's optimal sort order for compression purposes.
See this article:
https://www.sqlbi.com/articles/storage-differences-between-calculated-columns-and-calculated-tables/
For this reason, it's best to push any transformations as far upstream as possible. In your case, the first preference would be to derive the column in the data source itself, and the second would be Power Query.
There can certainly be situations where a DAX calculated column is more convenient, such as when the column depends on the data model in some way (eg measures or relationships). In your case though, a concatenation of other columns is a clear candidate for Power Query (if it not further upstream).
Regards