Excel: pivot ungroup date and compare data by days

Question

I have pretty much understood how group and ungroup data works in excel but one thing is not very clear: I have a table like this:

Date	Login
01/01/2021	550
02/01/2021	687
03/01/2021	781
---	---
01/03/2021	478
02/03/2021	925
---	---

I would like to turn this into a pivot in order to compare the number of login same days in different months; so my ideal output should be

Day	Jan	Feb	March
01	550	717	478
02	687	397	925
03	781	714	397
---	---	---	---

But, if I use the grouping proprieties of pivot table the days refears to their specific months so I don't have only 31 rows but I have as many rows as the original table

Day	Jan	Feb	March
01-jan	550
02-jan	687
03-jan	781
---	---	---	---
01-mar			478
02-mar			925
---	---	---	---

Can anyone help me please? Thank you

horseyride · Answer 1 · 2023-05-10T18:43:38.543

You could bring the data in powerquery with data ... from table/range

add column .. custom column ... with formula

=Date.MonthName([Date])

add column .. custom column ... with formula

=Date.Day([Date])

Right click, remove, the original date column

Click select the month name column, transform ... pivot column ... choose Login column for values and use default sum operation

File .. close and load ... to get it back to excel as a table or as a pivot table. Right click refresh when needed

Sample code below, which can be pasted in PowerQuery into home...advanced editor

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Month", each Date.MonthName([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Day", each Date.Day([Date])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Date"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Month]), "Month", "Login", List.Sum)
in  #"Pivoted Column"

Excel: pivot ungroup date and compare data by days

1 Answers1