How to Generate a series of dates from a historical table with Power Query

[PauloRicardo]

Hi everyone,

My trouble is with a historical table that has an alteration date for every branch the employee was transfer to.

Basically the objective is to generate dates between every alteration date for each employee so i can merge it with others historical tables.

Alternative 1:

ID	ALT_DATE	BRANCH
37502	01/02/2021	411090
37502	01/07/2021	410062
37502	01/11/2021	495999

In the table above, the employee was trasfered to another branch on the ALT_DATE date. Can i generate dates, day to day until the end of the first date to the second? The result must be something like this:

ID	ALT_DATE	BRANCH
37502	01/02/2021	411090
37502	02/02/2021	411090
37502	03/02/2021	411090
37502	...	411090
37502	29/06/2021	411090
37502	30/06/2021	411090
37502	01/07/2021	410062
37502	02/07/2021	410062
37502	...	410062
37502	30/10/2021	410062
37502	01/11/2021	495999
37502	02/11/2021	495999
37502	...	495999
37502	31/12/2099	495999

Alternative 2:

I also have done a variation of this, by having the initial and final date.

ID	INITIAL_DATE	FINAL_DATE	BRANCH
37502	01/02/2021	30/06/2021	411090
37502	01/07/2021	30/10/2021	410062
37502	01/11/2021	31/12/2099	495999

As i don't know which one is better to get the desired result, so i gave two options.

Thanks!

[johnt75]

Using the 2nd alternative, with both dates in the same row, you can add a custom column like

List.Dates( [InitialDate], Duration.Days([FinalDate] - [InitialDate]) + 1, #duration(1, 0, 0, 0 ) )

You can then expand the list into new rows

[DataNinja777 · Accepted solution]

Hi @PauloRicardo ,

If you're starting with Alternative 2, which includes INITIAL_DATE, FINAL_DATE, and BRANCH, that's the better choice for generating a day-by-day expansion in Power Query. You can use Power Query's List.Dates function to create a list of dates for each row between the initial and final date. Then you expand that list into rows. Here's the M code to do that:

let
    Source = YourSourceTableName,
    ChangedTypes = Table.TransformColumnTypes(Source, {
        {"INITIAL_DATE", type date}, 
        {"FINAL_DATE", type date}
    }),
    AddedDateList = Table.AddColumn(ChangedTypes, "Date", each List.Dates(
        [INITIAL_DATE],
        Duration.Days([FINAL_DATE] - [INITIAL_DATE]) + 1,
        #duration(1,0,0,0)
    )),
    ExpandedDates = Table.ExpandListColumn(AddedDateList, "Date"),
    FinalTable = Table.SelectColumns(ExpandedDates, {"ID", "Date", "BRANCH"})
in
    FinalTable

This will give you one row per day for each employee-branch record, making it easy to merge with other historical tables. If you're using the first format with only alteration dates, you'd need to add a calculated next date using the next row per employee, which is more complex. Stick with the second format if you can—it’s cleaner, more reliable, and requires fewer steps.

Best regards,