Forum Discussion

Regular Visitor

3 years ago

Solved

Find continuous shifts worked using Index

I am working on a shift Data where I need to find continuous shifts. There are instances when an employee has worked two shifts on same day. I want to create an index column where if previous shift start date = current shift start date then index should remain same other wise add 1. Like index column below:

UserID	StartTime	FinishTime	Index
1	3/07/2022	3/07/2022	1
1	3/07/2022	4/07/2022	1
1	4/07/2022	5/07/2022	2
1	9/07/2022	9/07/2022	3
1	9/07/2022	10/07/2022	3
1	11/07/2022	11/07/2022	4
1	11/07/2022	12/07/2022	4
1	30/07/2022	31/07/2022	5
1	31/07/2022	31/07/2022	6
1	1/08/2022	1/08/2022	7
1	2/08/2022	2/08/2022	8
1	6/08/2022	6/08/2022	9
1	7/08/2022	7/08/2022	10
1	8/08/2022	8/08/2022	11
1	9/08/2022	9/08/2022	12
1	9/08/2022	9/08/2022	12
1	26/08/2022	27/08/2022	13
1	27/08/2022	28/08/2022	14
1	29/08/2022	29/08/2022	15
1	29/08/2022	29/08/2022	15
1	23/09/2022	24/09/2022	16
1	24/09/2022	25/09/2022	17
1	26/09/2022	26/09/2022	18

Can anyone help please?

Anonymous

Please follow the below steps:

1. Group by the [UserID] and [StartTime]:

2. Add a index column:

3. Expand [FinshTime]:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldJLCsAgDATQu7gWNFO/ZxHvf426qQlJK3QXeZhMxDEcOe9quAIiIMvpXyxpS2xZW2frB6OokUgoHRUmbBSbmLsXfWsLG3fJBjZoK2xFW2Wr2hpb09bZ+h+DCAMzESIOzEyIxrCdD7qO+5esOhlNQrPRIvR5wXkD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserID = _t, StartTime = _t, FinishTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", Int64.Type}, {"StartTime", type date}, {"FinishTime", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"UserID", "StartTime"}, {{"Data", each _, type table [UserID=nullable number, StartTime=nullable date, FinishTime=nullable date]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Index", "Data", {"FinishTime"}, {"FinishTime"})
in
    #"Expanded Data"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

2 Replies

SamInogic
Super User
3 years ago
Hi Mkaur4360,

Power BI provides RANKX function to add a indexing within the table, so you can use below DAX expression to create a Index Custom column in your table,

Index User Day Shift =
RANKX(ALL('User Shifts'), 'User Shifts'[Start Time],,ASC,Dense)

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!
Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at [email protected]
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/

Anonymous

Not applicable

Please follow the below steps:

1. Group by the [UserID] and [StartTime]:

2. Add a index column:

3. Expand [FinshTime]:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldJLCsAgDATQu7gWNFO/ZxHvf426qQlJK3QXeZhMxDEcOe9quAIiIMvpXyxpS2xZW2frB6OokUgoHRUmbBSbmLsXfWsLG3fJBjZoK2xFW2Wr2hpb09bZ+h+DCAMzESIOzEyIxrCdD7qO+5esOhlNQrPRIvR5wXkD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UserID = _t, StartTime = _t, FinishTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UserID", Int64.Type}, {"StartTime", type date}, {"FinishTime", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"UserID", "StartTime"}, {{"Data", each _, type table [UserID=nullable number, StartTime=nullable date, FinishTime=nullable date]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded Data" = Table.ExpandTableColumn(#"Added Index", "Data", {"FinishTime"}, {"FinishTime"})
in
    #"Expanded Data"

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum