Weekly Duty Schedule in PowerBI

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. R...

Need Help

Tutorial Requests

jbwtp

3 years ago

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

Memorable Member

3 years ago

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

Memorable Member

3 years ago

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

Memorable Member

3 years ago

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

Forum Discussion

Weekly Duty Schedule in PowerBI

Related content

Weekly Average

P6 Schedule into PowerBI

Scheduled pipelines failing

Schedule a CSV export weekly

PowerBI Service Scheduled refresh error for Google Analytics