# Tags
**Software** : [[02 - Tags/Software/Power Query]]
**Functions** used : [[Excel.CurrentWorkbook]] [[Table.TransformColumnTypes]] [[Table.Group]] [[Table.ExpandTableColumn]] [[Table.AddColumn]] [[Table.ReorderColumns]] [[Table.ReplaceValue]] [[Table.UnpivotOtherColumns]] [[Table.Pivot]] [[List.Distinct]] [[List.Sum]]
**Date** : 30-March-2025
# Description
Though not recommended in Power Query rather in DAX here is how to add Subtotals and Grand totals to a Query or Dataset.
# Video Link
<iframe width="560" height="315" src="https://www.youtube.com/embed/S1_0qGY2BJk?si=nF0dbuYSdOVlHAGu" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>
[GITHUB File Link](https://github.com/jbotes/powerbiTutorials/blob/main/PQ_Subtotals_Video.xlsx)
# M-Query
```mquery
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
DataType = Table.TransformColumnTypes(Source,{{"PortfolioName", type text}, {"Stock", type text}, {"Shares", Int64.Type}, {"Holding", Int64.Type}}),
Total1 = SubTotalFunction(DataType),
#"Grouped Rows" = Table.Group(DataType, {"PortfolioName"}, {{"Count", each _ & SubTotalFunction(_), type table [PortfolioName=text, Stock=text, Shares=number, Holding=number]}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"PortfolioName", "Stock", "Shares", "Holding"}, {"PortfolioName.1", "Stock", "Shares", "Holding"}) & Total1,
#"Added Custom" = Table.AddColumn(#"Expanded Count", "Portfolio", each if [PortfolioName.1] = "Total" then [PortfolioName] & " - " & [PortfolioName.1] else if [PortfolioName.1] = null then "Grand Total" else [PortfolioName.1]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"PortfolioName", "Portfolio", "PortfolioName.1", "Stock", "Shares", "Holding"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"PortfolioName", "PortfolioName.1"}),
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns","Total","",Replacer.ReplaceText,{"Stock"})
in
#"Replaced Value"
```
```mquery
(x)=>
let
#"Unpivoted Columns" = Table.UnpivotOtherColumns(x, {}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value", each try List.Sum(_) otherwise "Total")
in
#"Pivoted Column"
```
## Optional Tags
**Tags** :
**Technique** : [[Custom Function]]