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
Mederic
2 years agoPost Patron
Hello,
Just a question about formatting the code,
How do you format the code so that it looks like FlexYourData
Is it in one of the tags in the screenshot list or is it a copy/paste directly into the message ?
Thanks in advance
Best regards
FlexYourData
2 years agoRegular Visitor
Crazy that there's no M option, but I selected C#.
- Mederic2 years agoPost Patron
Thanks FlexYourData , that's good to know
Best regards