Hi all,
I have a SharePoint List as similar as below, for same invoice, it is using same currency
Invoice:
| Company | Invoice No | Invoice Date | Currency |
| A | IN000001 | 1/1/2021 | USD |
| A | IN000002 | 1/1/2021 | USD |
| A | IN000003 | 1/2/2021 | EUR |
Invoice Item:
| Invoice No | Item Type | Amount |
| IN000001 | CPU | 2020 |
| IN000001 | Ram | 101 |
| IN000001 | Hard disk | 1010 |
| IN000001 | CPU | 2030 |
| IN000002 | Ram | 203 |
| IN000003 | Windows License | 1234 |
| IN000003 | CPU | 1234 |
When I trigger the flow for company A, it should sum all Invoice item with same invoice no and item type into single cell and sum the column with same currency into total. The column items is fixed and total rows is dynamic base on how many currency involved as below:
| Invoice No | Currency | CPU | Ram | Hard disk | Windows License | Others |
| IN000001 | USD | 4050 | 101 | 1010 | 0 | 0 |
| IN000002 | USD | 0 | 203 | 0 | 1234 | 0 |
| IN000003 | EUR | 1234 | 0 | 0 | 0 | 0 |
| Total | USD | 4050 | 304 | 1010 | 1234 | 0 |
| | EUR | 1234 | 0 | 0 | 0 | 0 |
I can do it with filter query to get which SharePoint item need to be sum and couple of loops to calculate the value for each cells in the table (including each invoice item, total currency and total amount). However, it is a huge performance issue.
Any better idea to save the data in single/few loop and output into html table?
