Many years ago, Microsoft introduced the Power Query add-in for Excel, which at first glance looks like a simple tool for importing data into Excel. However, in the meantime, they decided to integrate it into both Power BI and SSAS Tabular 2017 and various other software, because it was such a powerful data preparation tool that it would be a shame to leave it only in Excel. Therefore, we will now take a closer look at it.
Power Query is a tool for advanced preparation and combination of data from various sources, whether in Excel, Power BI or in the latest SSAS Tabular. Its advantage is that it is very easy to control and does not impose any limits on the user’s creativity. It can be connected to approx. 50 different data sources, and if you have Power BI, there are over 200 different data sources. It can import data not only from common databases such as SQL Server, Oracle, DB2, Access, SAP HANA or directly from SAP, but also from the web, from Analysis Services OLAP cubes, Excel, CSV and XML files, SharePoint, Office 365, Google Analytics or SalesForce. And in its latest version, this list can also be expanded by programming via custom connectors.
However, it does not end with data import. The resulting data can then be transformed and optionally combined with each other in Power Query. For example, you can add missing data, correct existing data, combine data from several files or databases into one table, combine data from all data sources (e.g. data from Excel with data from SQL Server), or calculate other statistics as needed. And all this using simple steps and no restrictions on where this data came from. After they are imported, all data looks like tables, and from that moment it doesn’t matter where they came from – all operations work the same on all of them. And if you want to see what Power Query can do, be sure to check out this webinar about Power Query:
Power Query uses its own M language for transformations, which currently contains over 1000 functions and elements for transformation and working with data – e.g. data aggregation, data transposition, horizontal and vertical combination of tables, data filtering, creation of conditional columns, intelligent splitting of data into multiple columns, date and time calculations, mathematical, statistical and scientific operations, iteration through records, and a bunch of other operations. And if these operations are not enough for you, you have the option to create your custom functions, and with them, even complex transformations can be performed later in a few steps. From the beginning, however, you will not need to master this language at all, because a large part of the functionality is built into the user menu in the form of buttons.
Another huge benefit of using Power Query is that it remembers all the steps you apply to your data in the step history. And you can subsequently change this history anytime – you can add more steps, change their order, delete and modify existing steps, or insert additional intermediate steps. And all this for only one purpose – so that everything continues and continues to work, even if the data or perhaps its structure in the data source changes. Or if you later remember that you should have used a different sequence of steps so that you don’t have to redo everything from the beginning. All these steps are then applied automatically not only to the existing data, but also to the new data that comes when the data is refreshed. And thanks to this, you can extremely simplify data preparation in some cases to the extent that you won’t have to laboriously build a data warehouse. Alternatively, to wait 3 weeks for the IT department, which under normal circumstances prepares this data, and due to being overwhelmed with other tasks, cannot do it anyway.
Power Query is, amongst others, found in these apps:
- in Excel 2010 and 2013 as a free add-in – you can download it from this link, which when enabled adds a new Power Query tab to the main menu,
- in Excel 2016 as part of the DATA menu, under the Get and Transform group:
- in Excel 2019/2021/365 as part of the Data menu, under the Get and Transform group
- in Power BI Desktop as Power Query Editor:
- in SSAS Tabular 2017 and later, and in Azure Analysis Services as Table Import Wizard, Query Editor and Shared Expressions, with a user interface similar to Power BI (which uses it in the background):
- in SQL Server Integration Services, as Power Query Source:
- in Power BI Dataflows:
Power Query is also found in other technologies from Microsoft, and the list has been constantly expanding over the years:
- Power Query in Excel 365 and Excel 2010/2013/2016/2019/2021
- Power Query in Excel 365 for Mac
- Power BI Desktop (for the cloud and for PBI RS)
- Power BI cloud / Power BI service
- Power BI Report Server
- Power BI Dataflows / Power BI data flows
- Power BI Data Gateway / Power Platforms
- Power BI Datamarts
- Power Automate
- Dataverse database (Power Apps, MS Teams, MS Dynamics, etc.)
- SQL Server Analysis Services (SSAS Tabular)
- Azure Analysis Services
- SQL Server Integration Services (SSIS) – Power Query Source
- Azure Data Factory (ADF)
Depending on where you use it, the list of data sources and the functionality differ slightly. For example in Excel you have around 50 data sources available, in Power BI around 200, and in SSAS Tabular around 25. However, everything else in them works plus or minus the same.
Power Query in Excel 2013 and higher has an additional bonus – direct integration with PowerPivot. You don’t need to drag the data through Excel, but you can directly load it into the data model. Which is a pretty useful shortcut if you’re parsing millions of rows. In addition, if you convert such an Excel file to a Power BI file, all these Power Query settings will also be transferred to it.
And at the same time, Power Query releases new updates every month.
Power Query is simply a tool that should have been here many years ago. And at the same time, it is designed in the way that most users imagine working with data during their preparation. In fact, this tool should be mandatory for everyone who needs to prepare data for their analyzes or reports. Because if you can handle it, it will save you a lot of time and trouble compared to classic data preparation procedures, and at the same time you will be well prepared for the future for possible changes. Because the whole tool is so flexible that there is probably no situation that it cannot handle. And if you don’t want to learn it yourself, you just found out where you can learn it 🙂 Because not everyone has time to try by trial and error how to do what they need. Whatever you decide, be sure to at least try this tool. After playing with it for a while, you’ll be surprised what you can do with it 🙂