cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
DanBusIntel Member
Member

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M Established Member
Established Member

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

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:

Final Table.png

 

Here's the file:

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

2 REPLIES 2
Nick_M Established Member
Established Member

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

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:

Final Table.png

 

Here's the file:

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

DanBusIntel Member
Member

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

Thanks @Nick_M   Much appreciated.