PDA

View Full Version : Combine totals for two rows without grouping and losing columns.



KCantor
2016-08-16, 02:10 PM
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
2016-08-17, 09:54 PM
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.

http://www.excelguru.ca/forums/attachment.php?attachmentid=5596&stc=1

Here's the code in M:


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"