How to make a Dumbbell Dot Plot in Excel (100% dynamic)

Dumbbell dot plots are an excellent chart style for presenting comparative data. These chart styles quickly show the difference or progress between two data points. Often, Excel tutorials for dumbbell dot plots create charts that require manual updating for new data. But using the right Excel techniques these charts can be fully dynamic. So, in … Read more

Calculate quarter from dates in Excel (inc non-standard calendars)

Quarter from date

In many companies, analyzing by quarter is common practice. Unfortunately, when we calculate quarter from dates in Excel, we can make things overly complex. This is especially true when working with financial years that do not align with the calendar year. Or even worse, non-standard calendars (such as 4-5-4 or 4-4-5)! So, in this post, … Read more

Promote headers in nested tables before expanding columns (2 ways)

Promote before expand

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

How to add manual information to a query (commentary & references)

Add manual data

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

Automate Reconciliations with Power Query (amazing time saver)

PQ Automated Reconciliation

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