Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have an Excel spreadsheet as an input file with the following data:
Project Name | 01/01/2020 | 02/01/2020 | 03/01/2020 | 04/01/2020 | 05/01/2020 | 06/01/2020 | 07/01/2020 | 08/01/2020 | 09/01/2020 | 10/01/2020 |
Project 1 | 8 | 8 | 8 | 4 | 4 | 4 | ||||
Project 2 | 4 | 4 | 4 | 8 | 8 |
These data are representing a project workload per day / per project.
How can I proceed to summarize my data per week or month (but still per project)?
Thanks in advance for your help
Solved! Go to Solution.
Hi @fslef ,
Yes, as @Martin1986 mentioned, you could try to use M code to convert table, then calculate it. You could try below M code and refer to my sample for details.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRskDCJkCsAMUmKPxYHYQuIyRVCjh0QUyMjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"01/01/2020" = _t, #"02/01/2020" = _t, #"03/01/2020" = _t, #"04/01/2020" = _t, #"05/01/2020" = _t, #"06/01/2020" = _t, #"07/01/2020" = _t, #"08/01/2020" = _t, #"09/01/2020" = _t, #"10/01/2020" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"01/01/2020", Int64.Type}, {"02/01/2020", Int64.Type}, {"03/01/2020", Int64.Type}, {"04/01/2020", Int64.Type}, {"05/01/2020", type text}, {"06/01/2020", type text}, {"07/01/2020", Int64.Type}, {"08/01/2020", Int64.Type}, {"09/01/2020", Int64.Type}, {"10/01/2020", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Name"}, "Attribute", "Value"),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Attribute", type date}}, "aa-DJ"),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "week", each Date.WeekOfYear([Attribute])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "month", each Date.Month([Attribute]))
in
#"Added Custom1"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @fslef ,
Yes, as @Martin1986 mentioned, you could try to use M code to convert table, then calculate it. You could try below M code and refer to my sample for details.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRskDCJkCsAMUmKPxYHYQuIyRVCjh0QUyMjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"01/01/2020" = _t, #"02/01/2020" = _t, #"03/01/2020" = _t, #"04/01/2020" = _t, #"05/01/2020" = _t, #"06/01/2020" = _t, #"07/01/2020" = _t, #"08/01/2020" = _t, #"09/01/2020" = _t, #"10/01/2020" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"01/01/2020", Int64.Type}, {"02/01/2020", Int64.Type}, {"03/01/2020", Int64.Type}, {"04/01/2020", Int64.Type}, {"05/01/2020", type text}, {"06/01/2020", type text}, {"07/01/2020", Int64.Type}, {"08/01/2020", Int64.Type}, {"09/01/2020", Int64.Type}, {"10/01/2020", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project Name"}, "Attribute", "Value"),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Attribute", type date}}, "aa-DJ"),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "week", each Date.WeekOfYear([Attribute])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "month", each Date.Month([Attribute]))
in
#"Added Custom1"
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, go to the Query Editor for this table, then select the Project column and go to Transform > Unpivot Columns > Unpivot Other Columns. This gives you an attribute column (which you can rename to Date and also convert to datetype) and a Value column.
After this you can simply summarize the Value column.