0

need to make the start date and end date columns from the date column. I have a table of employees and the dates on which they are on the vacation but I need to reduce the table so that there is no separate row for each date if that date is part of a vacation that lasts more than one day. But care should be taken that one person can be more than one period per year on vacation and that two people or more can be the on same period on vacation. How to do it? Example Input:

Name    Date
Ivana   12/25/2020
Ivana   12/26/2020
Ivana   12/27/2020
Ivana   4/20/2020
Ivana   4/21/2020
Ivana   5/1/2020
Jelena  4/20/2020
Jelena  4/21/2020

Expected Output:

Name    Start Date  EndDate
Ivana   12/25/2020  12/27/2020
Ivana   4/20/2020   4/21/2020
Ivana   5/1/2020    5/1/2020
Jelena  4/20/2020   4/21/2020
AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
Ivana Lovrinović
  • 33
  • 4

1 Answers1

0

The question was answered here : Create a StartDate and EndDate column after value change using either T-SQL, DAX or M Query

Answer :

Using Power Query M, you can follow these steps:

In my demo data the columns came in sorted correctly, but you can use this code to ensure they are:

#"Sort Table" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Date", Order.Ascending}})

Add an Index column > Add Column > Index Column > From 0 (It HAS to be from 0)

Add Column > Custom Column > name End Date >

if [Name] <> #"Added Index" [Name] {[Index] + 1} then null else Date.AddDays(#"Added Index" [Date] {[Index] + 1},-1)

Right-click End Date > Replace Errors > Replace with null

Adjust column order as needed

Rename New Value column to "Value", rename Date column to "Start Date". Remove unneeded columns

adnane
  • 89
  • 9