Staffing report status by week

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