Column from column values

[artfulmunkeey]

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 🙂

[v-stephen-msft · Accepted solution]

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.

[darentengmfs]

Hi @artfulmunkeey 

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.