PowerQuery grouping dates and rows by criteria-CodePudding

I have an extract from a much larger dataset which I'm trying to group.

The data is shown below and available here.

What I'm trying to achieve is:

Merge all rows based on 'Product_Type'
Sum all columns in blue and produce an average for the ones in green.
Remove unwanted columns (Time & Resource)
Merge rows that have the value 'Mortgages' in the column 'Product'
Merge Dates into Months.

I've managed to achieve the first three points using the following query:

= let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Time", "ResourcePlanning"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Date", "Product", "Channel", "Product_Type"}, {{"Total Offered Net", each List.Sum([Offered_Net_Abn5Sec]), type number}, {"Total Offered Inc", each List.Sum([Offered_Inc_Abn5Sec]), type number}, {"Total Handled", each List.Sum([Handled]), type number}, {"Total Abandoned Net", each List.Sum([Abandoned_Net_Abn5Sec]), type number}, {"Total Abandoned Inc", each List.Sum([Abandoned_Inc_Abn5Sec]), type number}, {"Total Abn", each List.Sum([Abn5Sec]), type number}, {"Total SL", each List.Sum([SL_Number]), type number}, {"Avg SL Number CR", each List.Sum([SL_Number_CR]), type number}, {"Avg Time to Ans", each List.Sum([Time_to_Answer])/List.Sum([Handled]), type number}, {"Avg Time to Abandon", each List.Sum([Time_to_Abandon])/List.Sum([Handled]), type number}})
in
    #"Grouped Rows"

However I struggling to achieve the last two points - how to specify a criteria to merge on and how to group the dates into months.

I've provided an example of the desired outcome below:

Desired outcome

Any help would be appreciated. Thank you.

CodePudding user response：

try below

trick is before grouping, (a) use Date.Month() to create a date column (b) Use a replacement on Product_Type when Product=Mortgage. I added Date.Year() as well because that will eventually come up

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Time", type time}, {"Product", type text}, {"Channel", type text}, {"Product_Type", type text}, {"ResourcePlanning", type any}, {"Offered_Net_Abn5Sec", Int64.Type}, {"Offered_Inc_Abn5Sec", Int64.Type}, {"Handled", Int64.Type}, {"Abandoned_Net_Abn5Sec", Int64.Type}, {"Abandoned_Inc_Abn5Sec", Int64.Type}, {"Abn5Sec", Int64.Type}, {"SL_Number", Int64.Type}, {"SL_Number_CR", type number}, {"Time_to_Answer", Int64.Type}, {"Time_to_Abandon", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Month", each Date.Month([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Year", each Date.Year([Date])),
#"ReplaceMortgage"=  Table.ReplaceValue( #"Added Custom1",  each [Product_Type], each if [Product]="Mortgage" then null else [Product_Type] ,Replacer.ReplaceValue,{"Product_Type"}),
#"Grouped Rows" = Table.Group(ReplaceMortgage, {"Product", "Channel", "Product_Type", "Month", "Year"}, {{"Total Offered Net", each List.Sum([Offered_Net_Abn5Sec]), type number}, {"Total Offered Inc", each List.Sum([Offered_Inc_Abn5Sec]), type number}, {"Total Handled", each List.Sum([Handled]), type number}, {"Total Abandoned Net", each List.Sum([Abandoned_Net_Abn5Sec]), type number}, {"Total Abandoned Inc", each List.Sum([Abandoned_Inc_Abn5Sec]), type number}, {"Total Abn", each List.Sum([Abn5Sec]), type number}, {"Total SL", each List.Sum([SL_Number]), type number}, {"Avg SL Number CR", each List.Sum([SL_Number_CR]), type number}, {"Avg Time to Ans", each List.Sum([Time_to_Answer])/List.Sum([Handled]), type number}, {"Avg Time to Abandon", each List.Sum([Time_to_Abandon])/List.Sum([Handled]), type number}})
in #"Grouped Rows"