Power Platform Community Forum Thread Details

Hi guys,

I have extracted a datatable from excel which has all the values that I need. My goal is to calculate the sum of all values present in Column H (Amount in local currency) and the sum of all the values present in Column I (Arrears after net due date), with respect to the names in Colum C (Name 1).

The names can be anywhere in the excel table.

Please refer to the table below.

Customer	Name 1	Net due date	Amount in local currency	Arrears after net due date
1000	ABC	2/4/2006	-10,000.98	0
1001	CDE	4/23/2010	-7.00	0
1002	EFG	5/16/2015	-815.49	3,050
1003	HIJ	2/10/2006	-28.00	0
1004	HIJ	2/4/2006	-7,527.93	0
1005	HIJ	8/26/2013	-281.68	3,678
1006	ABC	4/15/2017	-1,010.09	2,350
1007	CDE	10/20/2021	-1,954.06	701
1008	EFG	3/22/2007	-2,790.00	0
1009	ABC	3/30/2012	-451.51	0
1010	CDE	11/19/2012	-426.00	3,958
1011	EFG	6/29/2003	-5.47	7,389
1012	HIJ	2/7/2006	-0.55	0
1013	HIJ	10/7/2003	-395.00	7,289
1014	HIJ	5/21/2019	-1,249.00	1,584
1015	ABC	2/9/2023	-854.52	224
1016	CDE	6/12/2021	-939.00	831

So basically what I need is the following example:

Name	Total Amount in Local Currency	Arrears
ABC	4324353	34324
CDE	30000	1200
EFG	6000	- 3452

Any help will be appreciated!

Categories:

Power Automate Desktop

Assuming you have your data table in %ExcelData%, the following flow should do it:

The idea behind this flow is as follows:

You get the list of names via Retrieve data table column into list
You get a list of unique names by using Remove duplicate items from list. This will result in a list where each name is only included once.
You create a new data table for output by using Create new data table
You remove the first row from the new data table, because Create new data table will create it with a single empty row by default. So we use Delete row from data table to remove it.
You then loop through the list of unique names in a For each loop where:
1. You set the amount to 0 via Set variable
2. You set the arrears to 0 via Set variable
3. You use Find or replace in data table to find all rows in your %ExcelData% where the name equals the current name
4. You loop through the matched rows via another For each loop, where:
  1. You convert the row index to a numeric value by using Convert text to number. This is needed because for some reason the Find or replace in data table action will return the row indexes as strings, but they need to be converted to numbers in order to be used when referencing the table rows.
  2. You then use the row index to retrieve the amount and convert it to a numeric value via Convert text to number.
  3. You do the same as above for the arrears value via Convert text to number.
  4. You use Increase variable to increase the amount value with the current amount you just converted.
  5. You do the same to the arrears value via Increase variable.
5. When the nested loop for the current name ends, you use Insert row into data table to insert a row for the current name with its total values into the output table.

This will result in a %DataTable% variable that will have each name only included once and the two numeric values included as totals. Essentially a sub-total table.

Here's a code snippet that you can copy and paste into your PAD flow designer to have these actions created for you:

Variables.RetrieveDataTableColumnIntoList DataTable: ExcelData ColumnNameOrIndex: $'''Name 1''' ColumnAsList=> NamesList
Variables.RemoveDuplicateItemsFromList List: NamesList IgnoreCase: False
Variables.CreateNewDatatable InputTable: { ^['Name', 'Amount', 'Arrears'], [$'''''', $'''''', $''''''] } DataTable=> DataTable
Variables.DeleteRowFromDataTable DataTable: DataTable RowIndex: 0
LOOP FOREACH CurrentName IN NamesList
 SET Amount TO 0
 SET Arrears TO 0
 Variables.FindOrReplaceInDataTable.FindItemInDataTableByColumnIndex DataTable: ExcelData AllMatches: True ValueToFind: CurrentName MatchCase: True MatchEntireCellContents: True ColumnNameOrIndex: $'''Name 1''' DataTableMatches=> DataTableMatches
 LOOP FOREACH MatchedRowData IN DataTableMatches
 Text.ToNumber Text: MatchedRowData['Row'] Number=> RowIndex
 Text.ToNumber Text: ExcelData[RowIndex]['Amount in local currency'] Number=> CurrentAmount
 Text.ToNumber Text: ExcelData[RowIndex]['Arrears after net due date'] Number=> CurrentArrears
 Variables.IncreaseVariable Value: Amount IncrementValue: CurrentAmount
 Variables.IncreaseVariable Value: Arrears IncrementValue: CurrentArrears
 END
 Variables.AddRowToDataTable.AppendRowToDataTable DataTable: DataTable RowToAdd: [CurrentName, CurrentAmount, CurrentArrears]
END

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.