What’s the big deal about Power Query? Talk to those who have used it and they’ll tell you how amazing it is. Stories of automating tasks that used to take 3 hours now taking 3 minutes is not uncommon or an exaggeration.
If you haven’t heard of Excel’s Power Query tool, or you’ve heard of it but you’re not sure if it’ll be useful to you, then check out the video below where I showcase what the fuss is all about.
Watch the Video
Download Workbook
Enter your email address below to download the sample workbook.
Where is Power Query
Power Query is a tool built by Microsoft for Excel and Power BI. It’s the same tool in both apps, so you only need to learn it once and you can use it in either app.
In Excel the tools are in different places depending on the version of Excel you have:
Excel 2010 and 2013 users download Power Query here.
Click here to find Power Query in your version of Excel.
Purpose
Power Query's purpose is to:
 |
Automate the laborious getting and cleaning data tasks. |
 |
Eliminate the need to be a programmer: traditionally if you wanted to automate these laborious tasks, you’d use Excel’s programming language, VBA. However, Power Query doesn’t require programming knowledge as most of its tools are available from the GUI with point and click ease, as you can see in the video. |
 |
Reduce time-consuming tasks that can take hours, down to a fraction of the time, sometimes as little as a few seconds. |
Once you’ve gathered and cleaned your data using Power Query, you’re ready to use tools like formulas, PivotTables, and Power Pivot to analyse and visualise the data.
Data Sources
Power Query can get data from almost any file source imaginable, including proprietary systems that have either OLEDB or ODBC drivers. The image below lists the connectors currently available in Excel, but they're adding more all the time.
Power BI has even more sources available, including loads of online services. See the full list here.
Built-in Data Cleaning Tools
There is a vast range of built-in data cleaning and transformation tools readily available from Power Query's ribbon. Below are screenshots of the main tabs, but the menus don’t do its power justice. If you haven’t seen the video above, take a few minutes to see how easy it is to use.
For those keen to take things to the next level, there is also an extensive function library.
Learn More
If you’re keen to get up to speed quickly, please consider my comprehensive Power Query course.
Grouped Running Totals in Power Query
Sean
When loading the files from the folder. Power Query didn’t combine and transform the data when I clicked combine and transform data. It only pulls data from 1 sheet of 1 workbook. Negates any attempt at what I wanted to accomplish. Will have to stick with the macros I’ve created for now.
Mynda Treacy
Hi Sean,
Sounds like there’s something wrong with the query preventing the files being combined. You’re welcome to post your question on our Excel forum where you can also upload screenshots/files and we can help you further.
Mynda
John D
Madam happy to have the chance to visit your site and thank you for all you do, empowering too many people all over the globe with life changing skills. Your “school” on youtube has transformed many lives better than power query itself. It is an honour.
Mynda Treacy
That’s so wonderful to hear, John! Thank you 🙂
Waris
I find this so helpful. Thank you so much
Philip Treacy
You’re welcome.
Will
Hi Mynda and Phil – some guidance on achieving small file sizes –
I know each pivot table even with “Save source data with file” unticked increases the file size. With PQ, I build a pivot table by using an external data source -> choose connection to connect to a query. If I the tick add this data to the Data Model, the file size increases.
What in your opinion is the best way to achieve small file sizes –
– multiple pivot tables using connections; OR
– multiple pivots connecting to a Data Model loaded once; OR
– building the queries in PQ and using a Pivot Table only for summarising
Keeping in mind the time taken to load a large queries. I try and use referencing to queries rather than duplicating them or buildling new ones.
Thanks and regards
Will
Hi all – I am responding to my own comment
– multiple pivots using connections works wonders. My file size is in KB.
Build queries in PQ and connect the final query to a pivot table which you can replicate to obtain various outputs.
Mynda, please advise it this approach is incorrect
Mynda Treacy
Hi Will,
Whether you use PQ to get the data and load it to a PivotTable with connection only or load it to the data model and then build PivotTables should, most of the time result in a smaller file when you choose the data model route. With both scenarios the data is loaded to the Excel file: either in the Pivot cache or the data model. The data model has more sophisticated compression algorithms, but their effectiveness depends on the type of data you have. The more duplicate records in each column the better it can compress the data.
So, it will come down to trial and error as to which is most effective for you.
Mynda
Will
Thank you Mynda for the advise.
Unfortunately, the size of the file ballooned to 8.7MB (2 pivot tables – Query: connection only added to data model) from 234KB (15 pivot tables – Query: connection only Not added to data model).
Maybe I am doing something incorrectly. I’ll keep trying.
Mynda Treacy
Not necessarily. The Power Pivot compression algorithm is best when there is a lot of duplicate data. Sounds like your data is better suited to the regular pivot cache.
Richard Dickson
Hello I am trying to find your CSV files for the youtube coarse, but haven’t been able to locate them. Can you give me a link.
Mynda Treacy
Hi Richard, the files are available to download from this page under the video. You need to enter your email address to reveal the download link. Mynda
Mario
Hi Mynda.
I search I course in Power Query to introduce more intermediate or advanced concept, I bought book from Ken Puls and I think was a great book.
In your Power Query course, do you teach me some intermediate or advanced concepts?
Thanks
Mynda Treacy
Hi Mario, thanks for your interest in my course. Yes, you can see the full syllabus on the Power Query Course page. Please get in touch if you have any further questions. Mynda
Unmesh
Hi Mynda,
After closing and load the query, Am getting an expression error which says The Key didn’t match any rows in the table.
Can you please help me to correct this error. Thanking you in advance for this help.
Mynda Treacy
Hi Unmesh, this means you had column names in your original source file that have since changed. Either update the column references in the M code of the query via the advanced editor to match your current source file, or change the source file to match the original and expected names. Mynda
John Brewster
On your video, “Power Query Automates Boring Tasks”, I follow the instructions up to the point where I copy the address and Paste it in the Browse dialogue box, I press Ok and then I import the CSV files also and not the 3 Sales files in the video so could you please, please tell me what I am doing wrong.
Mynda Treacy
Hi John,
It’s difficult to say without seeing some screenshots of what you see at the screen where you click the ‘Transform’ button. Please post your question on our Excel forum where you can also upload a sample file and some screenshots and we can help you further.
Mynda