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
ChielFaber
2 years agoSuper User
You could try to create a indexnumber per group (in your case invoice number). The required steps are perfectly explained by Radacad in the following blog post:
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
Radacad uses the group by function together with the m-function Table.AddIndexColumn to get the required results. It's a great solution, but also very easy to implement.
I've used this solution multiple times and it works great. Do mind that with large datasets it does require some performance.
Hope this helps!