I want to get Resource and Leave Date combination based on continuity

[SR_dataguy]

I've a excel table as shown below.

Now, I want o transform it as this using power query:

For each resource, if leave date is not continous, it should giver same date as start and end date and if it is continous, it should give Min and max dates as start and end dates.

I tried somethng in Power query

Code is:

let
// Load your data into Power Query
YourData = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

// Convert data types if needed
#"Changed Type" = Table.TransformColumnTypes(YourData, {{"Res ID", type text}, {"Leave Date", type date}}),

// Sort the data by Res ID and leave dates
sortedData = Table.Sort(#"Changed Type", {{"Res ID", Order.Ascending}, {"Leave Date", Order.Ascending}}),

// Add an index column
addIndex = Table.AddIndexColumn(sortedData, "Index", 0, 1, Int64.Type),

// Add a custom column to identify the start of a new continuous leave period for each resource
addStartFlag = Table.AddColumn(addIndex, "Start Flag", each if [Index] = 0 or [Res ID] <> Table.Column(addIndex, "Res ID"){[Index] - 1} then 1 else 0, type logical),

// Add a custom column to identify the continuous leave period index for each resource
addPeriodIndex = Table.AddColumn(addStartFlag, "Period Index", each List.PositionOf(List.Select(addStartFlag[Start Flag], each _ = 1), 1), Int64.Type),

// Group the data by Res ID and the continuous leave period index
groupedData = Table.Group(addPeriodIndex, {"Res ID", "Period Index"}, {{"Start Date", each List.Min([Leave Date]), type date}, {"End Date", each List.Max([Leave Date]), type date}}),

// Remove the continuous leave period index column
#"Removed Columns" = Table.RemoveColumns(groupedData, {"Period Index"})
in
#"Removed Columns"

But it is giving unique Res ID and Min and Max dates as below.

Can anyone help where I'm going wrong to get the desired result as said above?

[slorin]

Hi @dufoq3 

@ImkeF explains the 5th element of Table.Group here :

https://www.thebiccountant.com/2018/01/21/table-group-exploring-the-5th-element-in-power-bi-and-powe...

Stéphane 

[dufoq3]

Thank you.

[slorin · Accepted solution]

Hi @SR_dataguy 

use Table.Group with GrouKind.Local

let
// Load your data into Power Query
YourData = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

// Convert data types if needed
#"Changed Type" = Table.TransformColumnTypes(YourData, {{"Res ID", type text}, {"Leave Date", type date}}),

// Sort the data by Res ID and leave dates
sortedData = Table.Sort(#"Changed Type", {{"Res ID", Order.Ascending}, {"Leave Date", Order.Ascending}}),

// Add an index column
addIndex = Table.AddIndexColumn(sortedData, "Index", 0, 1, Int64.Type),

Group = Table.Group(
    addIndex,
    {"Res ID", "Leave Date", "Index"},
    {{"Start_Date", each List.Min(_[Leave Date]), type date},
     {"End_Date", each List.Max(_[Leave Date]), type date}},
    GroupKind.Local,
    (x,y) => Byte.From( (y[Index]-x[Index]) <> Duration.Days(y[Leave Date]-x[Leave Date]))
)

in Group

 Stéphane

[dufoq3]

Hi @slorin, could you explain this solution please? I saw you to use it few times, but I sill don't understand it at all. Thank you in advance.

[SR_dataguy]

Thank you. It worked.