Forum Discussion
How to create a shift rotation schedule in Power Bi?
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.
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).
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 😄
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).