Calculation of Total s from a column based on Document Numbers

[muhammadbinzia]

Hello guys, its very important and difficult task that I am faced with. Below is a table 

where you can see that the data is in ascending order as per dates and also as per the Document number. If we see the same number document is used for clearing of one bill and all of its Receipts or Replies. Therefore the last column contains Total Values of Bill, Receipt and Exchange diff which combined knocks off the Bill or only a slight portion of Bill is left unpaid (That is why receipt is in negative). But what I want in Column five (5) is that based on sum of document numbers if the sum total of that document number is more than 0, that is, it is outstanding by some portion, then the remaining (Outstanding amount) show in against the Bill row of that Document Number and also receipt value multiplied by negative should show against receipt row of that particular document number. If the sum total for any particular document number is 0, then only the negative of receipt amount and Exchange diff should show against Receipt row of that document number and the Exchange diff row resepcetively.

Kindly please if anyone can formulate such condition. Its basically for calculation of late payment surcharge on bill receipts above the due date but the problem is that many bills have more than one receipts or other factors corresposnding to it. (Bill is represented by a unique document number).

Jul-17	Bill	8000025	36,816,920
Jul-17	Exchange diff	8000025	5,317
Jul-17	Receipt	8000025	(36,822,237)
Aug-17	Bill	8000297	77,788,412
Aug-17	Exchange diff	8000297	6,566
Aug-17	Receipt	8000297	(77,794,978)
Sep-17	Bill	8000551	109,868,311
Sep-17	Exchange diff	8000551	6,375
Sep-17	Receipt	8000551	(109,874,686)
Oct-17	Bill	8000674	100,688,746
Oct-17	Exchange diff	8000674	(61,802)
Oct-17	Receipt	8000674	(100,626,944)
Nov-17	Bill	8000839	110,543,290
Nov-17	Receipt	8000839	(110,527,940)
Nov-17	Exchange diff	8000839	(15,350)
Dec-17	Receipt	8001030	(288,773,588)
Dec-17	Bill	8001030	288,618,568
Dec-17	Exchange diff	8001030	155,020
Jan-18	Bill	8001136	284,152,877
Jan-18	Receipt	8001136	(284,140,251)
Jan-18	Exchange diff	8001136	(12,626)
Feb-18	Bill	8001279	254,178,038
Feb-18	Receipt	8001279	(254,281,739)
Feb-18	Exchange diff	8001279	103,701
Mar-18	Bill	8001546	208,551,174
Mar-18	Receipt	8001546	(208,509,371)
Mar-18	Exchange diff	8001546	(41,803)
Apr-18	Bill	8001666	205,417,715
Apr-18	Receipt	8001666	(205,437,363)
Apr-18	Exchange diff	8001666	19,648
May-18	Bill	8001929	120,783,466
May-18	Receipt	8001929	(120,753,579)
May-18	Exchange diff	8001929	(29,887)
Jun-18	Bill	8002067	119,256,071
Jun-18	Receipt	8002067	(119,256,071)
Jul-18	Bill	8000032	94,446,347
Jul-18	Receipt	8000032	(94,435,913)
Jul-18	Exchange diff	8000032	(10,434)
Aug-18	Bill	8000180	117,393,902
Aug-18	Receipt	8000180	(117,388,740)
Aug-18	Exchange diff	8000180	(5,162)
Sep-18	Bill	8000407	75,838,002
Sep-18	Receipt	8000407	(75,833,010)
Sep-18	Exchange diff	8000407	(4,992)

[Vijay_A_Verma · Accepted solution]

Solution uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuSIghsHUEzQkwFJ-?e=8XJUft 

M-code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type text}, {"Column3", Int64.Type}, {"Column4", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column3"}, {{"All", each _, type table [Column1=nullable date, Column2=nullable text, Column3=nullable number, Column4=nullable number]}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "ProcessFunction", each ProcessFunction([All])),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Column3", "All"}),
    #"Expanded ProcessFunction" = Table.ExpandTableColumn(#"Removed Columns", "ProcessFunction", {"Column1", "Column2", "Column3", "Column4", "Result"}, {"Column1", "Column2", "Column3", "Column4", "Result"})
in
    #"Expanded ProcessFunction"

(TableName)=>
let
    #"Added Index" = Table.AddIndexColumn(TableName, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Sum", each List.Sum(#"Added Index"[Column4])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Result", each if [Sum]>0 then
(if [Index]=0 then [Sum] else if [Index]=2 then -1*[Column4] else 0)
else if [Index]>0 then -1*[Column4] else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "Sum"})
in
    #"Removed Columns"

[muhammadbinzia]

Thank you so much for your kind response. This was a really good solution as it works on current file. You must have command over these things. I couldnt download the file earlier on but it now I saw it and it works quite well. It is so good to learn a new thing in a while.

Now heres where my Data gets a little trickier as per the table attached below:

I have different customers like more than twenty or thirty but I have attached for simplicity only 3 customers. In these customers the Months are repeating and Document Numbers may also be repeating. So, filter is required to first filter the customers and then sort it in Monthly order (Column 1) and then also sort it in the order of Column 2 that is First as per the documents number, for that document number, Bill should appear first in the row, then Receipts row even if they are more than two receipts and then other rows like Exchange diff and then it should perform the same function as it has for the previous example. So basically I need to filter the list at customer level and then sort it in Month wise order and then as per document number, sort into Bill Receipt and exchange diff (others) and then perform the same action in the last column. Can you further help in this regard as this seem a lot difficult but it will solve a huge problem for me. Thank you once again.

Customer No	Column1	Column2	Column3	Column4	Column5	Column6
1	Jan-22	Bill	8001136	284,152,877	1
1	Jan-22	Receipt	8001136	-284,140,251	3
1	Jan-22	Exchange diff	8001136	-12,626	4
1	Feb-22	Bill	8001279	254,178,038	1
1	Feb-22	Receipt	8001279	-254,281,739	3
1	Feb-22	Exchange diff	8001279	103,701	4
1	Mar-22	Bill	8001546	208,551,174	1
1	Mar-22	Receipt	8001546	-208,509,371	3
1	Mar-22	Exchange diff	8001546	-41,803	4
2	Jan-22	Bill	22222	284,152,877	1
2	Jan-22	Receipt	22222	-284,140,251	3
2	Jan-22	Exchange diff	22222	-12,626	4
2	Feb-22	Bill	8001279	254,178,038	1
2	Feb-22	Receipt	8001279	-254,281,739	3
2	Feb-22	Exchange diff	8001279	103,701	4
2	Mar-22	Bill	8001546	208,551,174	1
2	Mar-22	Receipt	8001546	-208,509,371	3
2	Mar-22	Exchange diff	8001546	-41,803	4
2	Apr-22	Bill	8001666	205,417,715	1
2	Apr-22	Receipt	8001666	-205,437,363	3
2	Apr-22	Exchange diff	8001666	19,648	4
2	Jul-22	Bill	8000025	36,816,920	1
2	Jul-22	Receipt	8000025	-31,822,237	3
2	Jul-22	Exchange diff	8000025	5,317	4
3	Jan-22	Bill	22222	284,152,877	1
3	Jan-22	Receipt	22222	-284,140,251	3
3	Jan-22	Exchange diff	22222	-12,626	4
3	Feb-22	Bill	8001279	254,178,038	1
3	Feb-22	Receipt	8001279	-254,281,739	3
3	Feb-22	Exchange diff	8001279	103,701	4
3	Mar-22	Bill	8001546	208,551,174	1
3	Mar-22	Receipt	8001546	-208,509,371	3
3	Mar-22	Exchange diff	8001546	-41,803	4