My boss and I are having a discussion about doing data transformations in Power BI (Power Query) vs doing them in SQL with views/sprocs/T-SQL. As a result of that I am trying to come up with some advantages and disadvantages to doing our transformations in either platform. I am hoping the group could help add to this discussion.
Here is what I have so far...
Advantages to doing transformation in Power Query:
Advantages to doing staging/transformations in T-SQL views/sprocs etc:
I realize there may be various reasons why you would do things in both Power BI and SQL, just looking for some thoughts and guidelines from the group.
Personally I am trying to do things in Power BI if I can, but if it gets too complex then i revert to SQL. Thoughts?