Forum Discussion

Mederic's avatar
Mederic
Post Patron
2 years ago
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....
  • FlexYourData's avatar
    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