I’m sure you’ve not got this far without encountering your fair share of Power Query errors. Just like Excel and other applications, Power Query has its own unique error messages. You’ve probably forgotten the first time you encountered the #NAME? or #VALUE! errors in Excel, but over time you hopefully worked out what to do when they … Read more
One thing that 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 still catch us out. Power Query is a tool for manipulating and cleaning data, and it has a lot of features for managing dates. The Power Query … Read more
In Excel, Power Query updates when we click the refresh button. This is great if we only want up-to-date data at that point in time. However, there are many scenarios where we want Power Query to automatically refresh. So, how can we achieve that? There are built-in options and VBA options. We will look at … Read more
In Power Query, promoting headers for nested tables before expanding columns leads to fewer and easier transformations. However, these options are not available in the user interface. Therefore, we must make a few changes to the M code ourselves. This post shows two ways to promote headers in nested tables before expanding columns. Table of … Read more
There are many scenarios where raw data itself isn’t enough to complete a task efficiently. For example, we may need to add manual information, such as categorization, commentary, or reference numbers. In this post, we look at this process and understand how to add manual information to a query. Table of Contents Key principles Example … Read more
Reconciliations are a common and time-consuming activity for finance and data professionals. So, in this post, we look at how to automate reconciliations with Power Query. A reconciliation is nothing more than comparing two pieces of information and ensuring they contain the same values. Unfortunately, reconciliations can be time-consuming, as a user may need to … Read more
For many Excel users, the transition to Power Query can be difficult. Having spent years working with Excel’s functions, moving to Power Query seems like an alien environment. The good news is that many of Excel’s text functions have equivalent functions in Power Query. The purpose of this post is to provide a reference to … Read more
Our ability to manage inputs effectively is critical to building automated processes in Excel. A common reporting scenario is to get data from the latest file. This generally occurs where each file includes the historical data; therefore, we only need the latest file. In this post, we are exploring how to create an efficient solution … Read more
Recently, in our training academy, charting legend Jon Peltier demonstrated the best data structure required for working with Charts in Excel. Following this, one of our academy members asked this question: I am trying to use what Jon was teaching in the charting master class for stacked legends. Is there anyway to get that … Read more
Power Query is a nightmare when we want to combine data with different column names, and even worse when those columns are not in the same order. Can we even combine that data? You bet we can! We’re looking at that today; using Power Query to rename columns in nested tables based on a list, … Read more