Combine totals for two rows without grouping and losing columns.

KCantor · Aug 16, 2016

I have a conundrum. We have a rep that has returned after a previous stint here. He has a new rep ID. I need to combine his new numbers with his old numbers but am unsure how to do that. I don't want to lose other information in columns like I would if I used group, I need all of the numbers. Is this possible? Sample data is below. I would like for BFREE and BFREE1 to show in a single row with combined totals, summed up.
Any help on this?

Rep	Quotes	Quoted Amount	Orders	Ordered Amount	Invoices	Invoiced Amount	Credits	Credited Amount	Converted	Converted Amount	Net Invoiced Amount
CBRADIO	32	141779.01	80	162368.14	57	113533.92	1	59.2	102	155433.85	155374.65
Pwolf	51	371960.58	45	126254.5	101	115827.64	3	3600.84	160	172429.58	168828.74
BFREE	35	104919.12	93	208095.03	66	123819.35	6	7806.22	127	236505.79	228699.57
BFREE1	1	2088.85	1	154.33	1	154.33	0	0	0	0	0
CAX	32	195675.57	23	23737.61	73	58371.63	3	269.06	123	88080.97	87811.91
PTRUCK	25	143723.14	23	83394.57	66	105009.85	1	13878.2	119	146371.49	132493.29
PHARVEY	36	435645.08	12	37907.97	53	69161.7	5	891.43	85	62635.48	61744.05

SteelReyn · Aug 17, 2016

My first thought is to do a replace values on the Rep column and replace any instance of BFREE1 with BFREE. Then group by the Rep column, and add back in all of your other columns. Sum each one and give it the same name.

Here's the code in M:

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rep", type text}, {"Quotes", Int64.Type}, {"Quoted Amount", type number}, {"Orders", Int64.Type}, {"Ordered Amount", type number}, {"Invoices", Int64.Type}, {"Invoiced Amount", type number}, {"Credits", Int64.Type}, {"Credited Amount", type number}, {"Converted", Int64.Type}, {"Converted Amount", type number}, {"Net Invoiced Amount", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","BFREE1","BFREE",Replacer.ReplaceText,{"Rep"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Rep"}, {{"Quotes", each List.Sum([Quotes]), type number}, {"Quoted Amount", each List.Sum([Quoted Amount]), type number}, {"Orders", each List.Sum([Orders]), type number}, {"Ordered Amount", each List.Sum([Ordered Amount]), type number}, {"Invoices", each List.Sum([Invoices]), type number}, {"Invoiced Amount", each List.Sum([Invoiced Amount]), type number}, {"Credits", each List.Sum([Credits]), type number}, {"Credited Amount", each List.Sum([Credited Amount]), type number}, {"Converted", each List.Sum([Converted]), type number}, {"Converted Amount", each List.Sum([Converted Amount]), type number}, {"Net Invoiced Amount", each List.Sum([Net Invoiced Amount]), type number}})
in
    #"Grouped Rows"

Combine totals for two rows without grouping and losing columns.

KCantor

New member

SteelReyn

New member

Attachments