Filling in dates

Overview

Let’s say you have been given a report with a set of start and end dates, but the report needs to be fully expanded to show all the in-between dates.

This article explains various ways of doing this.

Scenario 1 – Continuous dates between dates

Imagine, for example, you have been given the following table:

To fill in the dates between the two dates given for each person, you need to do the following:

• Load the table into Power Query
• Add a Custom Column with the following formula

The query should look as follows:

Next, expand the List objects in the ‘Dates’ column, like this:

The table should expand as follows, with all the in-between dates filled in:

Scenario 2 – Fill in x amount of days

Sometimes, you might get data in the following format - which holds the membership start date for each person, and their membership duration, in days.

To expand these to the correct dates, you need to do the following:

• Load the table into Power Query
• Add a Custom Column with the following formula

The query should look as follows:

After expanding the ‘Dates’ column, the query should transform as follows:

Scenario 3 – Fill in specific days

Imagine the following scenario, with follow up dates for an appointment. You need to determine when these follow up appointments are.

To determine the correct dates, you need to do the following:

• Load the table into Power Query
• Add a Custom Column with the following formula

The query should look as follows:

After expanding the ‘Follow Up Dates’ column, the query should transform as follows, with the correct Follow Up Dates, populated: