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
Over the last few months, I’ve been asked several times how to add a fiscal month, fiscal quarter, or fiscal year column in Power Query. So, I decided to note down the method; therefore, I don’t have to keep reminding myself how I did it last time. The good news is that it’s reasonably straightforward. … Read more
In Power Query, it is common to have nested Tables. These are Tables contained within a column, where each row contains a separate sub-Table. Sometimes we just want to expand the data; that is easy. But sometimes, we need to transform the data in the nested Table before expanding; that is NOT so easy. So, … Read more
Power Query has a really powerful feature that lets us combine all the files in a folder. However, sometimes the files are not contained in a single folder. Sometimes they are spread out in multiple places. How can we deal with that? That’s what we are looking at in this post: how to combine files … Read more
Expanding table columns is a common Power Query action. It even has its own icon. But there is a problem lurking here. Clicking the expand data button hardcodes the column names into the M code. So, if new columns are added to the source at a later date, they don’t show up. So, in this … Read more
Filtering is a common task in Power Query. Usually, we filter by a single value, or maybe a small number of known values. These filter values are hardcoded into the underlying M code. Therefore, to change the filter, we have to edit the query. But what if we don’t know which items we want to … Read more
In a previous post, we looked at various methods to remove additional spaces in Excel. One of the methods involved creating a custom function in Power Query. As this is a more advanced method, I wanted to go a bit deeper and describe the method to remove spaces in Power Query. Table of Contents Excel … Read more