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
I shared how to get data from OneDrive or SharePoint into Power Query in a previous post. Many people noted that the method shown in that post works with OneDrive for Business but doesn’t work with OneDrive Personal. So, in this post, I will demonstrate the technique needed to connect Power Query to files in … Read more
In Power Query, when we connect to a local file, it’s reasonably obvious how we do that; it’s built directly into the UI. We just need to navigate to the location… Bam. Done! However, what if we want Power Query to get data from OneDrive or SharePoint? How do we do that? It’s not obvious. … Read more
Imagine a scenario where you have two or more lists in Excel and from that, you want to list all possible combinations in one table. This is known as a cartesian join. The question might come in a simple form, such as: “How can we get a complete list of all General Ledger and Cost … Read more
In this post, we look at how to calculate the percentage of a total in Power Query. But we will also take this one step further, to consider how to calculate the percent of a category. In standard Excel, these calculations are simple because we are so used to the formulas to achieve this. For many, Power … Read more