Power Query: Start and End with Grouping

Welcome to our Power Query blog. Today I look at extracting start and end dates for my list of sales employees.

For those following the series, my past blogs have often dipped into the expense accounts of my (fictional) salespeople. For today’s scenario, I will extract the start and end date of my employees’ expense period to show how easy it is when I use the grouping functionality in Power Query.

I created the sheet below using a combined expense query created in Aggregating Aggravating Worksheets.

I already have my ‘Expenses’ query, so I begin by double clicking on my query to get into the ‘Query Editor’ screen:

In Group and Conquer, I looked at using grouping to simplify data, and I will be using a similar technique here. On the ‘Transform’ tab, I choose ‘Group By’. Since I am looking for the period for each person, my grouping will be driven by my Name column. I need to find the start and end date for their expense period. I begin entering the first grouping level, naming my column ‘Start Date’, and making it a minimum of the date:

So far so good, so what? Now I need to get the end date, so I create another grouping level using the ‘Add aggregation’ button. This will also act on the Date column, this time taking the maximum value:

Now all I need to do is click ‘OK’ to see what happens.

It all looks good, so I have my result which can be loaded into an Excel sheet.

Want to read more about Power Query? A complete list of all our Power Query blogs can be found here. Come back next time for more ways to use Power Query!