Forum Discussion
Solved
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....
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
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
Hello FlexYourData , ChielFaber , jgeddes ,
Thank you for your answers,
I've tested the different solutions
The one proposed by FlexYourData is interesting,
I made a few minor changes to get the exact result and reduced 1 or 2 steps (see MCode and screenshot result):
- inverted 2 tables : Stacked = Table.Combine({Invoices, Source})
- removed the steps : Sorted an RemoveInvoiceNo
Thanks to you
Have a nice day
Best regards
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[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({Invoices, Source}),
// Add index to each invoice no
Indexed = Table.Group(Stacked, {"Invoice No"}, {{"Rows",each Table.AddIndexColumn(_, "Index", 1)}})[[Rows]],
// Expand groups with Index as first column
Result = Table.ExpandTableColumn(Indexed, "Rows", {"Index"} & Table.ColumnNames(Source)),
#"Changed Type" = Table.TransformColumnTypes(Result,{{"Index", Int64.Type}, {"Reference", type text}, {"Designation", type text}, {"Qty", Int64.Type}, {"Price Unit.", Int64.Type}, {"Amount Excl. taxe", Int64.Type}, {"Amount Incl. Taxe", type number}, {"Invoice No", type text}, {"Invoice Date", type date}})
in
#"Changed Type"