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. 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

 

 

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

     

8 Replies

  • 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

     

  • FlexYourData's avatar
    FlexYourData
    Regular Visitor
    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

     

    • Mederic's avatar
      Mederic
      Post Patron
      2 years ago

      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"

       

       

       

       

       

       

  • ChielFaber's avatar
    ChielFaber
    Super User
    2 years ago

    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!

  • Mederic's avatar
    Mederic
    Post Patron
    2 years ago

    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

  • Mederic's avatar
    Mederic
    Post Patron
    2 years ago

    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