Forum Discussion
Column from column values
Hi all,
I have another problem that I cannot solve and wonder if the community can help.
I would like to create new columns based on the entries in the existing column 'Account Name' summing the correct amount by WorOrder and account name field, whilst retainnig the other information.
| Trans.Date | Account | Account Name | ProjectCode | ResourceCode | WorkOrder | Amount |
| 01/01/19 | 100111 | Cost | 101 | 306 | 101.200 | $150 |
| 01/01/19 | 111222 | Revenue | 201 | 306 | 201.100 | -$50 |
| 01/01/19 | 200222 | Revenue | 301 | 401 | 301.100 | -$100 |
| 01/02/19 | 100111 | Cost | 201 | 306 | 201.200 | $50 |
| 01/02/19 | 100222 | Revenue | 201 | 401 | 201.300 | -$100 |
I wish to get a result similar to the below:
| Trans.Date | Account | Cost | Revenue | ProjectCode | ResourceCode | WorkOrder |
| 01/01/19 | 100111 | $150 | -$50 | 101 | 306 | 101.200 |
| 01/01/19 | 200222 | -$100 | 301 | 401 | 301.100 | |
| 01/02/19 | 100111 | $50 | 201 | 306 | 201.200 | |
| 01/02/19 | 100222 | -$100 | 201 | 401 | 201.300 |
Any help would be greatly appreciated!
TIA 🙂
Hello @artfulmunkeey ,
I don't quite understand why your 111222 account will be merged with account 100111 in the desired result.
If you follow your table, the result may be this.
Cost = IF([Account Name]="Cost",[Amount])Revenue = IF([Account Name]="Revenue",[Amount])More details can be found here.
Best regards
Stephen Tao
If this post helps,then consider Accepting it as the solution to help other members find it faster.
2 Replies
Go to Power Query Editor, duplicate your current table, remove all columns besides Trans.Date, Account Name, and Amount. Then, click onto your Account Name column, and pivot it, with the criteria sum of Amount.
Hello @artfulmunkeey ,
I don't quite understand why your 111222 account will be merged with account 100111 in the desired result.
If you follow your table, the result may be this.
Cost = IF([Account Name]="Cost",[Amount])Revenue = IF([Account Name]="Revenue",[Amount])More details can be found here.
Best regards
Stephen Tao
If this post helps,then consider Accepting it as the solution to help other members find it faster.
