Difference between Power Query and Modeling in Power BI

[JamesJarman22]

Hi All,

Relatively new to Power BI and Query here and having some issues with creating new columns. I thought that all column creation would be done in Power Query and this would then update across to Power BI. However, for some reason simple date calculations are just not working in PQ but work fine when I create them in the modeling tab on PB. I cannot understand the difference between the two other than the languages for functions seems very different (DAX maybe??).

Can anybody help me understand the differences between the two and what the pros and cons are for creating new columns in PB vs PQ please? If Power QUery is the answer why is a simple function to check to see if a date is in the future or the past (based on todays date) not working...? I have a date field (called Date) and am trying to build a simple if statement.

Really appreciate any help you can provide.

Thank you

James

[dax · Accepted solution]

Hi @JamesJarman22 , 

According to your description, it seems that you want to know the difference between M code and DAX, right?

As I know, power query is better for model data (clean, transform etc.) but DAX is better for calculations (specially for measures which don't take a lot of ram when you’re doing massive computations). You could refer to M or DAX? That is the Question!  for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans · Accepted solution]

To compare dates to today in Power Query, use the following example in a custom column:

 = if [Stuff] > DateTime.Date(DateTime.LocalNow()) then true else false

[Stuff] is my date field, and this returns TRUE if it is in the future, otherwise FALSE.

DateTime.LocalNow() is equivalent to NOW() in Excel. DateTime.Date() just strips out the time, making it equivalent to TODAY()

As a rule you should use custom columns in Power Query vs Calculated Columns in the DAX model. Calculated Columns are calculated at query refresh and generally do not compress. You can read more about it here. Custom columns from Power Query are treated by DAX as a native column and it is optimized for this.

When possible, do all modeling in Power Query, and this includes the addition of more columns than your data source has. There are some exceptions to this, but they are pretty rare. For example, there are some instances where a fairly complex DAX function in a Calculated Column can be faster or more efficient than doing it in a Measure, and the calculation simply isn't possible in Power Query. Again, fairly rare. I always try to do it in PQ first.

[dax · Accepted solution]

Hi @JamesJarman22 , 

According to your description, it seems that you want to know the difference between M code and DAX, right?

As I know, power query is better for model data (clean, transform etc.) but DAX is better for calculations (specially for measures which don't take a lot of ram when you’re doing massive computations). You could refer to M or DAX? That is the Question!  for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.