Category: Power Query

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

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

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

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

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

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. The filename for Read More

In this post, I want to cover how to calculate the percent of a total in Power Query.  We’ll also take this one step further, to consider how to calculate the percent of a category. In standard Excel, these calculations are simple, as we are so used to the formulas to achieve this.  But for Read More

In this post, I want to cover a common reporting requirement, which is a Power Query running total.  I have previously covered a similar topic in a post about running totals in tables; however, those methods don’t work in Power Query.  So, we’ll need to find an alternative approach. Both Tables and Power Query were Read More

It’s now time to bring this Power Query series to a close.  And where better place to end than with some tips and tricks to help you to succeed. Change the default Close & Load options Power Query’s default Close & Load options assume we want to load the data into an Excel table.  As Read More