Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Exactly what I had in mind. 😊
Really helpful example, cheers very much!
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
Proud to be a Datanaut!
Hi @V_M_0451 ,
If you find @BA_Pete 's response as a solution, please accept it as such.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |