Forum Discussion
Calculation of Total s from a column based on Document Numbers
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"
Most Valuable ProfessionalSolution 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"
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 |