Subtotal + index column

[Mederic]

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. taxe	Amount Incl. Taxe	Invoice No	Invoice Date
A	Text1	3	10	30	36	F12	12/04/2023
B	Text2	5	2	10	12	F12	12/04/2023
C	Text3	8	5	40	48	F12	12/04/2023
G	Text4	4	17	68	81,6	F56	24/05/2023
H	Text5	6	13	78	93,6	F56	24/05/2023
T	Text6	9	7	63	75,6	F25	19/07/2023
A	Text7	3	8	24	28,8	F65	14/08/2023
J	Text8	7	5	35	42	F65	14/08/2023
Y	Text9	6	12	72	86,4	F65	14/08/2023
S	Text10	8	9	72	86,4	F65	14/08/2023

[Mederic]

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]

Crazy that there's no M option, but I selected C#.

[Mederic]

Thanks @FlexYourData , that's good to know

Best regards

[ChielFaber]

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!

[FlexYourData · Accepted solution]

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]

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"

[Mederic]

Hello @jgeddes ,
Thank you for this solution, which works well,
I'm waiting to see another proposal before confirming it.

I'd like to have fewer steps while keeping the order of the columns.

Best regards

[jgeddes]

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