Forum Discussion
Mederic
2 years agoPost Patron
Subtotal + index column
Hello everyone, I'm trying to achieve the result shown in the image below, Can you please help me with this? Thanks in advance Best regards Reference Designation Qty Price Unit. Amount Excl....
- 2 years ago
You can try this:
let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], // Group by Invoice No and Invoice Date to get sum of amount incl taxes Invoices = Table.Group(Source, {"Invoice No", "Invoice Date"}, {{"Amount Incl. Taxe", each List.Sum([Amount Incl. Taxe])}}), // Combine the sub-total rows with the original data Stacked = Table.Combine({Source, Invoices}), // Sort so sub-totals are at top of each invoice date Sorted = Table.Sort(Stacked, {{"Invoice Date", Order.Ascending}, {"Reference", Order.Ascending}}), // Add index to each invoice no Indexed = Table.Group(Sorted, {"Invoice No"}, {{"Rows",each Table.AddIndexColumn(_, "Index", 1)}}), // Remove extra column RemoveInvoiceNo = Table.RemoveColumns(Indexed,{"Invoice No"}), // Expand groups with Index as first column Result = Table.ExpandTableColumn(RemoveInvoiceNo, "Rows", {"Index"} & Table.ColumnNames(Source)) in Result