Cross Tab from Accounting Entries Debit Credits

[kashifzafar02]

Dear All

I have accounting entries in the following format

Date​	Voucher No​	Party​	Dr/Cr​	Debit Amount​	Credit Amount​	Net​
03-01-22​	1​	A​	Dr​	100​		100​
03-01-22​	1​	B​	Cr​		100​	-100​
03-01-22​	2​	A​	Dr​	40​		40​
03-01-22​	2​	C​	Cr​		40​	-40​
03-01-22​	3​	D​	Dr​	72​		72​
03-01-22​	3​	A​	Cr​		72​	-72​
03-01-22​	4​	A​	Dr​	2​		2​
03-01-22​	4​	A​	Cr​		2​	-2​

Out put I am looking for is:

	A	B	C	D	Total​
A	0​	100​	40​	-72​	68​
B	-100​				-100​
C	-40​				-40​
D	72​				72​
Total​	-68​	100​	40​	-72​	0​

Link to file is

Cross tab.xlsx

1drv.ms

I have tried Power query also but cannot get the logic. Any help is appreciated.

Thank you.

 

[JGordon11]

As long as your Dr always precedes its corresponding Cr then this may work:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    tbl = Table.AddColumn(AddIndex, "Map", each if [#"Dr/Cr"] = "Dr" then {_[Party], AddIndex[Party]{_[Index]+1}, [Net]} else {_[Party], AddIndex[Party]{_[Index]-1}, [Net]}),
    Map = Table.FromRows(tbl[Map]),
    Group = Table.Group(Map, {"Column1", "Column2"}, {{"Total", each List.Sum([Column3]), type number}}),
    Group1 = Table.Group(Group, {"Column1"}, {{"All", each Table.Sort(_, "Column2")}}),
    tbl1 = Table.AddColumn(Group1, "rows", each {[Column1]} & List.Accumulate(Group1[Column1], {}, (s,c)=> 
            s & (if List.PositionOf([All][Column2],c)>-1 then {_[All][Total]{List.PositionOf(Group1[Column1],c)}} else {null}))),
    tbl2 = Table.FromRows(tbl1[rows], {"Party"} & tbl1[Column1]),
    tbl3 = Table.AddColumn(tbl2,"Total", each List.Sum(List.Skip(Record.ToList(_),1))),
    tcn = Table.ColumnNames(tbl3),
    lst = {"Total"} & List.Accumulate(List.Skip(tcn,1), {}, (s,c)=> s & {List.Sum(Table.Column(tbl3,c))}),
    rcd = Record.FromList(lst, tcn),
    Result = Table.InsertRows(tbl3, Table.RowCount(tbl3), {rcd})
in
    Result

PQ pivot transactions.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Voucher No	Party	Dr/Cr	Debit Amount	Credit Amount	Net		Party	A	B	C	D	Total
2	3/1/2022	1	A	Dr	100		100		A	0	100	40	-72	68
3	3/1/2022	1	B	Cr		100	-100		B	-100				-100
4	3/1/2022	2	A	Dr	40		40		C	-40				-40
5	3/1/2022	2	C	Cr		40	-40		D	72				72
6	3/1/2022	3	D	Dr	72		72		Total	-68	100	40	-72	0
7	3/1/2022	3	A	Cr		72	-72
8	3/1/2022	4	A	Dr	2		2
9	3/1/2022	4	A	Cr		2	-2
10
Sheet1