BEST PRACTICE: How much data cleanup to do in DAX vs. Power Query?

Oh boy, that is a loaded question. Anonymous will probably want to chime in on this.

So, my answer to this is that it depends. There's a few schools of thought on this. One, school of thought, try to push as much data cleanup up the "chain" as possible. So if you think about it, you have:

1. Source

2. Power Query

3. Data Model/DAX

The obvious thinking here is why process more data or store more data than you need to? So if you can weed things out in a SQL View versus connecting directly to a table, that's a good idea. Can really speed up data loads and refreshes. But, if you don't have access to the source to make changes, that's out obviously.

So that's data cleanup, generally best to do that in the Source or in Power Query. What about transformation? Depends, generally you want to do your transformations in Power Query. That being said, I have seen unpivoting data from a SharePoint list take inordinately long in Power Query. That's where UNPIVOT came from (DAX solution for unpivoting columns in tables). So that's not absolute but generally a good idea.

Now, what about adding columns and such. Well, this gets into more grey area in my opinion. Some things are a breeze to do in DAX and a real pain in Power Query and vice versa. Then there is the maintainability piece of it. If you have to do certain calculations in DAX then is it better to do them all in DAX so that you have a single code base? In other words, someone coming after you doesn't have to know Power Query and R and Python and DAX to maintain your report file? Because you can use all of them if you want in the same Power BI file and now you need to know 4 languages instead of 1 to maintain it.

So, in very large generalities, better to push the processing up the chain to the source and failing that Power Query. But, there are other things to consider in my opinion that does not make it an absolute black and white issue.