Power Query M vs SQL: A Balanced Perspective

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?” That’s a question I hear a lot. 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 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:

• Excel
• Dataverse
• 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 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 test an idea quickly. 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 data warehouse 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 data warehouse.
• Transferable Concepts: There have 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.

1. 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.
2. 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 significantly smaller.
3. 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.
4. File Speed: Data processing takes place only during refresh, ensuring that the actual Excel file remains responsive with regular use.
5. 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:

1. 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.
2. Scalability: SQL databases are designed to handle massive amounts of data, which can be challenging for Excel and Power Query in certain scenarios.
3. 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, the transformations start all over again. SQL databases, however, can store processed data, sidestepping repetitive refresh cycles. You can for example configure incremental refresh.
4. 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.
5. 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.