Filtering in Power Query reduces the table to include only those items which meet specific criteria. However, Power Query doesn’t have an option to filter to include all items. In Excel, we can use the asterisk ( * ) as a wildcard character to match any text. Therefore, if we want to include all items Read More
Category: Blog
Get the refresh date & time in Power Query
Knowing when our data was last refreshed is essential. It lets us know whether everything is up-to-date. So, in this post, I’ll show you how to get the last refresh date and time using Power Query. This post is in response to a Power Query question I received from Christian: “I use a CSV file Read More
Create a fan chart in Excel
A fan chart, or uncertainty chart, as I like to call them, is a way to display historical data along with a prediction of future values. For example, they are often used to indicate inflation or exchange rate predictions, but can display any data with an uncertain future value. So how can we create a Read More
Stop the auto Changed Type step in Power Query
Power Query in Excel and Power BI has an annoying default feature that automatically applies the Changed Type step even if we don’t want it. It generally occurs when creating new queries from non-database sources, but it also appears after some basic transformations. In this post, I want to show you how to stop the Read More
How to create a step chart in Excel
Step charts show data that changes at specific points, then remain consistent until the following change occurs. It’s not a default chart type, but I want to share with you how to create a step chart in Excel. Interest rates set by central banks across the world follow a similar system. Some important people in Read More
Rename columns in Power Query when names change
Power Query is great for extracting data from a source, reshaping it, then loading it into Excel. A standard part of the transformation process involves Power Query using column headers to reference each column. When the column headers change in the source data, it gives us a big problem. Recently, my friend, Celia Alves released Read More
Clear old items from a PivotTable filter list
It’s a question that every PivotTable user has had to ask at some point: How to clear old items from a PivotTable filter list? Look at the screenshot below, we can see Delta in the PivotTable filter box and the slicer, but there is no Delta in the source data table. How can that happen? Read More
Why are Excel formulas not calculating?
We have all experienced it; for whatever reason, the formulas in Excel aren’t calculating as expected. Most of the time, it is something simple that we have never thought of. But, once you know the most likely reasons, it is easier to troubleshoot the problem. So, in this post, we are looking at the most Read More
Calculate CAGR in Excel (Compound Annual Growth Rate)
In this post, we are looking at how to calculate CAGR in Excel. CAGR stands for Compound Annual Growth Rate; it calculates the annual percentage growth over a period of time, where the growth is compounded each year. The compound annual growth rate is often used in finance to understand how different investments have performed Read More
VBA to sort sheets alphabetically
When working with Excel, the worksheets order matters. The tabs should reflect the flow which a user can understand. Since there is no native feature to do it, using VBA to short sheets alphabetically can be a useful time-saver. But things are never as straightforward, are they? Sorting can be in standard or reverse order Read More
