Power Query: Best way to duplicate data, flip signage, change business unit

andydtaylor

Active Member
Joined
Feb 15, 2007
Messages
355
Office Version
2010
Hi,

I have some table data where I would like to generate an aggregate reciprocal position. This relates to modelling allocation of costs within an organisation.

E.g. 5 departments a, b, c, d, e receive costs of -1, -2, -3, -4, -5 respectively. I would like to generate in the cleanest and most simple manner possible an opposing amount of +15 for department X which I am happy to hard code or take as a parameter. My actual source table contains about 8 other fields which are all to be retained. These other fields include month, cost type, SLA agreement number etc.

What's a good way to do this? I am presently generating various staging tables and combining but I don't like it. is there a better way? Keen to not mirror rows wholesale in order to reduce memory overhead. Would ideally like one aggregate opposing position.

Thanks,

Andy
 
Last edited:

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Watch MrExcel Video

Forum statistics

Threads
1,098,979
Messages
5,465,768
Members
406,449
Latest member
cubeincube

This Week's Hot Topics

Top