If you’re a Power BI practitioner, Power Query is the data connectivity and preparation technology that you’re inherently familiar with. It enables users to easily connect to hundreds of data sources and reshape the data to fit their needs, with a no-code, low-code user experience.
As you ramp up on Power BI, you’ll work on improving your data modelling and DAX skills but may lesser attention to Power Query and its powerful, underlying mashup language – the M Formula Language. This blog looks to provide some context around why getting across Power Query is not just exciting but prudent as well, if not for any other reason than it’s…everywhere!
Some history first – Power Query started its life as an Excel Add-In. Excel 2010 and 2013 ‘power’ users leveraged Power Query by installing the Microsoft Power Query for Excel add-in. Starting with Excel 2016, Power Query capabilities are natively integrated and can be found under the “Get & Transform” section of the Data tab in the Excel Desktop ribbon.
Power Query was later ported to Power BI Desktop as the de facto functionality to connect to and mash-up data from different sources for business analysts self-serving their reporting. The Power Query Editor is packed with new features like AI-enabled “Column from Examples” and out-of-the-box column distribution/profiling, and can be accessed in Power BI Desktop by clicking on “Edit Queries” or “Transform Data” if you’re using the recently GA ribbon experience.
If you’re leveraging the new(ish) online Power BI Dataflows experience for self-service preparation of (big) data in Power BI, here too you will find the Power Query editor making an appearance, albeit in a stripped-down avatar.
If you use Power BI to only build reports over enterprise data models created in SQL Server Analysis Services or Azure Analysis Services, Power Query is now available for use by BI developers in your organization as well. Within Microsoft SQL Server Data Tools for Visual Studio, users can leverage Power Query to access and reshape data as part of defining 1400 and above compatible tabular models.
The next time a BI developer or data engineer in your team wants to productionize your data preparation logic on Azure using Data Factory, she can simply port your M code into Wrangling Data Flow, leveraging Spark under the hood for cloud scale execution. However, this service is still in Public Preview and currently not all Power Query data sources or M functions are supported for data wrangling despite being available during authoring.
Now, let’s assume you aren’t in the business of data engineering or reporting and are instead a part of the innovation team, or simply prototyping low-code, no-code applications and automation flows on the Power Platform as a citizen developer.
The Common Data Service (CDS) for Apps lets you securely store and manage data that’s used by business applications. CDS for Apps includes a base set of standard entities that cover typical scenarios, but you can also create custom entities specific to your organization and populate them with data using Power Query in a Data Integration project. These entities can then be leveraged in Power Apps. In fact, Power Query is the integration technology available in AI-enabled 1st party Dynamics 365 apps like Customer Insights as well!
If your data is stored in SQL Server or Azure SQL, you can even use the “Transform data using Power Query” Action in Power Automate to quickly and easily create automated workflows to improve productivity with business process automation.
Whether you’re a business analyst, a data engineer or an innovation analyst, if you’re entrenched in the Microsoft ecosystem, Power Query and the M language has a promising future. Consider building expertise in it to improve your productivity across the Power Platform and Azure platforms and leverage a common language across teams to outsize your impact within the organization.