All language subtitles for 024 Calculated Column Best Practices_en

Instructor: I'd like to take a moment

and talk about some best practices when it comes to creating

calculated columns or adding new features within a data set.

So as a best practice, table transformations

and column calculations should ideally happen

as close to the original data source as possible.

Now, the reason behind this best practice

is that when calculations happen at the source,

you're able to use the original data source engine

like the SQL Server Engine or the MySQL Engine

or Excel's workbook engine and not rely

on Power BI's Engine to do these calculations.

Basically, all of this boils down

to optimizing Power BI reports for performance and speed.

Here's a high level look at the different places

where calculation should be made

or added as part of a report development project.

Ideally, new data features and columns are added

as far upstream as possible, which means

as close to the original data source as possible.

And if that can't happen, then your next best option

is to create calculated columns using the query editor,

followed by the power BI front end

and last in published reports or dashboards

if that functionality exists.

So at this point, you might be thinking

that Power Query and Power BI's front end

are all part of the same Power BI tool.

Why is Power Query more efficient than the front end?

And that's an awesome question.

The reason is that Power BI's internal engine,

called Vertipaq, creates something called the query plan

when you press load and apply

to load the data into Power BI's data model.

And this query plan takes all of the raw source data

transformation steps, any calculations that have been made,

columns that have been added, et cetera,

and it determines the best way to compress

all of that data before loading it into Power BI's memory.

Remember that lecture on storage and connection modes?

When you're using import mode, the data is loaded

into Power BI and the Veripaq engine needs to create

this highly optimized plan for fast

and reliable performance.

So when new calculations, new columns

and other data features are added using Power BI's front end

they're not added to that same query plan

and can't take advantage of the compression methods.

So because of this, you might disproportionately

bloat your data model size.

Now, there is a lot of nuance involved here

and it's generally outside the scope of this course,

but just keep in mind that when possible,

add columns at the source, if you can't,

then try the query editor, if that doesn't work

then use the front end tools in Power BI.

One last call out here before we move on.

This best practice isn't a strict requirement or rule

but it's really something

that can significantly impact performance

for very large or complex data models.

And I totally understand that where you define calculation

often depends on several factors

like accessibility to data sources, complexity,

business requirements, your own ability

and level within Power BI,

so basically we'll be practicing different methods

to create columns using both the query editor,

like we've already done, and DAX,

which is found in Power BI's front end.