Consolidate columns date values into a report as per period considering category variable

[Luthius]

Hello guys
I need to consolidate values distributed underneath columns represented by Days.

I just need the consolidation of these values by Category as per period (Week, Month and Year).
Due the quantity of information in my file, using a formula is not feasible. The file has +2k columns and +27k rows.

Column A	Column B	Column C	Column D	Column E	Column F	Column G	Column H	(...)
Id	Category	01/01	01/02/2020	01/03/2020	01/04/2020	01/05/2020	01/06/2020	(...)
1	A	$124	$230	$31	$43	$21	$190	$
2	B	$250	$18	$35	$178	$420	$221	$
3	C	$30	$75	$470	$12	$340	$110	$

I tried using pivot tables, but it recognize the column dates as a field instead as a date.
The final report will be something like below. Where the first column either is Week or Month as per year.

Year	Category A	Category B	Category C
Wk#1	$	$	$
Wk#2	$	$	$
Wk#3	$	$	$
(...)	$	$	$

 

[CA_Punit]

Please see post 19. I suppose i have have solved your probelm for dates. Please check it

 

[sandy666]

you didn't show what exactly you want to achieve
btw. year month and week you can create directly from Attribute column without duplicate this column

 

[Luthius]

you didn't show what exactly you want to achieve
btw. year month and week you can create directly from Attribute column without duplicate this column

Really? I thought we can only do it an once. We cannot have the 3 different ones (Year/Month/Week) without duplicating some column. How to do that?

 

[Luthius]

Please see post 19. I suppose i have have solved your probelm for dates. Please check it

It solved in deed. Thanks

 

[sandy666]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Category"}, "Attribute", "Value"),
    #"Parsed Date" = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each Date.From(DateTimeZone.From(_)), type date}}),
    #"Inserted Year" = Table.AddColumn(#"Parsed Date", "Year", each Date.Year([Attribute]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Attribute], "en-US"), type text),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Month Name", "Week of Year", each Date.WeekOfYear([Attribute]), Int64.Type)
in
    #"Inserted Week of Year"

 

[sandy666]

and without pivot

s666-PQ-luthius3.xlsx

Microsoft Excel Workbook

1drv.ms