Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
90 | |
82 | |
62 | |
61 | |
58 |
User | Count |
---|---|
159 | |
114 | |
100 | |
75 | |
65 |