Power Query - Insert Row adding column values based on a Group By

[Anonymous]

Hi,

I have the following example table:

Division          Grouping         Value      Target       MValue        MTarget
North             Costs               121          123           44                32
North             Sales                2             34              2                  5
East                Rebate             2             4                55                43
East                Sales                99           12              32                8
East                Costs               34            33              2                 9

South             Sales                4             45              45                8
South             Costs               3              2                3                 3

West               FCP                 2              98              32               4

I require a new row to be added to the table that same sums up each Value, Target, MValue. MTarget, per Grouping but grouped by Division. In this case the new row should be be named 'NorthEast' and it's Value for Sales combined for the Divisions North and East.

From the above example I require the following result:

Division          Grouping         Value      Target       MValue        MTarget

North             Costs               121          123           44                32
North             Sales                2             34              2                  5
East                Rebate             2             4                55                43
East                Sales                99           12              32                8
East                Costs               34            33              2                 9

South             Sales                4             45              45                8
South             Costs               3              2                3                 3

West               FCP                 2              98              32               4

NorthEast       Sales              101         46             32               13
NorthEast       Costs             155         156            46               41
NorthEast       Rebate           2             4               55               43

So you can see that for the North and East divisions the Value, Target, MValue, MTarget are summed, by the Grouping column.

How do I do this in Power Query?

Thanks.

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Please see the attached file's query editor and let me know if it helps

[Anonymous]

Thanks Zubair, much appreciated. Actually, this time I was able to do the required using the wizard.

Can I pick your knowledge on the below as it's a continuation on what you've helped me on:

Starting point is:

Division          Grouping         Value      Target       MValue        MTarget
North               Costs                121          123            44                 32
North               Sales                 2              34              2                   5
East                  Rebate              2              4               55                  43
East                  Sales                99             12             32                  8
East                  Costs               34             33              2                   9

South               Sales                4              45              45                  8
South             Costs                  3              2                3                   3

West               FCP                   2              98              32                  4

The end result is to be:

Division          Grouping           Value        Target        MValue        MTarget
North             Rebate                  121            123             44                  32
North             Refund                  2                34               2                    5

North             Adjusment           12               5                 33                 14

East                Rebate                 121            123              44                 32
East                Refund                 2                34                2                   5

East                Adjusment           12              5                 33                  14

South             Rebate                 121            123              44                 32
South             Refund                 2                34                2                   5

South             Adjusment           12              5                 33                 14

West             Rebate                  121            123             44                  32
West             Refund                  2                34               2                    5

West             Adjusment          12              5                 33                14

North            GP                      123            157             46                37
East               GP                      123            157             46                37
South            GP                      123            157             46                37
West             GP                      123            157             46                37

Firstly, I have duplicated the numbers for each division so the result I am looking for each will be the same however, you should see be able to see my requirement.

An additional row is added to the table, for one each division in the entire table, and it sums up the Value, Target, MValue, MTarget but only for Rebate and Refund groupings. The additional row is created with the Grouping value of GP.

Please can you help?

Thanks.