Key from 2 combines fields : Difference between Add Calculated column or add column in query editor

[amien]

I want to combine two columns within the same table. This new column can be used to link to a different table.

There are two options here:

1. Create a calculated column using DAX in the PowerBI Desktop

2. Add a Custom Column in the Query Editor.

Both will work. But what is the difference and i what cases should i use which option?

[TaylorClark · Accepted solution]

Here are the basic differences:

1. Evaluation time: Query columns are evaluated at query (data refresh) time. Calculated columns are evaluated at data model processing time.

2. Language used: Query columns use a functional language known as M (Quick Reference). Calculated columns use DAX (Quick Reference), the same language used in Measures.

3. What they can reference: Query columns can reference other queries, steps in the current query, and custom functions. Calculated columns can reference data loaded to the data model.

When should you use each?

There aren't any hard and fast rules about when to use one option over the other. It's usually case-specific. As you start to learn DAX and M, you'll realize their overlap is actually smaller than you might have expected -- they're designed for solving different problems. M is much more flexible and "general purpose", whereas DAX is designed specifically to perform analytics on tables. As a result, here's my advice:

1. Use whichever feels more comfortable to you. If you can easily write the expression in the query with M, go ahead. If it's simpler to express it in DAX, use it. I expect the languages will usually make the decision for you 🙂

2. Adding a query column requires the query and any referencing queries to be refreshed. Depending on the size of your dataset, this may be prohibitive and you may lean towards calculated columns for practicality.

Personally, I use both gratuitously. If I'm planning ahead and know I need the column at the time that I'm writing the query, I add it in the query editor. A small benefit here is that the column is included if you decide to copy your query into another PBIX file. If I realize I need something as I'm building measures and visuals, I probably add it as a calculated column.

Hope this helps! Let me know if you have any other questions

[tringuyenminh92]

Hi @TaylorClark,

Could you please share more about data refresh time and data model processing time, cause This is new concept to me and would like to be aware them.