very useful trick. I wished you commented the video instead of the music (it's a personal thing, music distracts me, music and coding take up the same space of my brain, the creation part 🤣🤣🤣🤣)
Thanks for your video. Was the only place could find answer to this. One thing I'm trying to do extra though is put the group totals as Header rows above the detail, rather than below. Cant work it out. Is it possible?
Based on the attachment used in the video, please make the following changes to the code. = Table.Group(#"Changed Type", {"Column1"}, {{"tbl", each #table(Table.ColumnNames(_),{{[Column1]{0} &" Total", List.Sum([Column2]) }} ) & _ }} )
Hello, thank for sharing your expertise. I want to do exactly the same, but with more columns (suppose 4), but the subtotals remains only one or two columns. How can I add the columns after COL 1 before COL 2 ? Thanks
You just need to ensure this section has the same number of columns as your main table: each _ & #table(Table.ColumnNames(_),{{"","",[GroupID]{0},[Group title]{0},List.Sum([Total Hours]) }} ) }}) I added two extra columns with blanks at the front before my GroupID. Similar Q&A a few weeks before as well below
How to create with 3 columns, the first column being dates and the subtotal being applied in the 3rd column? I need to process a bank statement. Example 01/07/2023 | PIX | 20,00 01/07/2023 | Maestro | 40,00 01/07/2023 |Visa | 40,00 --------------------|SUBTOTAL | 100
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type text}, {"Column3", Int64.Type}}), Result = Table.InsertRows(#"Changed Type",Table.RowCount(#"Changed Type"),{[Column1="-------------------",Column2="SUBTOTAL",Column3 = List.Sum(#"Changed Type"[Column3])]}) in Result
![[Power Query] Create sum of Expanded Table Column (number) after NestedJoin](http://i.ytimg.com/vi/T2ljRstFb9Y/mqdefault.jpg)
![[Power Query] Create sum of Expanded Table Column (number) after NestedJoin](/img/tr.png)
Cool use of the #table function. You are very creative! Thanks for sharing. Thumbs up!!
Great tutorial!
Is it possible to add a Grand TOTAL for items A, B & C? I am dealing with something like this at work. Great example .Thanks
I think that you'd need to make another table, sum the entire list, and combine it just as shown
@@SoNonWoo Thanks I will try that
very useful trick. I wished you commented the video instead of the music (it's a personal thing, music distracts me, music and coding take up the same space of my brain, the creation part 🤣🤣🤣🤣)
Thanks for your video. Was the only place could find answer to this.
One thing I'm trying to do extra though is put the group totals as Header rows above the detail, rather than below. Cant work it out. Is it possible?
Based on the attachment used in the video, please make the following changes to the code.
= Table.Group(#"Changed Type", {"Column1"}, {{"tbl", each #table(Table.ColumnNames(_),{{[Column1]{0} &" Total", List.Sum([Column2]) }} ) & _ }} )
@@short_excel Works Great. Simple . Thanks for taking time to respond
Hello, thank for sharing your expertise.
I want to do exactly the same, but with more columns (suppose 4), but the subtotals remains only one or two columns. How can I add the columns after COL 1 before COL 2 ? Thanks
You just need to ensure this section has the same number of columns as your main table:
each _ & #table(Table.ColumnNames(_),{{"","",[GroupID]{0},[Group title]{0},List.Sum([Total Hours]) }} ) }})
I added two extra columns with blanks at the front before my GroupID.
Similar Q&A a few weeks before as well below
Thanks for knowledge and sample file
very nice
Thanks you very much
Excellent 💯👍
Awesome trick
Very useful 👌. But sir how can I get total from several columns? Please help me. Thanks a lot.
he used the List.sum on column B. Just use list.sum for each column you want to sum.
Good..Thanks!
How to create with 3 columns, the first column being dates and the subtotal being applied in the 3rd column? I need to process a bank statement.
Example
01/07/2023 | PIX | 20,00
01/07/2023 | Maestro | 40,00
01/07/2023 |Visa | 40,00
--------------------|SUBTOTAL | 100
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type text}, {"Column3", Int64.Type}}),
Result = Table.InsertRows(#"Changed Type",Table.RowCount(#"Changed Type"),{[Column1="-------------------",Column2="SUBTOTAL",Column3 = List.Sum(#"Changed Type"[Column3])]})
in
Result