Forum Discussion
Microsoft EmployeeSummarize date with input data = 1 collumn per day
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
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
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.
daxCommunity Support
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
