Staffing report status by week

[V_M_0451]

Hi, I'm a bit of a beginner to PBI and am trying to figure out how to make something useful out of our staffing reports.

I have a master spreadsheet from each team with a column of staff names, then a coumn for each week with the values therein being a status of what that person was doing that week (on leave, sick, redeployed etc). These values vary a bit from team to team so I will probably need a helper column to standardise them. I have also taken all the seperate team master sheets and merged them all into a single one with the team name in another column.

What I am intending to do is display this in PBI in such a way that I can see how many times statuses comes up each week, how that changes over time, and a way to compare between teams (either a slicer or drillthrough to a another page perhaps). So far however PBI seems resistant to using the weeks as an axis (probably as they're only headers with other values below. I have tried making helper columns with that date down each week next to that status, but think I may need to consolidate them all into a single long column with all the dates running top to bottom, all the statuses in a column next to them, and team names also. Bit messier as a spreadsheet but PBI might read it more easily?

I feel like I am missing a more elegant solution though. Any guidance would be appreciated!

[BA_Pete · Accepted solution]

Hi @V_M_0451 ,

You're thinking along the right lines here. This really needs to be done in Power Query as follows:

Your department sheet 1:

// jTable1 - example of dept spreadsheet
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVfBbsIwDP2XnjnEbmI3RzbEmAS7MgnxB2i3/f+SUqZSuTRt7UOMqoRX+9l+cS+XKjhHXG2q8/7wwn6dd/927Mzb88HrpgWP9Urw7VHGZmAX0vP754hZ6DNDTWhFiG/YrSVk3HNXY5wEXJxKoEz3Vlh9yOGe5Cc6xklPSnwW0xfrNn3CevLzONgUsShCAdZwT8xNZK8EhY6aSWrG6OtFP5LlrAbf3dKrTQixsSr8JDNoBs4OWW5Zpa6KPb5XdFWGavqpW1FhgLSkWIdU3LGSTk/W4xLbZZ6jmqdJO9Lz6WPE9P+0P02Ye6diq0V6iDX4Nt65KizH65HVuHMhR3oQVv/8q70HFiEHUvMrRDWsGNqmWC0yErj30Tfdy1cnFruRTiFoQgSzaYgMZTvJoTdzPIkazBvjhoUvTy4zqS67FUCeCXU4Rp2WkC/1YDcqp2tXnesk9wSWXDszOtJFNc11iRW7PFrS4upgBU4ugNL8n/z0JZ8lRVBA+U4r+/AtwuMc5c/v7Wb581Blm1q4/gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Ref No." = _t, #"30/03/2020" = _t, #"31/03/2020" = _t, #"01/04/2020" = _t, #"02/04/2020" = _t, #"03/04/2020" = _t, #"04/04/2020" = _t, #"05/04/2020" = _t, #"06/04/2020" = _t, #"07/04/2020" = _t, #"08/04/2020" = _t, #"09/04/2020" = _t, #"10/04/2020" = _t, #"11/04/2020" = _t, #"12/04/2020" = _t]),
    chgAllTypes = Table.TransformColumnTypes(Source,{{"Ref No.", type text}, {"30/03/2020", type text}, {"31/03/2020", type text}, {"01/04/2020", type text}, {"02/04/2020", type text}, {"03/04/2020", type text}, {"04/04/2020", type text}, {"05/04/2020", type text}, {"06/04/2020", type text}, {"07/04/2020", type text}, {"08/04/2020", type text}, {"09/04/2020", type text}, {"10/04/2020", type text}, {"11/04/2020", type text}, {"12/04/2020", type text}})
in
    chgAllTypes

Your department spreadsheet 2:

// jTable2 - example of dept spreadsheet
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tVbJbsMgEP2VyuceZgEGjmmqNJWaXqwqhyh/EPXW/6+Js9mBBJzJYQYZhuc3MAubTePIeGpem+/1e6fXI+ln02tvy4HB9nXToHgO3ffs63EsQww8idi8vVARStCQ6T7jVPvykIpwFpAj3Pwzo+56+vu32x2HnqBDmzAuOrYxmAVAmQo2vANBa11cXixv6AFoxibjdvCTmV6DGRY9rsODIEcUFC9IvJ7bHFAPLAQYncalzWm4RE4aHIb0X4DklI7VEhEcu2CfGZcgoBiXFAtstlpUqGP1UUuaLhTdiZyCq9aplTEh9LH+tz+tsu4TmkkxB0lIC8wAgtNjFmxktvrIqGpENor0HANOBhtVaM8Qi8pTyj8KSgzsWUJqWyqgcaNtw83XK2OrJGz3jitgOF5LNn0BuBUzZ7VY3VGHgrX3WOPwsGudmXg5SpGHwYMOo64TWcxA5RgmYBiCLamcBc45CiQTnDtLH0+yr745IjlS/UyKFlgueUGVeAghlMR6UTayz72eKgLKoUGj45xQH+IKziFbn2N1Xw7PA67pkjcvTYr6RlH6kguZI6rIOUciNQ+zW3Dbfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Ref No." = _t, #"30/03/2020" = _t, #"31/03/2020" = _t, #"01/04/2020" = _t, #"02/04/2020" = _t, #"03/04/2020" = _t, #"04/04/2020" = _t, #"05/04/2020" = _t, #"06/04/2020" = _t, #"07/04/2020" = _t, #"08/04/2020" = _t, #"09/04/2020" = _t, #"10/04/2020" = _t, #"11/04/2020" = _t, #"12/04/2020" = _t]),
    chgAllTypes = Table.TransformColumnTypes(Source,{{"Ref No.", type text}, {"30/03/2020", type text}, {"31/03/2020", type text}, {"01/04/2020", type text}, {"02/04/2020", type text}, {"03/04/2020", type text}, {"04/04/2020", type text}, {"05/04/2020", type text}, {"06/04/2020", type text}, {"07/04/2020", type text}, {"08/04/2020", type text}, {"09/04/2020", type text}, {"10/04/2020", type text}, {"11/04/2020", type text}, {"12/04/2020", type text}})
in
    chgAllTypes

Main query, appended and transformed:

// jTable
let
    Source = Table.Combine({jTable1, jTable2}),
    unpivotOtherCols = Table.UnpivotOtherColumns(Source, {"Ref No."}, "Attribute", "Value"),
    renCols = Table.RenameColumns(unpivotOtherCols,{{"Attribute", "workDate"}, {"Value", "workStatus"}})
in
    renCols

1. Go into Power Query > New Source > Blank Query, then go to Advanced Editor and paste the first code in. Call this query jTable1.

2. Follow step 1 again but using the second code. Call this jTable2.

3. Same again, but now with the third section of code. You will now see all the steps I took to append the 'dept spreadsheets' together, and unpivot them into a usable table structure for your requirements.

Obviously where I have [Ref No.] you will have employee name, and where I have the date columns, yours will be weeks, but the principles and steps will be the same.

Pete

[V_M_0451]

Exactly what I had in mind. 😊

Really helpful example, cheers very much!

[BA_Pete]

Hi @V_M_0451 ,

No problem, happy it's helped.

Please mark my answer as the solution if it's done what you needed.

Thanks,

Pete

[danextian]

Hi @V_M_0451 ,

If you find @BA_Pete 's response as a solution, please accept  it as such.