Forum Discussion
Shaping data for leave projection
Hi everyone and happy new year!
I'm working on a report for projected leave balances for employees. The data that comes from our payroll system is in this format:
| Date | 15/01/2023 | 29/01/2023 | 12/02/2023 | 26/02/2023 | 12/03/2023 | 26/03/2023 | 09/04/2023 | 23/04/2023 |
| Person A | 50 | 59.205 | 68.41 | 77.615 | 86.82 | 70 | 79.205 | 88.41 |
| Person B | 27 | 36.205 | 45.41 | 54.615 | 63.82 | 55 | 64.205 | 73.41 |
| Person C | 58 | 67.205 | 76.41 | 85.615 | 94.82 | 95 | 104.205 | 113.41 |
| Person D | 22 | 31.205 | 40.41 | 49.615 | 58.82 | 68 | 77.205 | 86.41 |
| Person E | 7 | 16.205 | 25.41 | 34.615 | 43.82 | 40 | 49.205 | 58.41 |
When I try to load it to Power BI the fields display like this:
I'm guessing it needs to be in this kind of format:
| 15/01/2023 | Person A | 50 |
| 15/01/2023 | Person B | 27 |
| 15/01/2023 | Person C | 58 |
| 15/01/2023 | Person D | 22 |
| 15/01/2023 | Person E | 7 |
| 29/01/2023 | Person A | 59.205 |
| 29/01/2023 | Person B | 36.205 |
| 29/01/2023 | Person C | 67.205 |
| 29/01/2023 | Person D | 31.205 |
| 29/01/2023 | Person E | 16.205 |
In practice there are significantly more staff than this and I want to do a projection for up to 1 year so it's not practical to do this manually every time, and I'd like other people in future to be able to just add the excel file with the raw data to the source folder without having to muck around with the spreadsheet.
I want to produce a report that shows leave balances over time e.g.
How would I go about doing this? Alternatively is there a quick way in excel to change the format to the second example above that I could use?
Hi Anonymous
You can put the follwing code to "Advanced Editor" in power query and you need to change your dateformat like:"mm/dd/yyyy"
e.g: 2/28/2023
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZA5DgMhDEWvEk09QizeKLP16UdTpk2k5P5S8NhOQQPYPD382bbl8fx836/TeVkXzLr0VDOOA0mCMnbmREUbQkmqNhTjwOTA9vVvuoxm5bE0cgTQTAhuomYmPApwjNtkuioiinAgZCZBN3UwU9ei5FCVMrtuOpWSrcRU2VzQ3YViLhJL7floMt31Vt+IeNXjtYgHHg+y6Q1D+6j9Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"01/15/2023" = _t, #"01/29/2023" = _t, #"02/12/2023" = _t, #"02/26/2023" = _t, #"3/12/2023" = _t, #"3/26/2023" = _t, #"4/9/2023" = _t, #"4/23/2023" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date", "Person"}, {"Attribute", "Date"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", type number}}) in #"Changed Type1"Output:
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi Anonymous
You need to use Power Query to Pivot your columns
You can paste this to PowerQuery Advanced editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZA5DgMhDEWvEk09QizeKLP16UdTpk2k5P5S8NhOQQPYPD382bbl8fx836/TeVkXzLr0VDOOA0mCMnbmREUbQkmqNhTjwOTA9vVvuoxm5bE0cgTQTAhuomYmPApwjNtkuioiinAgZCZBN3UwU9ei5FCVMrtuOpWSrcRU2VzQ3YViLhJL7floMt31Vt+IeNXjtYgHHg+y6Q1D+6j9Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"15/01/2023" = _t, #"29/01/2023" = _t, #"12/02/2023" = _t, #"26/02/2023" = _t, #"12/03/2023" = _t, #"26/03/2023" = _t, #"09/04/2023" = _t, #"23/04/2023" = _t]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Date", "Person"}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Person"}, "Date", "Value"),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Date", type date}}, "en-GB"),
#"Changed Type with Locale1" = Table.TransformColumnTypes(#"Changed Type with Locale", {{"Value", type number}}, "en-GB")
in
#"Changed Type with Locale1"This is Unpivot other columns.
Hi Anonymous
You can put the follwing code to "Advanced Editor" in power query and you need to change your dateformat like:"mm/dd/yyyy"
e.g: 2/28/2023
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZA5DgMhDEWvEk09QizeKLP16UdTpk2k5P5S8NhOQQPYPD382bbl8fx836/TeVkXzLr0VDOOA0mCMnbmREUbQkmqNhTjwOTA9vVvuoxm5bE0cgTQTAhuomYmPApwjNtkuioiinAgZCZBN3UwU9ei5FCVMrtuOpWSrcRU2VzQ3YViLhJL7floMt31Vt+IeNXjtYgHHg+y6Q1D+6j9Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"01/15/2023" = _t, #"01/29/2023" = _t, #"02/12/2023" = _t, #"02/26/2023" = _t, #"3/12/2023" = _t, #"3/26/2023" = _t, #"4/9/2023" = _t, #"4/23/2023" = _t]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Date"}, "Attribute", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date", "Person"}, {"Attribute", "Date"}}), #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", type number}}) in #"Changed Type1"Output:
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
