Sum the set of records of identical name IDs in first column across time

[Anonymous]

Hi All, 
Trust you are well!

I am new to this forum and I have searched for similar approaches to the issue below I am facing but could not find it here!

It goes like transforming the first table below into the second table using Power Query or M Languange.

it basically sums all the values or records from second column onwards (time frame) of the name identifiers A, B, C, and so on.

Your feedback, suggestion is very much appreciated!

FROM THIS
Date	01-Jan	02-Jan	03-Jan	04-Jan	05-Jan	06-Jan	07-Jan	08-Jan	09-Jan	10-Jan	11-Jan	12-Jan	13-Jan	14-Jan	15-Jan	etc
A	1	1	1	1	1	1	1	1	1	1	1	1
A													1	1	1	1
B							1	1	1	1	1	1	1	1	1	1
B													1	1	1	1
B	1	1	1	1	1	1	1	1	1	1	1	1
C	1	1	1	1	1	1	1	1	1	1	1	1
C													1	1	1	1
C							1	1	1	1	1	1	1	1	1	1
C
D	1	1	1	1	1	1	1	1	1	1	1	1
etc
TO THIS
Date	01-Jan	02-Jan	03-Jan	04-Jan	05-Jan	06-Jan	07-Jan	08-Jan	09-Jan	10-Jan	11-Jan	12-Jan	13-Jan	14-Jan	15-Jan	etc
A	1	1	1	1	1	1	1	1	1	1	1	1	2	2	2	2
B							1	1	1	1	1	1	2	2	2	2
C	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
D	1	1	1	1	1	1	1	1	1	1	1	1
etc

[Anonymous]

That has worked fantastically! thanks

[AlexisOlson · Accepted solution]

Unpivot and the re-pivot using sum as the aggregation.

Select the Date column and click Unpivot Columns > Unpivot Other Column in the Transform tab:

Select the Attribute column and click on Pivot Column. Choose the Value column as the Values Column.

Result:

Sample M query you can paste into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTKEYgWlWB2IiAIUG4JFnIgUQTXHGasIqi78IhBdLkjmKMXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"01-Jan" = _t, #"02-Jan" = _t, etc = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"01-Jan", Int64.Type}, {"02-Jan", Int64.Type}, {"etc", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"