Dates between create columns

[Igarsaba]

Hi guys, i need some help

I have a table with Start Date and End Date for each employee

I need to create one row for each day between these two date, repeating the employee names.

Is there a way to do that?

[ChielFaber · Accepted solution]

I had a similar usecase within my work environment and I solved this using M powerquery.

They idea is to create a list with dates between the start data and the enddate

 You can create a new column containing the list with dates as followed:

Table.AddColumn(  #"PreviousStepName", "NewColumnName", each List.Dates([Startdate], Duration.Days([Enddate] - [startdate]), #duration(1,0,0,0)

    ))

With this code you get a new column containing for each row a list with dates between the startdate and enddate. In the next step you simply expand the list and as a result you get a row for each date between the startdate and enddate.

For example when the startdate is 1-1-2023 and the enddate is 10-1-2023 you get a row for each day.

[ChielFaber · Accepted solution]

I had a similar usecase within my work environment and I solved this using M powerquery.

They idea is to create a list with dates between the start data and the enddate

 You can create a new column containing the list with dates as followed:

Table.AddColumn(  #"PreviousStepName", "NewColumnName", each List.Dates([Startdate], Duration.Days([Enddate] - [startdate]), #duration(1,0,0,0)

    ))

With this code you get a new column containing for each row a list with dates between the startdate and enddate. In the next step you simply expand the list and as a result you get a row for each date between the startdate and enddate.

For example when the startdate is 1-1-2023 and the enddate is 10-1-2023 you get a row for each day.

[Igarsaba · Accepted solution]

In this case, the last day isnt beeing created, what parameter defines that?

[ChielFaber]

Do you mean if the enddate is null? You could choose to replace those values with the date from DateTime.LocalNow from https://learn.microsoft.com/en-us/powerquery-m/datetime-localnow

If that isn't what your saying could you then provide a small example table with dummy data

[ronrsnfld]

Add 1 to the "Count" parameter:  

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

[johnt75]

You could create a calculated table in DAX

Employee dates =
SELECTCOLUMNS (
    GENERATE ( Employee, CALENDAR ( Employee[Start date], Employee[End date] ) ),
    "Employee first name", Employee[First name],
    "Employee last name", Employee[Last name],
    "Date", [Date]
)