In this post I’m going to show you how to use Power Query’s Unpivot tool to fix 3 common data layout problems: The Straightforward Unpivot Unpivot Multi-column Data Types Unpivot Nested Column Headers (the worst offender): And to flip things around, literally, we’ll use the Pivot tool to fix Repeating Rows, which is commonly referred […]

The monthly update cycle for Excel’s Power Query sure keeps us on our toes. Power Query Updates for Excel 2010 and 2013 If you’re using Excel 2010, 2013 then you update Power Query by going to the Power Query download page and getting the latest copy and reinstalling it. No need to uninstall first, just […]

Power Query* can easily take a column of dates and extract, or convert it into various handy date formats with the click of a few buttons. For example, the dates in the first column of the table below have been used to quickly create new columns displaying various date information: The above examples are just […]

In Excel we often need to work with lists. List of numbers, letters, dates, countries, products, you get the idea. With Power Query* we can generate lists of standard items like numbers, letters and dates quickly and easily. We can use one of the many Power Query List Functions, or we can use a shortcut […]

Power Query’s Unpivot tool allows us to fix one of the most common mistakes (many) Excel users make when capturing data in Excel, and that is to use the wrong layout. You see they tend to jump into a report format, or they might put it in a format that makes it easy for someone […]

Power Query if statements are a bit different to writing IF formulas in Excel, but once you get the hang of them they’re pretty easy. In fact, the Power Query versions of IF OR, and IF AND are even easier than the Excel equivalents. Note: this tutorial assumes you already know how to write Excel […]

Since VLOOKUP is one of the most popular Excel functions it makes sense that one of the first things you want to do in Power Query is VLOOKUP. But step away from the Add Custom Column button because there’s not a formula in sight. Excel Power Query VLOOKUP is actually done by merging tables. Makes […]

A common task in Excel is to compare two lists to identify which items are duplicates, different or missing from one list compared to the other. We can use Power Query* to easily compare two lists like these staff lists below: For referencing purposes in this tutorial I’ve formatted the names that are different in […]

In this tutorial we’re going to reformat Excel reports with Power Query. Why? Because external systems can create some pretty ugly reports. Here’s one from a popular Australian accounting system, MYOB: It’s not just ugly because of how it looks. It’s also ugly because the layout prevents you from (easily) doing any further analysis of […]