Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
SR_dataguy
New Member

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

I've a excel table as shown below.

SR_dataguy_0-1710514369084.png

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

SR_dataguy_1-1710514411736.png

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.

 

SR_dataguy_2-1710514548283.png

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

1 ACCEPTED SOLUTION
slorin
Super User
Super User

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

View solution in original post

5 REPLIES 5
slorin
Super User
Super User

Thank you.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

slorin
Super User
Super User

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

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Thank you. It worked.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors