Search code examples
powerquerym

Power query - Group a table based on the date and the hourly interval

I need to group a table based on the date and the hourly interval, using the Sum:

  • Date
  • Interval: from 8am today to <8am today+1

Previously I was using MS Access and a query to create it. Now I need to go through Power Query in MS Excel.

That was the SQL Query used before: SELECT switch(Tbl_Prod_Chat.[Interval]>=8,Tbl_Prod_Chat.[Date],Tbl_Prod_Chat.[Interval]<8,Tbl_Prod_Chat.[Date]-1) AS LINK_DATE, Tbl_Prod_Chat.Agent, Sum(Tbl_Prod_Chat.ProdChat) AS Prod_Chat FROM Tbl_Prod_Chat GROUP BY Switch(Tbl_Prod_Chat.[Interval]>=8,Tbl_Prod_Chat.[Date],Tbl_Prod_Chat.[Interval]<8,Tbl_Prod_Chat.[Date]-1), Tbl_Prod_Chat.Agent;

The table is built as:

  • Field 1 "Date" (type/format: mm/dd/yyyy)
  • Field 2 "Interval" (type: whole number): 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 0
  • Field 3 "Volume of contact" (type: whole number)

The new table would be:

  • Field 1 "Date"
  • Field 2 "Total Volume" (sum on 24h from 8am toady to <8am Today+1).

Can you please help me on this? Thanks

Seb

Solution

  • Sounds like you just need to add a single custom column

    add column .. custom column...

    =  if [Interval] >7 or [Interval]=0 then [Date] else Date.AddDays([Date],-1)

    or

    = if [Interval] <8 and [Interval] > 0 then Date.AddDays([Date],-1) else [Date]

    That will take all hours [8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,0] and use the current date and will take all hours [1,2,3,4,5,6,7] from the next date.

    Then right click ... Group By .. on that new custom column and do operation Sum on Column: Volume of Contact, with whatever name you want in New Column Name

    sample full code

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Interval", Int64.Type}, {"Volume of contact", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Interval] >7 or [Interval]=0 then [Date] else Date.AddDays([Date],-1), type date),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Volume of Contact", each List.Sum([Volume of contact]), type number}})
in #"Grouped Rows"