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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Super User
Super User

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors