Forum Discussion
Power Query - Insert a new row summing row values for addition columns
Hi,
My starting point is:
Division Grouping Value Target MValue MTarget
North Refund 2 4 5 6
North Rebate 4 2 5 5
North Cash 4 4 4 4
South Refund 8 1 3 7
South Rebate 2 3 4 11
South Cash 3 2 3 2
I require the end result to look like:
Division Grouping Value Target MValue MTarget
North Refund 2 4 5 6
North Rebate 4 2 5 5
North Cash 4 4 4 4
South Refund 8 1 3 7
South Rebate 2 3 4 11
South Cash 3 2 3 2
North GP 6 6 10 11
South GP 10 4 7 11
As you can see I require an additional row inserted for each division (there are many more divisions in my real data) with the value of 'GP' present in the Grouping column for that new row and the Value, Target, MValue, MTarget column values to be a sum of the 'Refund' and 'Rebate' Grouping values.
Please can someone help me achieve this?
Thanks.
Please take a look at the attached file to see all the detailed steps, but here's a quick overview:
- Load the data ( I just took what you posted here, but had to some manual splitting, so the first 2/3 of the applied steps are that)
- Duplicate the querry ( I named it GP_Grouping)
- Filter out rows you dont want to sum ( though probably better to say the rows you want to keep, but for now it just takes the rows where grouping <> Cash"
- Grouped the rows by Division, and then added columns for the Sum of the four columns
- Add a custom column with the name Grouping and the values GP
- Go back to the original table and append it with the GP_Grouping table
Final Table:
Here's the file:
2 Replies
Please take a look at the attached file to see all the detailed steps, but here's a quick overview:
- Load the data ( I just took what you posted here, but had to some manual splitting, so the first 2/3 of the applied steps are that)
- Duplicate the querry ( I named it GP_Grouping)
- Filter out rows you dont want to sum ( though probably better to say the rows you want to keep, but for now it just takes the rows where grouping <> Cash"
- Grouped the rows by Division, and then added columns for the Sum of the four columns
- Add a custom column with the name Grouping and the values GP
- Go back to the original table and append it with the GP_Grouping table
Final Table:
Here's the file: