Forum Discussion
Best practices for a calculated column? In the SQL query, in the PowerQuery, or in the dataset?
Hello everyone,
I don't know which option is the most efficient one and I'm hoping some kind stranger will know the answer.
Where is the best place to calculate a column? Say, IF A then B else C.
1. In the SQL query (I'm linking to an Oracle database)?
2. In the PowerQuery?
3. In the actual dataset?
Or it doesn't really matter? The dataset size is about 110 MB.
Help a girl out, please.
Best,
Alice
Hello @AliceW
This video is very useful on how to decide or design calculated columns. In my case, I reduced a powerbi above 1.3 gb (daily values above 90M rows) to 400mb.
Each time you create a computed column in the actual dataset, you need to use more space than if you create in the sql query or power query. it's not always possible to do this, but if you can create the computed column in the sql query (in the database, not in the query) or in the powerquery it would be great.
About to create the column in the SQL query or PowerQuery, I think there are no differences (it depends more on whether you know how to do it in sql or in the power query).
For example
- Calendar: If you have daily values or monthly values with millions of rows, it is very useful to create a calendar table that will create the (year, month, day, etc.) in the dataset values because it will greatly increase the size. when the calendar table will use the dates of all products in the daily/monthly dataset values
- Names: Normaly in databases works with IDs (for example, ProductTypeId - 1 means ProductTypeName - Car) and if you need to put the display names in the visuals. The easiest way is to create a computed column, but if the daily/monthly value table has many rows, this column is a string and this takes up a lot of memory, but if you create the computed column in sql or powerquery, the file size will be smaller than if you do so within the dataset. (image with many type names). Or you can map the dataset with translations of identifiers to names, link the relationship, and you don't need to create the calculated column
3 Replies
Hello @AliceW
This video is very useful on how to decide or design calculated columns. In my case, I reduced a powerbi above 1.3 gb (daily values above 90M rows) to 400mb.
Each time you create a computed column in the actual dataset, you need to use more space than if you create in the sql query or power query. it's not always possible to do this, but if you can create the computed column in the sql query (in the database, not in the query) or in the powerquery it would be great.
About to create the column in the SQL query or PowerQuery, I think there are no differences (it depends more on whether you know how to do it in sql or in the power query).
For example
- Calendar: If you have daily values or monthly values with millions of rows, it is very useful to create a calendar table that will create the (year, month, day, etc.) in the dataset values because it will greatly increase the size. when the calendar table will use the dates of all products in the daily/monthly dataset values
- Names: Normaly in databases works with IDs (for example, ProductTypeId - 1 means ProductTypeName - Car) and if you need to put the display names in the visuals. The easiest way is to create a computed column, but if the daily/monthly value table has many rows, this column is a string and this takes up a lot of memory, but if you create the computed column in sql or powerquery, the file size will be smaller than if you do so within the dataset. (image with many type names). Or you can map the dataset with translations of identifiers to names, link the relationship, and you don't need to create the calculated columnAliceW , best is that you do it as the source/SQL.
If not then power Query
if [A] ="X" then [B] else [C]
last one is DAX column
if ([A] ="X" , [B] , [C])
