cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mkaur4360
Regular Visitor

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:

UserIDStartTimeFinishTimeIndex
13/07/20223/07/20221
13/07/20224/07/20221
14/07/20225/07/20222
19/07/20229/07/20223
19/07/202210/07/20223
111/07/202211/07/20224
111/07/202212/07/20224
130/07/202231/07/20225
131/07/202231/07/20226
11/08/20221/08/20227
12/08/20222/08/20228
16/08/20226/08/20229
17/08/20227/08/202210
18/08/20228/08/202211
19/08/20229/08/202212
19/08/20229/08/202212
126/08/202227/08/202213
127/08/202228/08/202214
129/08/202229/08/202215
129/08/202229/08/202215
123/09/202224/09/202216
124/09/202225/09/202217
126/09/202226/09/202218

 

Can anyone help please?

 

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @Mkaur4360 ,

 

Please follow the below steps:

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

vcgaomsft_0-1674545253258.png

2. Add a index column:

vcgaomsft_1-1674545285020.png

3. Expand [FinshTime]:

vcgaomsft_2-1674545322516.png

vcgaomsft_3-1674545370360.png

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

View solution in original post

2 REPLIES 2
v-cgao-msft
Community Support
Community Support

Hi @Mkaur4360 ,

 

Please follow the below steps:

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

vcgaomsft_0-1674545253258.png

2. Add a index column:

vcgaomsft_1-1674545285020.png

3. Expand [FinshTime]:

vcgaomsft_2-1674545322516.png

vcgaomsft_3-1674545370360.png

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
Solution Sage
Solution Sage

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)

SamInogic_1-1674195786613.png


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/

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors