How to create a shift rotation schedule in Power Bi?

[OmtaBeginner1]

Hi ,

I got an assignment from a section in my company where they wanna track each shifts StopDurationTime and cause of it. The purpose is to get an overview of causes for each shift. The overview will help us understand what causes we have to analyze for each shift and hopefully we can try to reduce the StopDurationTime. 

Ill give you a backround:

We have 3 shifts:

1. Shift 1 
2. Shift 1:2
3. Shift 2

So basically,
Shift 2 only works Day and Evening - (06:30AM - 15.06 PM) and (15.06 PM - 23.42 PM). 
Either Shift 1 or Shift 1:2 will work night time (23.42 PM - 06:30 AM) for 4 weeks straight. 
For an example: If Shift 1 work night time (4 weeks straight), Shift 1:2 vill join Shift 2 to cover either day och evening shifts.  

Since the year started (Week 1) we have a rotation schedule looking like this and it will continue liks this through this year: 

How can I Create a pattern in Power Query? 
Should I create a loop or is there another way of doing it?

I've created a function where = 

let
varTime = DateTime.Time([Starttid])
in
if varTime >= #time(06,30,0) and varTime < #time(15,05,59) then "FM-Skift"
else if varTime >= #time(15,06,0) and varTime < #time(23,41,59) then "EM-Skift"
else "Natt-Skift" 

This function divides the 24 hours into different shifts during the day - In this case FM (Day) , EM (Evening) , Natt (Night)
I want to replace FM,EM,NATT to each shifts (Shift 1, Shift 1:2, Shift 2) related to the rotation.

[AlexisOlson · Accepted solution]

I think the simplest method would be to set up your pattern and use Table.Repeat to copy it as many times as you want.

I filtered to the first 8 rows and removed the Week column before repeating and then added back the Week column by defining a new index column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOyEwrUTC0MkKw4SwjpVidaCUjrKqQ1YNUGRNllglRZpli0Y+qHqTKDKstyOpBqsyJMsuCKLMsifKjoQFRnjQkLvQNiQj+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, Night = _t, Evening = _t, Day = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Week] <= 8),
    #"Repeated Table" = Table.Repeat(Table.RemoveColumns(#"Filtered Rows",{"Week"}), 7),
    #"Added Index" = Table.AddIndexColumn(#"Repeated Table", "Week", 1, 1, Int64.Type)
in
    #"Added Index"

Repeating 7 times (as above) returns 56 rows. You can trim off extra rows or repeat more time as you see fit.

[OmtaBeginner1]

Thanks so much for the help!

It worked extremly well! 

[AlexisOlson · Accepted solution]

I think the simplest method would be to set up your pattern and use Table.Repeat to copy it as many times as you want.

I filtered to the first 8 rows and removed the Week column before repeating and then added back the Week column by defining a new index column:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrOyEwrUTC0MkKw4SwjpVidaCUjrKqQ1YNUGRNllglRZpli0Y+qHqTKDKstyOpBqsyJMsuCKLMsifKjoQFRnjQkLvQNiQj+WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week = _t, Night = _t, Evening = _t, Day = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Week] <= 8),
    #"Repeated Table" = Table.Repeat(Table.RemoveColumns(#"Filtered Rows",{"Week"}), 7),
    #"Added Index" = Table.AddIndexColumn(#"Repeated Table", "Week", 1, 1, Int64.Type)
in
    #"Added Index"

Repeating 7 times (as above) returns 56 rows. You can trim off extra rows or repeat more time as you see fit.

[OmtaBeginner1]

I've been trying to figure out how to manage this code related to my data. Is there a possibility you could simplify this example a litte more? For an example, the long green text - is that a source? And where do i put this code - Power Bi or the Query? 

// Noob 😄  

[AlexisOlson · Accepted solution]

Create a new blank query (or duplicate any existing query) and open the advanced editor and replace all the text with the M code I provided. This will recreate exactly what I did and you can walk through the applied steps.

Instead of connecting to a source, I use the Enter Data tool. The long green text is that entered data in a compressed format.

To apply to your data, you're essentially trying to recreate the last three steps (Filtered Rows, Repeated Table, and Added Index).