Difference between Power Query and Modeling in Power BI

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.

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.