Find continuous shifts worked using Index

[Mkaur4360]

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?

[v-cgao-msft · Accepted solution]

Hi @Mkaur4360 ,

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

[SamInogic]

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 crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/