It can be argued that Power Query (also known as “Get & Transform”) is the single greatest feature in Excel. Power Query can be used to help solve almost every Excel problem you encounter.
Can you think of any other feature in Excel that can make that claim? Probably not.
One of the greatest selling points of Power Query is that the same problem can be solved with many different approaches. It all comes down to your experience and creativity.
The more experience you gain, the more efficient your strategies become. The more creatively you think, the greater the problems you can solve with ease.
What Power Query Does
The expertise of Power Query lies in three key areas:
Importing Data from Different Sources
Power Query allows you to retrieve data from sources such as:
- Excel workbooks
- Text files
- Folders
- SharePoint
- Databases
- Web pages
- Exchange
- Online Services
- Outlook
This is a shortlist of sources that Power Query can natively communicate with, and new data connectors are constantly being released.
You can also work with what is known as “Big Data”; data that exceeds Excel’s native 1 million row limit. If you want specifics, “Big Data” is defined as a maximum of 2 billion tables, with each table having a maximum of 2 billion columns and 2 billion rows.
This works out to a file size of just over 9 quintillion bytes of data. Microsoft considers this “effectively unlimited”. For the VAST majority of users, this is an accurate statement.
Transforming and Cleaning Data
Power Query is equipped with an amazing transformation engine.
(The above is just one of several sets of transformation tools.)
You can shape and change data with just a few clicks of the mouse.
Transformations, like those including complex formulas and possibly VBA code, that would take hours to develop in Excel can be accomplished in seconds in Power Query.
Transformations such as:
- Compare two datasets and identify the differences/similarities
- Split data into separate columns
- Merge separated columns of data into a single column
- Group data and calculate aggregations
- Transpose, pivot, and unpivot data
- Calculate differences between dates or times
- Standardize the formatting of text
The list of transformation possibilities is almost endless.
Load Your Data
Once the transformations have taken place, you can load the result into…
- Excel Tables
- The Excel Data Model
- Pivot Tables
- Pivot Charts
You can even leave the data in the source location, only to be pulled when needed during report updates.
Creating Relationships
When combining data from multiple locations, we can leave the data in their source locations and connect them with relationships.
We are then in a prime position to create Pivot Table or Pivot Chart reports that answer complex questions about the data that would be otherwise near-impossible to answer.
Power Query’s Best Kept Secret
With most tools, like VBA, you must put in a substantial amount of time and effort into research, testing, and practice before you start to reap even the most meager rewards.
Power Query is the exact opposite.
With just a small investment in time, you can begin producing substantial results that will make any casual observer think you worked weeks to produce.
Featured Course
Master Excel Power Query – Beginner to Pro
With my Power Query course, we give you the tools to make a powerful impact on your workplace.
The course will take you from a complete novice to the foremost super-guru of the office. Even if you’ve never used Power Query, you will begin your journey developing skills that use techniques usually reserved for The Pros.
“But I’ve been using Power Query for years. What can your course offer someone like me?”
Power Query is a constantly evolving product. New features and new techniques are revealed with each monthly update.
Even if you are a Power Query veteran, there are likely features you have never tried or didn’t realize have been released.
We also dive deep into the “M Language” of Power Query in the Advanced portion of the course.
Still Not Convinced Power Query is for You?
If you are sitting on the fence as to whether you think this course may be for you, let’s look at a comparison between solving a complex problem using traditional Excel tools versus solving the same problem with Power Query.
The Data
You’ve been given the following dataset in an Excel workbook format.
The dataset is over 200,000 rows in depth.
You need to create two reports:
- Sales by Group-Customer Name
- Sales by Customer Category
The problem is that Customer Name and Category are not in the Group file. All you have is an ID number.
The bad news is your customer’s ID number is mixed in the same cell as the Invoice Number and Transaction ID.
These Customer ID numbers will need to be extracted then used to perform a lookup in a list of customer IDs and customer names.
We have this information in a text file.
Living in a World Without Power Query
We will pretend that Power Query was never invented, and we will create these reports using traditional Excel tools.
Converting the Text File to an Excel File
There are many ways you could obtain this data in an Excel format.
- Contact the IT Department and request that the customer information be extracted from the corporate database in an Excel workbook format.
Okay; that’s not happening any time soon.
- Open the file in your favorite text editor and copy/paste the data into a new Excel workbook. You hope that Excel can detect the proper column separators and maintain the structure of your date. If it doesn’t, you now have a whole new can of worms to deal with.
Once complete, we can copy the revised data to a new sheet in the same file as the sales data.
Assembling the Data
Our first report needs to show sales by Customer Group.
Because the sales data does not have the customer names, it only contains the Customer ID, we need to extract the Customer ID from the middle of the Invoice-Customer-Transaction column.
This will most certainly involve some online research to see how other users have solved this type of problem.
Extracting the Customer ID
We will likely create a series of “helper columns” to determine the locations of the leading and trailing dashes that border the Customer ID number, extract the Customer IDs, then use the IDs to lookup the associated Customer Name.
Once we have the Customer Name, we can look up the related Customer Group.
These formulas will use functions such as:
- SEARCH
- TRIM
- VALUE
- LEN
- MID
- VLOOKUP
We also need to create a calculation to determine the total sale amount for each transaction. This involves multiplying the UnitPrice by the Quantity.
Not only will all this work require a substantial investment in time, research, and testing, the file’s size is exploding beyond anything reasonable. This also reduces workbook performance.
Creating the First Pivot Table
With the data now derived, we can create our first Pivot Table.
We perform the following:
- Insert a blank Pivot Table
- Add the necessary fields
- Sort the table by sales from the highest sale to the lowest sale
- Format the sales value
We finally have our finished report.
SUCCESS!
But wait! We’re not finished. We still need to produce the 2nd Pivot Table report.
This requires more VLOOKUP functions, thereby reducing performance further and increasing file size.
With the new data, we perform the following steps to create the 2nd report:
- Copy the first Pivot Table and paste it to a new worksheet
- Update the data range to include the newly created CustomerCategoryName column
- Change the fields in the Pivot Table to reflect the new report requirements
That was a LOT of work.
The Power Query Solution
We will keep the report separate from the source data.
Featured Course
Power Excel Bundle
Connect to the Data Source (Excel Workbook)
- Open a blank Excel workbook.
- Click the Data tab select Get Data -> From File -> From Workbook.
- Browse to the file location and click Transform Data.
Extracting the Customer ID
- Select the Invoice-Customer-TransactionID
- Click the Transform tab and select Extract -> Text Between Delimiters.
- Enter the delimiter (in this case, “space dash space”).
- Change the result to a whole number.
Obtaining the Total Sales
- Select the Quantity and UnitPrice columns and click the Add Column tab and select Standard -> Multiply.
- Load the results to Excel as a connection.
Connect to the Master File (Text File)
- Click the Data tab select Get Data -> From File -> From Text/CSV.
- Browse to the file location and click Transform Data.
Extracting the Customer Group
- Select the CustomerName
- Click the Transform tab and select Extract -> Text Before Delimiter.
- Enter the delimiter (in this case, “space open parentheses”).
- Load the results to Excel as a connection.
Combine Results
- Merge the two queries by selecting the Data tab and clicking Get Data -> Combine Queries -> Merge.
- Select the 2 queries and the key column common to each table (in this case, CustomerID).
- Load the results into a Pivot Table.
Creating the Pivot Table
- Add the necessary fields
- Sort the table by sales from the highest sale to the lowest sale
- Format the sales value
It looks like a lot of steps, but it translates to a difference of hours to minutes in production time.
Size Matters
If we compare the file sizes from both solutions, we see that the traditional solution occupies 41MB of space, while the Power Query solution occupies 5MB. That’s an 88% savings in storage space. This is also a report that could be easily emailed as an attachment.
So, What Does It All Mean?
The key takeaways are these:
- Fewer steps
- Greater reliability
- Faster processing
- Easier maintenance
The only real downside is that you won’t get to spend as much time reading Web pages and watching videos about how to create complicated solutions to your Excel problems. But hey, it’s not a perfect world.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
