group Leave Dates for Emp By Date Sequence

[mhrkhader]

Dear Team,

Greeting.

i have excel sheet with trhe following Data

EmpID      LeaveDate  

E001         12 Mar 2019

E001         13 Mar 2019

E001         15 Mar 2019

E001         17 Apr 2019

E001         18 Apr 2019

E002       12 Mar 2019

E002       15 Mar 2019

E002        22 May 2019

E002        23  May 2019

but the result we want in PowerQuery is

EmpID      LeaveDate          Leave To
E001         12 Mar 2019      13 Mar 2019
E001         15 Mar 2019      15 Mar 2019
E001         17 Apr 2019      18 Apri 2019
E002       12 Mar 2019       12 Mar 2019
E002       15 Mar 2019       15 Mar 2019
E002        22 May 2019      23  May 2019

how to achive this based on dates Seq using PowerQuery? if the Dates in a Seq for a Particular Emp, LEave Start Date and End Date shoud be assumed that the Leave Dates between Start and End and anything between

thanks

regards

Maher

[Greg_Deckler]

@ImkeF  generally has tricks for this kind of thing.

[ImkeF]

Hi @mhrkhader  

I don't understand your explanation of the pattern to be used here. Please try to answer my questions below:

[mhrkhader]

as your question you asked why we are not creating from 12 to 15 March, but the emp apply on leave on 12 and then resume work on 13 and 14 and then apply for leave on 15 march. so in this scenario i will make 2 entries. one for 12 March and one for 15 March

[ImkeF · Accepted solution]

Thanks @mhrkhader ,

that's clear now and makes sense.

I've created an Index for the sequences with List.Accumulate:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTW9U0sUorVATJNEUwzBNMSzjQyQjAR2oxMEExzBBOhzdgAwowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    TransformToList = #"Changed Type"[Date],
    AddSequenceIndex = List.Accumulate(
        TransformToList, 
        {[Value = List.First(TransformToList), Index = 0]}, 
        (state, current) => if current - List.Last(state)[Value] = #duration(1,0,0,0) 
                                then state & { [Value = current, Index = List.Last(state)[Index] ] } 
                                else state & { [Value = current, Index = List.Last(state)[Index] + 1 ] }
    ),
    #"Converted to Table" = Table.FromList(AddSequenceIndex, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Value", "Index"}, {"Value", "Index"}),
    #"Grouped Rows" = Table.Group(#"Expanded Column1", {"Index"}, {{"Partition", each _, type table [Value=date, Index=number]}}, GroupKind.Local),
    SkipSeed = Table.SelectRows(#"Grouped Rows", each ([Index] <> 0)),
    FirstElementAsStart = Table.AddColumn(SkipSeed, "Leave Date", each Table.First([Partition])[Value]),
    LastElementAsEnd = Table.AddColumn(FirstElementAsStart, "Leave To", each Table.Last([Partition])[Value]),
    Cleanup = Table.RemoveColumns(LastElementAsEnd,{"Partition"})
in
    Cleanup

Paste that into the advanced editor or check the file enclosed:

[mhrkhader]

hi imkef,

i tried but it is not working.

i attached the file for your reference. 

https://we.tl/t-yje6M3rBty

i have the following code, where to past and what changes do i have to make for current code so it will work.

the code is 

***************

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(Source, {"Code"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns1",{{"Attribute", "Date"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] = "AB")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Code", Order.Ascending}, {"Date", Order.Ascending}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Sorted Rows", {{"Date", type date}}, "en-GB"),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type with Locale",{{"Date", "From Date"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Value"})
in
#"Removed Columns"

******************

mainly now i have 2 Columns (Code, Leave Date)

thanks

[ImkeF]

Hi @mhrkhader 

sorry, forgot to mention that you have to transform this into a function that you have to apply on each emplyees-figures. Please check workbook enclosed.

[mhrkhader]

Hi Imkef,

Greetings

lets take this example

emp ID is E001 apply for leave for Dates 13,15,16,19,22,23,24,27,29,30 March

the report should show this

EID	Date
E001	13 march
E001	15 March
E001	16 March
E001	19 March
E001	22 March
E001	23 March
E001	24 March
E001	27 March
E001	29 March
E001	30 March

the report should show Dates from Start to End if they are in Seqence with increment of 1 but if there is a Gap of more than 1 day then we should split the imtervals. her is the result

EID	Start Date	End Date	Note
E001	13 March 2019	13 March 2019	her becase this date is one day leave without any consective date after we will make this date same in both columns
E001	15 March 2019	16 March 2019
E001	19 March 2019	19 March 2019
E001	22 March 2019	24 March 2019
E001	27 March 2019	27 March 2019
E001	29 March 2019	30 March 2019

thanks for your support Imkef and thanks for all who replied. please check and let me know.