Welcome back to another article in my Power Query series. Let’s get back to basics today and answer a common question. Just what is Power Query?. Power Query for Excel is an excellent tool for data analysis. It allows you to easily clean, transform and combine multiple data sources. It’s part of the Microsoft Excel family, and it’s free to use.
If you want more information and instruction on how to get hold of Power Query, I suggest my article on Where Can I Get Power Query For Excel, which will help you install the Add-in if you are earlier versions of Excel.
If you are using more recent versions of Excel, navigate to the Get & Transform group of tools in the Data Tab it will be ready and waiting for some Excel magic!. The Power Query interface allows anyone to connect to multiple data sources and local spreadsheets and collect, combine, and transform that data. Once users load the data into Excel or Power BI, reports can be updated as new data adds to the data sources. This process allows the automatic transformation of data and reports at users’ fingertips.
A Short History of Power Query.
Power query has been around for numerous years in one form or another, making an appearance in Excel 2010 for Windows. At this time, the feature was available as a free add-in. It can still be downloaded from here: Download the Power Query add-in. Once enabled, Power Query functionality is available from the Power Query tab on the ribbon. In Microsoft 365, Power Query is the primary process for importing, cleaning and transforming your data. All available wizards to work with your data reside in the Data Tab of the Excel ribbon. Just navigate to Get & Transform, and you are good to go. Excel 2016 and Excel 2019 have the same look and feel as Power Query in Microsoft 365.
I use Microsoft 365 as any updates and improvements are part of the subscription service, rather than the standalone versions of Excel. That decision is entirely yours.
Power Query aPower Query add-in is no longer supported.
Early 2019, Microsoft officially depreIn early 2019, Microsoft officially deprecated the Power Query Add-in, how Excel 2010 and 2013 users have access to Power Query. As of this article date, the add-in is still usable, but that may change in the future. I will update this information in this article once the status changes.
Power Query Is Not Limited To Excel.
So, Power query is not limited to Excel. Power Query is a data connectivity and transformation technology preparation engine available in many Microsoft products. However, the main focus of ExcelPowerUSer.Com is Power Query in Excel, and Power BI. Power Query comes with a graphical interface for getting data from multiple sources, along with the Power Query Editor applying transformations. There are currently two types or versions of Power Query.
- Power Query for Desktop—Find this version in Power Query for Excel and Power BI Desktop.
- Power Query Online – This version is generally used for Power BI dataflow, Azure Data Factory and other online data sources.
BBoth versions of Power Query are much the same experience for the user.
How Power Query Can Help You.
This section of the article is a brief overview of how Power query can help you and how its use can address many of the data issues that individuals find with data in organizations. Feel free to select any of the many online articles, tutorials and how-tos on my website. The articles will deep dive into specific Power Query information and solutions. So, here are solutions to data problems Power Query can provide. I believe these give you a brief overview of how this fantastic set of tools can help you. I have summarized my top 3 issues with accessing and using data that I find in organizations.
Issues Connecting To Multiple Data Sources.
Using the easy and numerous data connections available makes accessing data sources very easy. If you want to take a sneak peek at the breadth of connections a user can access, then navigate to the Data tab | Get & Transform to see the list of connections
Expand each of the options Expand each of the options to see the available connections. The experience for the user is also consistent across all options. The familiar user interface to connect to different data sources is easy to use and navigate
Shaping Data Once!
If you regularly have to reshape or transform data, then Power Query will help. If you are a macro user in Excel, it may replace some of your procedures. Quickly build queries within the interface to connect to and then transform the data. These queries can then refresh to get up to date data. The query build experience is intuitive, and a user can get comfortable rapidly within the familiar environment. If you need to change the query to accommodate a change in location of data or data schema change, then editing the query is very simple. I find editing within Power Query very simple without pulling apart a whole set of macro or code to make changes.
There are many pre-built transformation options in the graphical user interface editor. These built-in options can range from simple editing, such as removing columns or rows, to more complex functions, such as group, unpivot or pivot. To access the built-in options, select the transformation option in the menu. Below is a sample of the types of pre-built-in functions.
The ‘M’ Language.
Not all data transformations are possible with the graphical user interface. While there are a LOT of functions to choose from, you may require something more Not all data transformations are possible with the graphical user interface. While there are many functions to choose from, you may require something more specialized. So, there is a solution. The query engine is driven by a scripting language called ‘M’, and it is possible to write your won queries or transformations in this language. If you want advanced process transformations using the Power Query engine, you can use the Advanced Editor. Here you access the query script and modify it as you wish. I cover this topic in numerous other blog posts and articles, so make sure to bookmark this website or sign up for my spam-free mailing list.
If you work with data sources, especially those with millions of lines of transactional data, then Power Query will change your world. You can work with a subset of an original data set by shaping your data with queries as described above down to a manageable size. As well as managing these large data sets, you can refresh data manually, or in Power BI there is the option to schedule a refresh automatically. I encourage you to learn what Power Query is and how it works, and you will get the benefits of your data analysis easier and faster.
Working With Very Large Data Sets.
The ability to work with hundreds of data sources with potentially millions of data lines is a game-changer. Working with a subset of an entire set shaped by queries allows users to work with data sets that are more relevant and manageable. You can work with data sets and shapes of literally any type and size. These large data sets can be refreshed manually or scheduled if working with the Power BI tool.
Where Can You Use Power Query?.
have summarized below a matrix of where you can find Power Query—this list of correct at the time of this article date. I will update this as it changes in time. For more information Dataflows, I suggest this article here.
I hope this article has given you a good idea of what Power Query is and how it can help you. It’s a robust data management and analysis tool that can help you clean up, transform and combine your data to get the most out of it. You don’t need to be an Excel expert in using it – in fact, the interface is not limited to Excel at all. In this article, I have just scratched the surface of the potential of what it can do for you.
Follow this link to read all articles, how to and tutorials for all Excel users.
To keep in touch and up to date with my articles, sign up for my mailing list. No Spam. I promise. See you soon. Keep Excelling!.