October 1, 2020
Power Query Custom Data Types
You may already be familiar with Excel Data Types for geography and stocks, but with Power Query Custom Data types
Read More
September 24, 2020
Quickly Create Running Totals in Power Query
A running total (or cumulative sum) is when you add the previous value to the next, basically you're summing values
Read More
September 3, 2020
Power Query Custom Functions
If you're familiar with using Excel functions (and of course you should be) then you'll know that a function typically
Read More
August 13, 2020
Import Data from a PDF to Excel
Office 365/Microsoft 365 users can now import data from a PDF to Excel using Power Query*. It’s super handy for
Read More
August 6, 2020
Connecting to an OAuth API Like PayPal With Power Query
My previous post on using Power Query to access API's looked at API's that required either basic or no authorization.
Read More
July 23, 2020
Getting Started with API’s in Power Query
An Application Programming Interface (API) allows a program to retrieve data from a system. A 'program' could be something written
Read More
April 2, 2020
Import Multiple Files Containing Multiple Sheets with Power Query
In this post we’re going to look at how you can import multiple files containing multiple sheets with Power Query,
Read More
November 28, 2019
Extract Start and End Dates with Power Query
A few weeks ago, Matt asked if we could extract start and end dates with Power Query. He has a
Read More
July 9, 2019
Referencing the Next Row in Power Query
Unlike Excel, referencing the next row in Power Query, or even the previous row, is not as simple as we
Read More
May 21, 2019
Scrape Data from Multiple Web Pages with Power Query
Previously we looked at scraping data from the web by example, where the website paginated the data. That is, it
Read More
May 14, 2019
Power Query Get Data from Web by Example
Getting data from the web is possible with Power Query in both Excel and Power BI. However, Excel is a
Read More
April 16, 2019
Power Query Approximate Match VLOOKUP
A while ago I wrote about to how perform the equivalent of an Excel exact match VLOOKUP formula with Power
Read More
March 28, 2019
Get Data from the Web with Power Query
The ability to get data from the web with Power Query is super handy and Power Query’s user-friendly GUI makes
Read More
January 24, 2019
Numbering Grouped Data in Power Query
Numbering items within grouped data is easy with an Excel COUNTIF formula, but numbering grouped data in Power Query requires
Read More
June 19, 2018
Power Query Consolidate Excel Sheets
Splitting data over multiple sheets is perhaps one of the worst Excel crimes I see. It’s a crime because it
Read More
June 12, 2018
Excel Power Query Add Column from Example
The Power Query Add Column from Example feature simplifies a task many of us perform daily. For example, how often
Read More
October 12, 2017
Power Query Get Files from a Folder
If you receive your data in separate daily, weekly or monthly files then before you can even begin analyzing the
Read More
July 20, 2017
Power Query Unpivot Scenarios
In this post I’m going to show you how to use Power Query’s Unpivot tool to fix 3 common data
Read More
May 24, 2017
Power Query Updates
The monthly update cycle for Excel’s Power Query sure keeps us on our toes. Power Query Updates for Excel 2010
Read More
April 26, 2017
Power Query Date Formats
Power Query* can easily take a column of dates and extract, or convert it into various handy date formats with
Read More
April 14, 2017
Power Query Lists
In Excel we often need to work with lists. List of numbers, letters, dates, countries, products, you get the idea.
Read More
March 1, 2017
Power Query Unpivot
Power Query’s Unpivot tool allows us to fix one of the most common mistakes (many) Excel users make when capturing
Read More
June 1, 2016
Excel Power Query VLOOKUP
Since VLOOKUP is one of the most popular Excel functions it makes sense that one of the first things you
Read More
May 25, 2016
Power Query and Power Pivot Definitive Guide
One of the most common questions I get is, what’s the difference between Power Query and Power Pivot and when
Read More
March 29, 2016
Excel Compare Two Lists
A common task in Excel is to compare two lists to identify which items are duplicates, different or missing from
Read More
February 9, 2016
Power Query Reformats Ugliest Report Ever
In this tutorial we're going to reformat Excel reports with Power Query. Why? Because external systems can create some pretty
Read More
February 1, 2016
Power Query Version Compatibility and Installation
When you know what Power Query is capable of you’ll want to get your hands on it immediately. It’s available
Read More
January 27, 2016
Transpose Data in Excel
There are 3 ways you can transpose data in Excel (not including VBA). Download the workbook and follow along Enter
Read More
October 20, 2015
Combine Excel Worksheets with Power Query
A recurring task in my previous life as an accountant was to analyse actual vs budget figures. However the figures
Read More