Summarize date with input data = 1 collumn per day

[fslef]

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

[dax · Accepted 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.

[Martin1986]

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.