How to create Start Date and End Date columns from one Date Column in a new tabel

[htsvhwave]

I need to make the start date and end date columns from the date column, in a new tabel. I have a table of employees and the dates on which they are absent because of sickness but I need to reduce the table so that there is no separate row for each date if that date is part of a absent period that lasts more than one day.  Furthermore i need to filter "type of absence so the new tabel only contains absence regarding sickness forexample and not vacation.

Example Input: 

Name    Date        Type of absence   
Ivana   12/25/2020  Kids are sick
Ivana   12/26/2020  Kids are sick
Ivana   12/27/2020  Kids are sick
Ivana   4/20/2020   Sick
Ivana   4/21/2020   Sick
Ivana   5/1/2020    Vacation
Jelena  4/20/2020   Vacation
Jelena  4/21/2020   Vacation

 Expected Output:

Name    Start Date  EndDate     Type of absence
Ivana   12/25/2020  12/27/2020  Kids are sick
Ivana   4/20/2020   4/21/2020   Sick
Ivana   5/1/2020    5/1/2020    Vacation
Jelena  4/20/2020   4/21/2020   Vacation

How to do it? 

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RiQUndIQmY11n3Mic?e=8LMOVL

[htsvhwave]

Thank you for the help. would it be possible for you to take screenshot at the different steps as i am quite new with power bi and want to implement the steps in my own data?

[Ahmedx]

see my video
https://1drv.ms/v/s!AiUZ0Ws7G26RiQiBLn8mE73iJOM5?e=Ioyci9

YearMonth =  Date.Year([Date])*100 + Date.Month([Date])

