Hello,
I use a leave tracking software where employees enter leave requests. Each request creates a record and a group of records can be exported to a .csv file. The problem: when a multi-day request is made the record appears as follows:
| StartDate | EndDate | Employee | Hours | Type |
| 1/23/2017 | 1/26/2017 | EEName | 32 | Vacation |
I would like the data to list each day individually:
| StartDate | EndDate | Employee | Hours | Type |
| 1/23/2017 | 1/26/2017 | EEName | 8 | Vacation |
| 1/24/2017 | 1/26/2017 | EEName | 8 | Vacation |
| 1/25/2017 | 1/26/2017 | EEName | 8 | Vacation |
| 1/26/2017 | 1/26/2017 | EEName | 8 | Vacation |
Any tips?
Hi Chris,
You can use this query, that will expand the rows where there are more than 8 hours:
let
Source = Csv.Document(File.Contents("C:UsersCatalinDesktopSampleData.csv"),[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"StartDate", type date}, {"EndDate", type date}, {"Employee", type text}, {"Hours", Int64.Type}, {"Type", type text}}),
Records = Table.ToRecords(#"Changed Type"),
NumberOfHours=8,
Expand = (x) => List.Generate(
() => Record.Combine({x, [EndDate=x[StartDate],Hours=NumberOfHours]}),
(record) => record[StartDate] <= x[EndDate],
(record) => let
NextStart = Date.StartOfDay(Date.AddDays(record[StartDate], 1)),
NextEnd = Date.EndOfDay(NextStart),
ThisEnd = List.Min({NextEnd, x[EndDate]})
in
Record.Combine({record, [StartDate=NextStart, EndDate=ThisEnd,Hours=NumberOfHours]})
),
Transformed = List.Transform(Records, each if [Hours] <=8 then {_} else Expand(_)),
Combined = List.Combine(Transformed),
Result = Table.FromRecords(Combined),
#"Changed Type1" = Table.TransformColumnTypes(Result,{{"StartDate", type date}, {"EndDate", type date}})
in
#"Changed Type1"
I attached the sample file used, and the file with the query, so you can test it.
You should change the file path in the first line of the query:
Source = Csv.Document(File.Contents("C:UsersCatalinDesktopSampleData.csv")
The solution is inspired from this topic: multiply-rows-based-on-date-and-time
Hello Catalin,
Thanks so much for the help. I appreciate the thorough response as well as the references and sample files.
Kind regards,
Chris
You're wellcome 🙂
Keep in mind that the solution is based on the StartDate and EndDate, it ignores the Hours column, the new rows will always have 8 hours. If there is a record with a 4 days span and 28 hours instead of 32 (4*8), you will get 8 hours for all new rows, the initial 28 hours will not be distributed.
Depending on your needs, the solution needs to be adjusted if you need to distribute the exact amount from Hours column.
