We’ve been able to create PivotTables in Excel since the mid-1990s. Ever since then, people have been asking how to extract data from PivotTables using formulas. As great as PivotTables are for analysis, they are not always the best for presentation, which is why extracting data using a formula is so useful. We currently have Read More
Category: Power Query
How to split cells in Excel
There are many circumstances where we receive information with multiple data points inside a single cell. This often occurs when the data’s original intention is slightly different from how we intend to use it. In these circumstances, we often need to split a cell into its constituent parts. This post will look at solving this Read More
All possible combinations from multiple lists in Excel
Imagine a scenario where you have two or more lists in Excel and want to create a single table of all possible combinations. The question might come in a simple form, such as: “How can we get a complete list of all General Ledger and Cost Centre combinations?”. Sounds simple, right? There must be a Read More
Power Query – Different country date formats
One of the things which continually causes problems for all data professionals is dates. Over time, Microsoft has given us a plethora of tools and Excel functions for handling dates, yet they can still catch us out from time-to-time. Even with Power Query (Excel’s tool for manipulating data), we can run into issues when trying Read More
Power Query – Filter with wildcards
In Power Query, there is one key feature that is missing, the ability to filter using wildcards. Some have got around this with complex M code, while others suggest using the Text filter functions, such as Text.Contains, to create a similar effect. However, what I’m about to show you is an easier way to filter Read More
Power Query – Combine rows into a single cell
Last week we saw how to split a delimited cell into separate rows. This week we are going to do the opposite; combine rows into a single cell with Power Query. While having a flat table is excellent for data manipulation, but it’s not always the way a user wants to view the information. If Read More
Power Query – Split delimited cells into rows
This is the first of two posts showing how to split and combine rows from/to delimited cells. In days gone by, it would take a long time for us to resolve these scenarios. But with Power Query, it’s now a quick and easy process, which can be updated with a simple refresh. In this first Read More
Change the Power Query source based on a cell value
If you’re like me, you build Power Query solutions in a test environment first; then, when ready, it’s released into the wild. This means the paths of all source files must be updated. Or maybe you’re a consultant who creates solutions for others; when you distribute the workbook to your client, you have to provide Read More
Power Query – Absolute and relative references
In Excel, we can easily create absolute and relative references. A simple $ symbol here and there is all it takes. But Power Query is less flexible, therefore getting values from other rows is not as simple. So, in this post, we cover how to get Power Query absolute and relative references. Download the example Read More
Auto refresh Power Query on parameter change
A few days ago, a reader asked a question about using parameters in Power Query; they wanted to auto-refresh Power Query whenever a parameter changes. “One question, how do you set the query to refresh automatically when a value changes.” I’ve not tried this before, but I can understand the benefits. It reduces the need Read More
