Use From and To date columns to create list of dates per person

[itsme]

I have a table that shows PTO for employees. There is a To and From date columns, then columns for Hours and Hours Per Day. Instead of using the From and To columns, I want to create a list of dates for each employee that will be taking PTO on those days with the number of hours for each day.

For example, if Art has PTO from 5/30/2022 to 6/3/2022 and Marc has PTO only on 5/30/2022, the table will look like this:

Employee	From	To	Hours	Hours Per Day
Art	5/30/2022	6/3/2022	32	6.4
Marc	5/30/2022	6/3/2022	8	8

I want to transform the table in PQ so it looks like this:

Employee	Date	Hours
Art	5/30/2022	6.4
Art	5/31/2022	6.4
Art	6/1/2022	6.4
Art	6/2/2022	6.4
Art	6/3/2022	6.4
Marc	5/30/2022	8

Any idea how I can do that?

Thank you in advance!!

[wdx223_Daniel · Accepted solution]

NewStep=#table({"Employee","Date","Hours"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Dates(_{1},Duration.Days(_{2}-_{1})+1,Duation.From(1)),(x,y)=>{x{0},y,_{4}}))

[itsme]

@wdx223_Daniel Figured it out! Thanks so much for your help! I was playing with the x-y variables and replaced y with x (see screenshot below), then realized x by itself is giving the columns as a list, starting from 0.

So, I replaced _{4} with x{4} to give me the column 4 from the original table into the Hours column.

Thanks again!

[wdx223_Daniel · Accepted solution]

NewStep=#table({"Employee","Date","Hours"},List.TransformMany(Table.ToRows(PreviousStepName),each List.Dates(_{1},Duration.Days(_{2}-_{1})+1,Duation.From(1)),(x,y)=>{x{0},y,_{4}}))

[itsme]

@wdx223_Daniel I'm showing this error: "Expression.Error: The import Duation.From matches no exports. Did you miss a module reference?" I'm having a hard time figuring out this peice of code.

[itsme · Accepted solution]

@wdx223_Daniel I just realized Duration was spelled incorrectly. Now I have an error in each value for Hours: