About Brian DeChesare
Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street. In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.
Excel Power Query: Full Explanation and Examples [Tutorial Video] (11:03)
In this tutorial, you’ll learn how to use Power Query in Excel to retrieve data from online sources without having to copy and paste text and clean it up manually – and you’ll see how it might be useful when creating presentations and other documents.
One common question we get is: “I see you now cover Excel features like Power Pivot, Power Query, VBA, and the Internal Data Model in Excel. Do I need to know these features?”
“Will I be tested on them in interviews? How could they possibly be useful for deal-based roles like IB or PE?”
The Short Answer About More Advanced Excel Features
The short answer is that you don’t “need” to know these features for interviews, but they can make your life on the job much easier.
Even with basic knowledge gained from a few minutes spent using these tools, you can save yourself hours and hours of time.
Yes, you usually work with financial models and valuations in investment banking, private equity, and related fields, but you also have to analyze data and present it effectively.
Power Query is hugely helpful here because it can automatically parse websites, extract data, and put it in tabular format, so that you do not have to copy and paste anything manually or clean the data manually.
Also, if the website or source data ever changes, Power Query lets you refresh it automatically and make the changes flow through your model.
Excel Power Query Demonstration
The other day, I had to look up and classify a company’s sales transactions by state.
I had the abbreviations for each state or territory, but I needed to find their full names… and they were inconsistent.
Also, there were some non-standard/less-common ones, such as “MP” for the Northern Mariana Islands, “AP” for U.S. Armed Forces – Pacific, etc:
I could have Googled it and copied and pasted and tweaked the data, but instead I used Power Query to do this.
One warning: Power Query won’t work in Mac Excel. You need a fairly modern version of PC/Windows Excel (2016, 2019, or Office 365) to follow this example; otherwise, you can download Power Query as an add-in for older versions (Google “Power Query” and your version of Excel).
Step 1: Go to Data, Get Data, From Other Sources, and From Web in Excel and enter the URL you want to use. In this case, we want to use this list of state and territory abbreviations from Wikipedia:
https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations
Step 2: Click “OK” and then “Connect” and select the Table that you want to use:
Step 3: Go to “Transform Data” and delete the columns that you do not need (here, it’s based on the best matches for abbreviations and the only column that listed all the possible abbreviations).
Step 4: Now, press “Close & Load” to import this data into a table on a new spreadsheet in your Excel file. At this point, you can also rename the table under Properties and “Name” on the right-hand side under “Query Settings”:
Step 5: Now, you can write an INDEX/MATCH formula to retrieve the state/territory name from the appropriate column. We use this one in the example:
=INDEX(State_Table,MATCH(Txns_by_State!D5,State_Table[USPS],0),1)
Step 6: Copy and paste this formula down using the Ctrl + C and Alt, E, S, F shortcuts:
If the data is really messy, we could have also rearranged the table so that each row contained the state’s full name and one possible abbreviation for it, then the next row contained the next possible abbreviation for it, and so on.
And if something ever changes, we could easily refresh this data automatically and keep the transformations in place now.
Excel Power Query: Other Use Cases
You could use this same feature to update data sheets for items such as:
-Employees by location.
-Buildings by location.
-Employees by product line or geography.
-Revenue or Net Income by product or geography.
These are all common items in management presentations, memos, the Confidential Information Memorandum (CIM), and other documents that you use or create all the time in investment banking and private equity.
All you have to do is enter the Investor Relations section of the company’s website, find the right URL, and let Power Query parse all the text on the page and turn it into usable tables.
This feature is arguably even more useful in corporate finance / budgeting roles where you may need to retrieve data from online sources and link it to Excel, and you don’t want to bother with manual copy-and-paste commands.
