Power Query - Insert a new row summing row values for addition columns

[Anonymous]

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.

[Anonymous · Accepted solution]

Please take a look at the attached file to see all the detailed steps, but here's a quick overview:

1. 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)
2. Duplicate the querry ( I named it GP_Grouping)
3. 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"
4. Grouped the rows by Division, and then added columns for the Sum of the four columns
5. Add a custom column with the name Grouping and the values GP
6. Go back to the original table and append it with the GP_Grouping table

Final Table:

Here's the file:

https://1drv.ms/f/s!AoQIGRpzoxRHgbxBe-I0NdRAmrMPhg

[Anonymous]

Thanks @Anonymous   Much appreciated.