Performing Data Staging/Transformations in Power BI vs SQL

Hi All,

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...

Power BI

Advantages to doing transformation in Power Query:

• Fewer tables/views/sprocs muddying up SQL databases - Resulting in cleaner SQL dbs, less maintenance & less chance of breakage (who moved my cheese!?)
• Doesn't require SQL Server access - A business analyst or other user can see what is being done or takeover Power BI models if they know Power BI but don't have access to SQL
• Easier to see all transformations - easier to see what transformations have been done on data in Power Query (rather than going back and forth between SQL and Power BI)
• Faster once you are proficient in Power Query/M language

Potential Concerns

• All logic tied to Power BI - if need to move to another platform (Tableau...gasp!) may be harder to reuse

SQL

Advantages to doing staging/transformations in T-SQL views/sprocs etc:

• Can do more complex queries with T-SQL (I've run into a couple that I just had to do in SQL as it was too complicated in Power Query)
• Possible re-use of objects with other applications
• Provides another layer for logic not tied to Power BI

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?

Thanks!

Dustin