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....
Need Help
  • FlexYourData's avatar
    FlexYourData
    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

     

jgeddes's avatar
jgeddes
Super User
2 years ago

Here is one way to do it...

 

 


let
Source = 
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdE7DsIwDAbgu2SOlMR5OSMgAWKlC6o6cgMGjk9+46gLEUPcqvYXJ+66moOxZnm+X6E/Y1/B40VC6eEcCB/J+eTIUzSbXc1RDVK5LxpOin+SkxK0YGUJJPGMXJQkVCFdeygo52DlbBmRkvN5V1dVaIB0QMcK1eJcLaqQagDQIvMXEfYLzfm6ozG6qqNj2ReBrdyqCOqdeEc3RaxdUBJlGjQjDyVtXAmVFYGLTTN1Hz/W69HaX7V9AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Designation = _t, Qty = _t, #"Price Unit." = _t, #"Amount Excl. taxe" = _t, #"Amount Incl. Taxe" = _t, #"Invoice No" = _t, #"Invoice Date" = _t]),
autoTypeChanges = 
Table.TransformColumnTypes(Source,{{"Reference", type text}, {"Designation", type text}, {"Qty", Int64.Type}, {"Price Unit.", Int64.Type}, {"Amount Excl. taxe", Int64.Type}, {"Amount Incl. Taxe", Int64.Type}, {"Invoice No", type text}, {"Invoice Date", type text}}),
groupToSum = 
Table.Group(autoTypeChanges, {"Invoice No", "Invoice Date"}, {{"Amount Incl. Taxe", each List.Sum([Amount Incl. Taxe]), type nullable number}, {"Reference", each null, type text}, {"Designation", each null, type text}, {"Qty", each null, type text}, {"Price Unit.", each null, type text}, {"Amount Excl. taxe", each null, type text}}),
toListOfRecords = 
Table.ToRecords(groupToSum),
insertSumsInTable = 
Table.InsertRows(autoTypeChanges, 0, toListOfRecords),
groupForIndex = 
Table.Group(insertSumsInTable, {"Invoice No"}, {{"_innerTable", each _, type table [Reference=nullable text, Designation=nullable text, Qty=nullable number, #"Price Unit."=nullable number, Amount Excl. taxe=nullable number, Amount Incl. Taxe=nullable number, Invoice No=nullable text, Invoice Date=nullable text]}}),
addIndexColumnToInnerTable = 
Table.TransformColumns(groupForIndex, {"_innerTable", each Table.AddIndexColumn(_, "Index", 1, 1)}),
expandInnerTable = 
Table.ExpandTableColumn(addIndexColumnToInnerTable, "_innerTable", {"Reference", "Designation", "Qty", "Price Unit.", "Amount Excl. taxe", "Amount Incl. Taxe", "Invoice Date", "Index"}, {"Reference", "Designation", "Qty", "Price Unit.", "Amount Excl. taxe", "Amount Incl. Taxe", "Invoice Date", "Index"})
in
expandInnerTable