--------------------
{{"StartDate", (x)=> List.Min(x[Date])}, {"EndDate", (x)=> List.Max(x[Date])

[htsvhwave]

Thank you it seem to work, however in my dataset i have employees who have been on sickleave for a couple of month, now that employees absence looks like this

Name	absence	Start date	End date
Jelena	Sick	01-03-2023	31-01-2023
Jelena	Sick	03-04-2023	28-04-2023
Jelena	Sick	01-05-2023	31-05-2023

Where the wanted output should be 

Name	absence	Start date	End date
Jelena	Sick	01-03-2023	31-05-2023

[Ahmedx · Accepted solution]

pls try this

video

https://1drv.ms/v/s!AiUZ0Ws7G26RiQsBPhhEVbbfUt-F?e=uDQljZ

file

https://1drv.ms/u/s!AiUZ0Ws7G26RiQoCk9TGSqQ3xqKn?e=DuZqpo

[htsvhwave]

It has further split the dates

[Ahmedx]

first sort the table by Name and by date then repeat my video
or
Share sample pbix file to help you.

[htsvhwave]

Hi

Im still having som issues, it seem as it is grouping some dates and others not

[htsvhwave]

Thank you for the response, but i also want to see if a person is sick multiple times that it is shown in rows

Name    Start Date  EndDate     Type of absence
Ivana   12/25/2020  12/27/2020  Sick
Ivana   4/20/2020   4/21/2020   Sick
Ivana   5/1/2020    5/1/2020    Sick

[v-yueyunzh-msft]

Hi , @htsvhwave 

Thanks for your quick response!

According to your description,  You want to merge the data from top to bottom by row number, right?

This is my test data and my understanding for your end result :

We can realize it in Power Query Editor.You can create a blank query and enter this in "Advanced Editor":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixLzEtU0lEyNNI3MtU3MjAyAHK8M1OKFRKLUhWKM5OzlWJ1UJWZEafMnLAyE6ASmKpgLJKGOCVN9eFyYYnJiSWZ+Xlgea/UnFQMo3GqwG4G3BMQO4yw2Q+UwZCDm4wkiel3bKoQ1scCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, #"Type of absence" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Type of absence", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", (x)=> let absence= try #"Added Index"{x[Index]-2}[Type of absence] otherwise null in if absence =x[Type of absence] then   
1 else 0
  ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom2", (x)=>
Table.Sort(Table.SelectRows(#"Added Custom" ,(y)=>y[Index]<=x[Index] and y[Custom]=0),{"Index",1}){0}[Index]
  ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    Custom1 = Table.Group(#"Removed Columns",{"Custom2","Name","Type of absence"},{
{"Start Date",(x)=>Table.Sort(x,"Date"){0}[Date]    },{"End Date",(x)=>Table.Sort(x,{"Date",1}){0}[Date] }

}),
    #"Removed Columns1" = Table.RemoveColumns(Custom1,{"Custom2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Start Date", type date}, {"End Date", type date}})
in
    #"Changed Type1"

Then we can get this Table:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[htsvhwave]

Thank you for the help @v-yueyunzh-msft. would it be possible for you to take screenshot at the different steps as i am quite new with power bi and want to implement the steps in my own data?

[v-yueyunzh-msft]

Hi , @htsvhwave 

Of course! Thanks for your quick response!

You can first create a blank query :

Then you can go to the new blank query and you can click this in the Power Query Editor:

Then you can put this in the code content:

let
    Source = Table,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", (x)=> let absence= try #"Added Index"{x[Index]-2}[Type of absence] otherwise null in if absence =x[Type of absence] then   
1 else 0
  ),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom2", (x)=>
Table.Sort(Table.SelectRows(#"Added Custom" ,(y)=>y[Index]<=x[Index] and y[Custom]=0),{"Index",1}){0}[Index]
  ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom"}),
    Custom1 = Table.Group(#"Removed Columns",{"Custom2","Name","Type of absence"},{
{"Start Date",(x)=>Table.Sort(x,"Date"){0}[Date]    },{"End Date",(x)=>Table.Sort(x,{"Date",1}){0}[Date] }

}),
    #"Removed Columns1" = Table.RemoveColumns(Custom1,{"Custom2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{"Start Date", type date}, {"End Date", type date}})
in
    #"Changed Type1"

For the "Source= Table" the Table is your table you need to convert.

The Table is like this :

You can check the correctness of each step of this new table, maybe there is an inconsistency in our column names, you can modify it.

You can view each step of data conversion in each step in case there is an error.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[htsvhwave]

Could you step by step show how to do it

[v-yueyunzh-msft]

Hi, @htsvhwave 

Sorry, this may not be very clear to you.The best thing is that you can keep your step names the same as mine to avoid other problems.

Here are the detailed steps you can refer to :

First we need to create a blank query in Power Query Editor:

(1)The first step(Source):

= Table

(2)The second step(Added Index):

We can click the "From 1" in the Add Column Pane.

(3)The 3nd step(Added Custom):
We can click the "Custom Column" in Add Column Pane.

And we can enter this in it:

let absence= try #"Added Index"{_[Index]-2}[Type of absence] otherwise null in if absence =_[Type of absence] then   
1 else 0

(4)The 4nd step(Added Custom1):
We can click the "Custom Column" in Add Column Pane.

And we can enter this in it:

Table.Sort(Table.SelectRows(#"Added Custom" ,(y)=>y[Index]<=_[Index] and y[Custom]=0),{"Index",1}){0}[Index]

(5)The 5nd step(Removed Columns):

We can click "remove" button for the [Custom] in 4nd step, it will generate a new step automatically.

(6)The 6nd step(create a new step called "Custom1"):

We can click "Insert Step After" after the 5nd step. The default of this step is the "Custom1".

We can put this code on the M code imput above the table:

= Table.Group(#"Removed Columns",{"Custom2","Name","Type of absence"},{
{"Start Date",(x)=>Table.Sort(x,"Date"){0}[Date]    },{"End Date",(x)=>Table.Sort(x,{"Date",1}){0}[Date] }
})

(7)The 7nd step(Removed Columns1):

We can click "Remove" of the [Custom2] to remove this column in 6nd step.

(8)In the last step we can update the date type for all the columns we want to be :

I hope this makes it clear for you to show each process.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[iceparrot]

Hi @htsvhwave , 

to solve your Problem you can open the Power Query Editor.

As you said you want to create a new table:

Duplicate your existing table with the example input by right clicking on the table namen in the left column and select Duplicate. 

Afterwards select the duplicated table and select Group By in the top navigation

In the window which opens switch to Advanced and set the inputs to the following:

When you're done press enter and then Save the result in the Power Query editor.

--- 
This is what the Power Query M looks like:

= Table.Group(#"Changed Type", {"Name", "Type of absence"}, {{"Start_Date", each List.Min([Date]), type nullable date}, {"End_Date", each List.Max([Date]), type nullable date}})

This is the final result: 

Let me know if this fixes your problem. If yes, please mark it as the solution to help others as well.