So you’re trying to decide between learning Power Query M vs SQL. You might have looked at Power Query M and thought, “Do I really need this if I’m fluent in SQL?” You’re not alone in this thinking. As I talk about Power Query’s benefits, I often hear remarks like:
- “Why not just learn SQL? It’s a better long-term choice.”
- “Power Query struggles with huge datasets.”
- “Mature environments use SQL”
So, why should you still pay attention to Power Query? Who really benefits from it? And is there any point in diving into Power Query M if you’re already a SQL pro? These are the burning questions I aim to address in this piece. Before we get into the nitty-gritty, let’s briefly outline what Power Query brings to the table.
Where Can You Find Power Query?
First off, Power Query isn’t some obscure tool. It’s everywhere in the Microsoft ecosystem. Here are some places you’ll find it:
- Power Automate
- Analysis Services
- Power BI Desktop
- Azure Data Factory
- Power BI Report Server
- SQL Server Integration Services
- Power BI Dataflows / Datamarts
- Dynamics 365 Customer Insights
Power Query is easy to learn
If you’ve ever felt intimidated by data manipulation, Power Query might just be the tool for you. While Power Query M may not have the reputation of SQL, it’s a superhero in its own right. Here’s why this tool could be a useful for you:
User-Friendly Design: Power Query’s graphical user interface (GUI) is wonderfully intuitive. Users can:
- Dive right in, making most transformations with just a few clicks.
- Easily visualize how each step affects their dataset, an advantage that isn’t always easy in platforms like SQL Server Management Studio.
- Start transforming data without a steep learning curve. Where the thought of diving into SQL can be scary for many, Power Query offers an easy to approach alternative.
Self-documenting: Another standout feature of Power Query is its self-documenting nature. As you make changes and tweak data, Power Query automatically generates the corresponding M code. And when they’re unsure, they can easily ‘step through’ each transformation to see what it does.
Reducing the Knowledge Gap: With Power Query, non-technical users can more easily perform complex data operations previously reserved for SQL experts. This improved data handling can bring great benefits to teams and businesses.
So Power Query is not just about making things simpler; it’s about making advanced data manipulation accessible to the many. And in today’s world where so many work with data, that’s worth a lot.
But, Why Should You Bother With Power Query?
Imagine you want to quickly test an idea. That’s where Power Query shines:
- Quick Prototyping: Power Query combined with Excel speeds up the process of testing ideas. This combo is often much quicker than setting up a DWH environment, writing views and then making a POC from that.
- Accessibility: Power Query is available to a huge market because it’s free and available in Excel. The average business user will shy away from a database, let alone programming in SQL. However, Power Query is so easy to learn. And in my experience these same users are fine picking up Power Query. Don’t forget this is a huge market.
- Add Missing Data: Ever found your datawarehouse missing a crucial piece of information? Perhaps in a forgotten CSV or Excel sheet? Or something you need for a quick test? Rather than waiting in line for the BI team, Power Query lets you easily fetch, combine, and integrate this data with your main dataset. And when you’re happy with how things work, you can still have the BI team include your data in the datwawarehouse.
- Transferable Concepts: There’s been many developers moving into SQL, telling me that it was so easy for them because they knew Power Query. They had been working with different join types, appending data, filter criteria etc. All of these concepts are available in both Power Query and SQL. The benefit is that once you know these in Power Query, the same concepts apply to the SQL language. But Power Query has one big benefit here. Because of the graphical user interface in Power Query, many find it easier to master the concepts there.
So if any of the before points sounds appealing, that could be a great reason to learn some Power Query M. But there’s a more important one for a big audience. Its presence in Excel.
How Power Query Adds value in Excel
Excel is powerful by itself, but since the presence of Power Query it’s become even more powerful.
- Row Limit: Excel has a row limit of 1,048,576 rows. Power Query bypasses this by allowing you to filter or summarize large datasets before importing. And if you require the full dataset, simply load it into the data model, sidestepping the row constraints.
- File size: Power Query helps reduce size of workbooks in two ways.
- Selective Data Import: Instead of dumping an entire dataset into Excel, Power Query lets you connect to the source and cherry-pick only the data segments you need. You can even summarize your data.
- Efficient Data Storage: By loading data into the data model rather than the worksheet, you can enjoy its much better compression capabilities, meaning your file size will be to significantly smaller.
- Connectivity: Power Query isn’t just for Excel or SQL databases. It can connect to a wide variety of sources, such as web pages, SharePoint lists, or even folder directories. With hundreds of data accessing functions, you can incorporate new data.
- File Speed: Data processing takes place only during refresh, ensuring that the actual Excel file remains responsive with regular use.
- Automation: Power Query automates your data cleaning process by recording all your steps. Next time, you simply press ‘refresh’ and your transformations are applied again to your data. Much easier to learn and maintain compared to VBA.
So does that mean you should forget about SQL? Well, not quite.
SQL’s Relevance: Even in a Power Query World
Power Query is incredibly flexible, but SQL is still irreplaceable in the business intelligence domain. Here’s why mastering SQL remains invaluable, even if you’re adept with Power Query:
- Performance: SQL databases shine when it comes to performance. They’re better suited for well performing queries. While Power Query relies on the device’s power during a refresh, SQL taps into robust server capabilities, often delivering results faster.
- Scalability: SQL databases are designed to handle massive amounts of data, which can be challenging for Excel and Power Query in certain scenarios.
- Storing data: SQL’s primary function is storing data. Conversely, Power Query fetches, modifies, and temporarily loads data into a destination. Next time when Power Query refreshes, it the transformations start all over again. SQL databases, however, can store processed data, sidestepping repetitive refresh cycles. You can for example configure incremental refresh.
- Security concerns: Databases prioritize data safety. They allow precise control over who accesses what, ensuring sensitive data remains confidential. Think IP Whitelisting but also security rules for who can see the data. That way you don’t expose all data from a table to each user.
- Governance and Data changes: Structural modifications in a database, like renaming a column, can trip Power Query. However, a well-structured SQL view can be adapted to such changes seamlessly. So, instead of updating countless reports in Power Query, you only have to modify a single SQL view, simplifying maintenance.
Reflections on the Co-existence of SQL and Power Query
After learning about of the up and downsides of Power Query and SQL, let’s take a step back and reflect on their respective roles and the harmony they can achieve together. So here’s a few of my thoughts:
- Harmonious Coexistence: Both Power Query and SQL have their strong suits, and rather than viewing them as competitors, it’s more useful to recognize their complementary roles. While Power Query excels in simplicity and and the easy learning curve, SQL brings precision and scalability to the table.
- Knowing Where to Store: Each tool has areas where it shines brightest. It’s up to you to identify where certain tasks fit best. Are you happy to fiddle around, is your dataset relatively small or are you creating a proof of concept? Go for Power Query. Want the enduring strength of SQL, go ahead.
- Learning Curve: Power Query’s intuitive interface makes it an excellent starting point for data manipulation newbies. The beauty lies in how mastering its concepts creates a smooth transition to understanding SQL.
- Empowering Excel Users: Power Query bridges the gap for Excel enthusiasts, providing them more sophisticated data transformations. Considering the huge number of Excel users globally, Power Query’s influence shouldn’t be underestimated.
- Quick Turnarounds: Power Query’s design supports high-speed operations, especially for on-the-go tasks that don’t warrant the more extensive setup SQL might require. Another benefit, it comes for free with Excel and Power BI Desktop.
- SQL’s Enduring Might: While Power Query simplifies tasks for many, SQL remains the king of complex operations. It offers unmatched control, strong security, swift processing, and the ability to enforce specific rules, making sure that data remains both reliable and robust.
That was quite the read. So where does this brings us? Well let me just say, I’m a fan of both SQL and Power Query M. My career started using lots of Power Query in Excel. It helped for month-end-close tasks, prepare data that came from csv, excel and other files, and eventually it got more and more complex.
What many of the projects have in common, is that when they grow in size and complexity, they don’t always perform well with just Power Query.
The take-away from this article is. Both Power Query M and SQL are great. They both deserve their place, but there are scenarios where one has preference over the other.