I have two tables, the first table has a list of invoice numbers and the second table has a list of products associated with each invoice. I want to sum the total cost of the products for each invoice and include it in the first table using Excel Power Query.
Table 1 [InvoiceNumbers] [OtherData]
Table 2 [Product] [Amount] [InvoiceNumber]
List.Sum() seems to be the function I need to use, but I cannot filter table 2 by invoice number using this function
Table.SelectRows() can be used to select the second table, and filter it to a specific set of rows, but I cannot seem to filter the rows of Table 2 using a column from Table 1.
I have also looked into Grouping and joining the table, but because of other factors I have left out, this is not going to work.
The full query Im working with looks like this.
List.Sum(Table.SelectRows(Table2, each [InvoiceNumber] = [InvoiceNumber])[Amount])
This just returns the sum of all the rows because [InvoiceNumber] is equal to itself.
How can I reference the Invoice Number of the row in Table 1 to use it as a condition in the Table.SelectRows() function? Or is there a better way to get the data sum the from Table 2?
Table 1 Final [InvoiceNumber] [OtherData] [SumOfAmounts]
