Fill Dates between dates

[Anonymous]

I have a date range, Start date and End date and total hours for that range per job.

What i have done is taken total hours/(End date-Start date) to create an average for each line.  I would like to auto generate the dates between start and end dates.

For example this is the table I have.

JOB#, Start date, End date, Total hours, Total daily hours

12345, 5/10/16, 5/20/16, 100, 10

12346, 5/12/16, 5/22/16, 50, 5

What I would like to see is:

JOB#, Date, Total hours, Total Daily Hours

12345, 5/10/16, 100, 10

12345, 5/11/16, 100, 10

12345, 5/12/16, 100, 10

12345, 5/13/16, 100, 10

12345, 5/14/16, 100, 10

12345, 5/15/16, 100, 10

....

12345, 5/20/16, 100, 10

12346, 5/12/16, 50, 5

12346, 5/13/16, 50, 5

....

12346, 5/22/16, 50, 5

any help is greatly appreciated.  Thank you.

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD JOB#,

  Date([Start date] + IterNo() - 1) as Date,

  [Total hours],

  [Total daily hours]

While [Start date] + IterNo() - 1 <=  [End date];

LOAD * Inline [

JOB#, Start date, End date, Total hours, Total daily hours

12345, 5/10/16, 5/20/16, 100, 10

12346, 5/12/16, 5/22/16, 50, 5

];

[Anonymous]

Thank you!

[nicogene]

Hello Sunny,

I hope this message finds you well.

I would like to recreate each row per day (screenshot below) and excel file in attachment:

What is the best approach?

Thank you very much for your help.

Kind regards,

Nicolas.