Weekly Duty Schedule in PowerBI

[renny13]

Hi, how do I create a random weekly duty schdule assignment in PowerBI? Or doI have to use Excel?

I have 8 groups of people and need to asign 1 person of each group to a week of the year for duty. Random assignment that repeats until all weeks of the year are covered and to ensure that no one same person is assignted to work more than one holiday a year.

I have the dataflow of all people in PowerBI with employee ID for each and certain sort parameters. 

How do I create those 8 distinct groups and how do I assign 1 person of each group on duty on a weekly rotation?

[jbwtp]

Hi @renny13,

The description of the problem that you provided is quite unclear. For the start, do you have enough people in each group to cover all holidays in the year just once? Why do you have 8 groups and what they represent? E.g. is this just total numbr of people randomly assigned to one of each group? Or they are split by some rule?

There is a way to make sure that a person is only assigned to work on holiday once (basically, upon assignment, the person if virtually removed from a group and therefore can not be selected again), but would be great to know other details:

1. How people split to groups?

2. Do you need 8 people working on each holiday (i.e. one from each group)?

3. How do you define a holiday? Is this a statutory holiday (in some form os a dictionary table) or it includes weekends? 

I think, this is the most basics to start with. If you can answer these quesitons I can come up with a sample code, which we can start looking at.

Cheers,

John

[jbwtp]

This is the answer in a simplistic scenario. Groups are autoallocated based on the list of employees (in Source) and a number of groups (in NoOfGroups). Holidays are passed in Holidays.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6gkk3MOkOJj2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    Holidays = let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyVorViVYy0jeCMY31jcHMWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
    in
        #"Changed Type",
    NoOfGroups = 3,
    SplitToGroups = List.Skip(List.Accumulate(Table.ToRecords(#"Changed Type"), {[GroupID = 0]}, (a, n)=> a & {Record.AddField(n, "GroupID", if List.Last(a)[GroupID] = NoOfGroups then 1 else List.Last(a)[GroupID]+1 )} )),
    Main = Table.FromRecords(SplitToGroups),
    #"Grouped Rows" = Table.Group(Main, {"GroupID"}, {{"Assign", each Table.ExpandTableColumn(Table.NestedJoin(Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), {"Index"}, Table.AddIndexColumn(Holidays, "Index", 0, 1, Int64.Type), {"Index"}, "Added Index", JoinKind.LeftOuter), "Added Index", {"Date"}, {"Date"})}}),
    #"Expanded Assign" = Table.ExpandTableColumn(#"Grouped Rows", "Assign", {"ID", "Date"}, {"ID", "Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Assign",{"GroupID"}),
    Output = Table.TransformColumnTypes(#"Removed Columns",{{"ID", type text}, {"Date", type date}})
in
    Output

Cheers,

John

[jbwtp · Accepted solution]

This is the answer in a simplistic scenario. Groups are autoallocated based on the list of employees (in Source) and a number of groups (in NoOfGroups). Holidays are passed in Holidays.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6gkk3MOkOJj2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}}),
    Holidays = let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyVorViVYy0jeCMY31jcHMWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
    in
        #"Changed Type",
    NoOfGroups = 3,
    SplitToGroups = List.Skip(List.Accumulate(Table.ToRecords(#"Changed Type"), {[GroupID = 0]}, (a, n)=> a & {Record.AddField(n, "GroupID", if List.Last(a)[GroupID] = NoOfGroups then 1 else List.Last(a)[GroupID]+1 )} )),
    Main = Table.FromRecords(SplitToGroups),
    #"Grouped Rows" = Table.Group(Main, {"GroupID"}, {{"Assign", each Table.ExpandTableColumn(Table.NestedJoin(Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), {"Index"}, Table.AddIndexColumn(Holidays, "Index", 0, 1, Int64.Type), {"Index"}, "Added Index", JoinKind.LeftOuter), "Added Index", {"Date"}, {"Date"})}}),
    #"Expanded Assign" = Table.ExpandTableColumn(#"Grouped Rows", "Assign", {"ID", "Date"}, {"ID", "Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Assign",{"GroupID"}),
    Output = Table.TransformColumnTypes(#"Removed Columns",{{"ID", type text}, {"Date", type date}})
in
    Output

Cheers,

John