Ever wondered what the Microsoft Office add-in Power Query is and how it works? If so, you have come to the right place.
We spoke to Pauline Jouffret, our Power Query expert at UpSlide, for all her top tips to help you master the basics of this data transformation and preparation tool.
TL;DR
Power Query, in Microsoft 365, empowers users to transform data easily without IT assistance, combining sources, cleaning, and reshaping data without coding.
It acts as an ETL tool, letting you work with vast data sources without changing the original, thus keeping Excel files lightweight and efficient.
Let’s identify the 4 key elements of the Power Query Editor: the main ribbon, queries, data table preview and query settings.
What is Power Query?
Power Query is a user-friendly, powerful data transformation and preparation tool within Microsoft 365. Because of its low-code interface, Power Query is an excellent tool to combine and transform data without the help of your IT department. It gives power and flexibility to end-users, saving you money and resources.
You’ll want to use Power Query when:
- Processing large amounts of data
- Importing data from different sources (e.g. Excel, CSV, SharePoint, SQL, and Salesforce)
- Cleaning, transforming and combining data according to your needs without coding
- Repeating regular data transformations, for example, in a monthly report
A key benefit of Power Query is that it doesn’t change the original data source. The outcome is like a snapshot of reshaped data brought to Excel. Since the source data is not directly imported, it does not make your Excel file heavier (i.e. it doesn’t slow down your computer!)
In summary, Power Query is an ETL (Extract-Transform-Load) tool that everyone can use because no coding skills are required.
Power Query is the perfect solution if you don’t have a data engineer within your organization. The low-code interface means that anyone can transform data with very minimal training.
What is the difference between Power Query and Power BI?
Power Query is part of Power BI. It’s the first step of a BI project where you want to ETL your data, calculate KPIs and present them in charts.
The interface of the Power BI Desktop software allows you to benefit from three technologies (Power Query, Power Pivot and Power View). With Power BI, you can do all the steps of the data analysis chain, from extracting data to creating visuals on an interactive dashboard that you can share and analyze.
> Discover UpSlide’s Power BI to PowerPoint or Word Link
Is Power Query a good alternative to Excel VBA?
Power Query can easily replace VBA (Visual Basic for Applications) as it enables you to:
- Process your tables simply by clicking on buttons. Plus, no coding skills are needed!
- Visualize your operations step-by-step without running a single macro.
- Easily edit the order of steps by dragging and dropping! Plus, you can add and delete unnecessary steps with a simple click.
However, Power Query can’t replace VBA when it comes to interacting with Excel’s interface, automating processes, generating new files, and sending emails.
Is Microsoft Power Query free?
Yes. Power Query is a free extension of Excel and Power BI. Within Power BI, Power Query can also be called Power Query Editor. Though Power BI desktop is free to download, you will need to pay for a license if you want to share any dashboards with your colleagues.
Is there a Power Query for Mac?
Yes. Power Query is also compatible with Excel for Mac. If you’re a Microsoft 365 subscriber, the Power Query experience is available in Excel for the Mac from the Get Data command on the Data tab. Once enabled, Power Query functionality is available from the Power Query tab on the Excel ribbon.
Do I have to download Power Query?
Power Query is a free add-in for Excel 2016 and beyond, as well as a built-in Excel feature for Microsoft 365. If your organization uses those versions of Excel, you do not need to download Power Query. You just need to click the button named Get Data in the Get & Transform section in the Data tab.
What to consider when using Power Query
- As you enter the Power Query interface, you will no longer be able to interact with Excel. If you need to return to Excel, you will have to close the Power Query editor.
- The Microsoft Power Query add-in for Excel 2010/2013 is no longer being updated by Microsoft. If you are using Excel 2010/2013, you should upgrade to a newer version of Excel to use Power Query.
Top tip:
If you are regularly cleaning and transforming data in Excel, we recommend pinning the Power Query Editor to your quick access toolbar in Excel.
How does Power Query work?
You can open the Power Query editor by clicking on transform data. It will open in a separate window. Here’s how to navigate Power Query and what you’ll find in each tab:
Data ribbon: search for data sources.
Queries and Connections pane: have a quick look at all of the connections (queries) you made.
Once you launch Power Query, you land on the Power Query Editor which is made up of four distinct elements:
Main ribbon: create queries from scratch thanks to its buttons (if you are familiar with the M language, you can also access the Advanced Editor to tweak them manually).
List of queries: keep track of all connections to data sources and streams.
Data Table preview: to visualize your data result after a specific step.
Query Settings (Applied Steps): navigate through all the steps you made. Power Query automatically records all transformations made.
That’s all for this introduction to Power Query. To discover more about how Power Query can improve data transformation and analytics within your organization, see the resources below.
For more tips on how to boost your efficiency within the Microsoft 365 suite, bookmark our Knowledge Hub or follow us on LinkedIn.
Top resources to learn more about Power Query:
- How to automate data tasks in Excel using Power Query – Excel Campus
- How to use Microsoft Power Query – Kevin Stratvert
- How Power Query will change the way you use Excel – Leila Gharani
- Power Query challenges – Ken Puls
- Combine Excel Files with Power Query – Miguel Escobar
