Forum Discussion

analyticsyc's avatar
analyticsyc
Frequent Visitor
3 years ago
Solved

Should I use SQL or Power Query for Data Transformations? Or something else?

I work with Syspro (an ERP system) which sits on top of an SQL Database. It records all of our company's sales information, and I use PowerBI to tie together multiple tables and create reports for di...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi analyticsyc - Roche's Maxim - do transformation as far upstream as possible and downstream when necessary.

     

    By creating SQL views for the basis of your all your extracts is advantageous.  When changes are made to the main database, the views can be subject to System Testing before production deployment.  That said, it is unlikely that tables in ERP system to be changed.  But, how easy is it for you to include and make changes to views in the main ERP.  Also, when you make changes to these views, the changes won't necessary load into your Power BI Data models.

     

    I would create pipeline before you import into Power BI Dataset files to pull the ERP data and then transform it.  

     

    Personally, I like Dataflows for this it does things in a No-Code/Low-Code way and uses query folding against the source.  This allow you centralise the Power Query in one place, but you won't have the flexibilty or scale of other Azure data lake solutions.

     

    So, it makes sense to replicate the ERP into a lakehouse for bronze, silver and gold transformations.  I would recommend using Azure pipeline to replicate the ERP database.  Here are couple of videos to watch:
    Modern Data Lakehouse? Where do you even start??? Azure Synapse of course! - YouTube. 
    Merging your data in a modern lakehouse data warehouse - YouTube.

Rickmaurinus's avatar
Rickmaurinus
Helper V
2 years ago

Good question, not per se an easy answer.

 

Power Query and SQL are not adversaries; they're allies, each offering unique advantages.

 

Power Query is known for its user-friendly interface, making it great for beginners and quick tasks. It also serves as a bridge for Excel users, allowing more sophisticated data transformations, especially in on-the-spot situations.

 

On the other hand, SQL is unmatched in its performance, scalability, and in executing precise and secure operations. It provides superior control, robust security, and ensures data reliability.

 

Depending on your project, one may fit better, but understanding and leveraging the strengths of both can be a great combination.

 

I wrote an article on this to further explain the concepts: 

https://gorilla.bi/power-query/power-query-vs-sql/