My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
You may already be familiar with Excel Data Types for geography and stocks, but with Power Query Custom Data types we can now create data types based on our own data. This enables us to organise our data into a single column and then extract and reference the underlying columns/fields using formulas. It’s a streamlined …
A running total (or cumulative sum) is when you add the previous value to the next, basically you're summing values every step of the way. Creating a running total in Excel is easy. Mynda has written about a couple of ways you can do it Running Totals in Excel Running Totals in Excel Tables But …
If you're familiar with using Excel functions (and of course you should be) then you'll know that a function typically takes some input and gives you an output. The input, for example, could be a range, a number or a piece of text. The output type will vary, the SUM function will output a number, …
Office 365/Microsoft 365 users can now import data from a PDF to Excel using Power Query*. It’s super handy for importing data tables inside PDF documents because Excel locates them for you ready to import. And if the data is a little messy you can use the Power Query transform tools to clean it before …
My previous post on using Power Query to access API's looked at API's that required either basic or no authorization. This post looks at API's that implement OAuth authorization, and as an example I'll be connecting to PayPal. The following query/process works in both Excel and Power BI Desktop. What is OAuth? OAuth is a …
An Application Programming Interface (API) allows a program to retrieve data from a system. A 'program' could be something written in a language like Python or PHP, or a program could be your web browser or Excel (using Power Query). The 'system' is any repository of data, very often web based. So you might be …
In this post we’re going to look at how you can import multiple files containing multiple sheets with Power Query, even if the data isn’t formatted in an Excel Table. In other words, the worst data layout ever! The data in these sheets also doesn’t include the date that the data relates to, but …
A few weeks ago, Matt asked if we could extract start and end dates with Power Query. He has a list of non-contiguous dates and wants to identify the various date ranges. Taking the list below, you can see there are gaps indicated by the orange arrows (note, my dates are formatted dd/mm/yyyy): The desired …
Unlike Excel, referencing the next row in Power Query, or even the previous row, is not as simple as we Excel users have become accustomed. There are several approaches, but some are more efficient that others, which is something to consider if you’re working with a lot of data. Let’s take a look. First, the …
Previously we looked at scraping data from the web by example, where the website paginated the data. That is, it spread the results over multiple pages. This week I’m going to show you how you can automatically scrape data from multiple web pages with Power Query using a custom function. This approach works with URLs …
Getting data from the web is possible with Power Query in both Excel and Power BI. However, Excel is a little behind in Power Query features, therefore in this post I’m going to show you a new feature available in Power BI Desktop using Power Query to get data from the web by example. In …
A while ago I wrote about to how perform the equivalent of an Excel exact match VLOOKUP formula with Power Query which, by the way, is dead easy. Ever since then I’ve been asked to explain how to do a Power Query approximate match VLOOKUP formula. So, here it is. It requires a few more …
The ability to get data from the web with Power Query is super handy and Power Query’s user-friendly GUI makes it quick and easy. More importantly Power Query gives you an opportunity to clean the data before loading it into Excel. Then you can refresh it with one click to get updates. The current Power …
Numbering items within grouped data is easy with an Excel COUNTIF formula, but numbering grouped data in Power Query requires a few more steps. For example, taking the data below, in column C I’ve numbered the Sub Areas within each Area: Notice they don’t all have the same number of sub areas. Download the Workbook …
Splitting data over multiple sheets is perhaps one of the worst Excel crimes I see. It’s a crime because it breaks the rule that source data should be in a tabular format. Tabular data is what we need for PivotTables and many functions like SUMIFS, COUNTIFS, INDEX, VLOOKUP etc. To have your data in any …
The Power Query Add Column from Example feature simplifies a task many of us perform daily. For example, how often do you add a column that references another column; either to extract part of it, perform a calculation or convert the data into something else? Or maybe you want to join columns together? Let’s take …
If you receive your data in separate daily, weekly or monthly files then before you can even begin analyzing the data, you need to consolidate it into one table. In the past we may have written a Macro to automate this process, but if you didn’t have Macro/VBA skills then you were stuck doing it …
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 …
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 …
One of the most common questions I get is, what’s the difference between Power Query and Power Pivot and when should I use them. In this post I’m going to demystify these Excel tools and help you identify whether it’s worth your time to learn them and which one will give you the biggest benefit. …
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 …
When you know what Power Query is capable of you’ll want to get your hands on it immediately. It’s available in 32-bit and 64-bit, but unfortunately it’s not available in all versions of Excel. I’ve put together a comparison table to help you identify Power Query version compatibility. Power Query Version Compatibility Table How to …
There are 3 ways you can transpose data in Excel (not including VBA). Download the workbook and follow along Enter your email address below to download the sample workbook. Get Workbook By submitting your email address you agree that we can email you our Excel newsletter. Please enter a valid email address. Download the Excel …
A recurring task in my previous life as an accountant was to analyse actual vs budget figures. However the figures were often in different worksheets, different workbooks or even different systems. And the task of consolidating all the data into one table that was ready to analyse with PivotTables was not straight forward. Not anymore. …
