Though tech tools offer a wide variety of data management solutions, consolidating information from multiple sources remains a challenge. Making sense of disparate datasheets often relies on manual effort.
![Download 10 Excel Templates for Marketers [Free Kit]](https://no-cache.hubspot.com/cta/default/53/9ff7a4fe-5293-496c-acca-566bc6e73f42.png)
This is where power query can help by wrangling data from various origins into an integrated view.
As a marketing consultant, I work with multiple teams across a client’s business. To get a true picture of what’s going on with a SaaS company’s revenue funnel, for example, I typically need data from marketing, sales, customer success, and product.
However, the data I need typically gets collected across multiple locations and in different formats. Piecing everything together means getting everything in one place, in the same format, and able to be manipulated in whichever way is needed.
So, power query has been very useful to me over the years, and it’s not as complicated as you might think.
With power query, you can import, clean, transform, and merge datasets from multiple sources. Once you know how to use it, gathering and interpreting data from diverse sources becomes a whole lot easier.
What is a power query?
Power query is essentially a technology you can use within Excel to connect datasets together within an Excel spreadsheet. You can use power query to pull data from a range of sources, including webpages, databases, other spreadsheets, and multiple file types.
Once the data is imported, you can also use the power query editor to clean and transform the data. Finally, you can import the transformed data into your existing spreadsheet.
Common actions you can use in the power query editor after importing data include filtering, removing duplicates, splitting columns, formatting data, and merging data.
The core benefit is that tasks that would take you hours to do manually can be performed in minutes with power query. Plus, you can repeatedly use the same cleaning or transformation actions again at the click of a button when the data sources are updated.
With any advanced feature in Excel, there is a bit of a learning curve. But while it might take you a couple of tries to get familiar with power query, it has a pretty user-friendly interface once you know where everything is.
How to Use Power Queries in Excel
When learning how to use something like power query, the most useful method is with an example. The steps below can be applied to any type of data set, and the functionalities highlighted can be replaced with other ways to use queries as needed.
In this example, I’m pulling together sales data from two different geographic regions for analysis. The data comes from the U.S. and the U.K., with formatting and currencies that are written differently and formatted differently across two spreadsheets.
My goal is to combine these data sets in one sheet, with the data cleaned and easily interpreted and analyzed in one place.
Step 1. Open Excel and access power query.
First things first, open the main spreadsheet you’ll be working from. In this case, I’ve opened my USA sales spreadsheet, as this is where I want to clean and combine data.
In the main ribbon, click on “Data.” You’ll see “Get Data (Power Query)” as the first item in the Data ribbon.
Step 2. Import your data.
Click on the dropdown to access your power query options.
In this first step, we want to hit “Get Data,” so we can start a query using the data in the U.K. sales spreadsheet.
Clicking this option will open a window where you can choose a data source. As you’ll see, it’s possible to pull data from a wide range of sources, including shared SharePoint files.
In this case, we’re selecting “Excel workbook” to access the file that is stored locally on my computer. After making this selection, you simply browse your files for the right spreadsheet and hit “Get Data.”
Then, on the following screen, hit “Next.” From here, you can select specific sheets that you want to import. You’ll be able to preview the data before hitting “Load” to finalize the import.
If you select “Load” at this stage, power query will import the data into a new tab on your existing spreadsheet.
From there, you can select “Data” and “Launch Power Query Editor” to perform mass cleaning or updating of the imported data.
Step 3. Cleaning imported data.
If you prefer to clean your data before you import it, you can do so during the import stage.
Instead of selecting “Load” during the import process, you can select “Transform data,” which opens the Power Query Editor just like in the previous step.
For instance, the U.K. sales sheet contains the same data as the U.S. sheet, but it’s not formatted correctly. The currency format is not applied to the currency cell.
So, in Power Query Editor, I can select the “Transform” tab to edit the data before I import it (I could also do this if I imported the data first and then opened the Power Query Editor).
As you can see, there are tons of options available to you here. In this instance, I’m going to select the Currency column and selecting “Data type.”
From there, I can select the Currency option and change the value to USD.
Step 4. Load your transformed data.
The final step is adding this cleaned and transformed data into my existing spreadsheet. In Power Query Editor, navigate back to the “Home” tab and click “Close & load.”
