Скачать или смотреть Add Sub Totals and Grand Total to Table in Power Query

#power_query #powerquery

Function Named Sub_Total:

(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"

Advanced Editor Code:
let
Source = null,
T1 = #table({"Cat", "Data1", "Data2", "Data3", "Data4", "Data5", "Data6"},
{{"A", 1, 2, 5, 10, 20, 50},
{"B", 1, 2, 5, 10, 20, 50},
{"A", 1, 2, 5, 10, 20, 50},
{"B", 1, 2, 5, 10, 20, 50},
{"C", 1, 2, 5, 10, 20, 50},
{"D", 1, 2, 5, 10, 20, 50},
{"D", 1, 2, 5, 10, 20, 50},
{"C", 1, 2, 5, 10, 20, 50},
{"A", 1, 2, 5, 10, 20, 50},
{"B", 1, 2, 5, 10, 20, 50}}),
TT = Sub_Total(T1),
#"Grouped Rows" = Table.Group(T1, {"Cat"}, {{"D", each _ & Sub_Total(_), type table [Cat=text, Data1=number, Data2=number, Data3=number, Data4=number, Data5=number, Data6=number]}}),
#"Expanded D" = Table.ExpandTableColumn(#"Grouped Rows", "D", {"Cat", "Data1", "Data2", "Data3", "Data4", "Data5", "Data6"}, {"Cat.1", "Data1", "Data2", "Data3", "Data4", "Data5", "Data6"}),
#"Added Custom" = Table.AddColumn(#"Expanded D", "Category", each if [Cat.1] = "Total" then [Cat] & " " & [Cat.1] else [Cat.1]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Cat", "Cat.1", "Category", "Data1", "Data2", "Data3", "Data4", "Data5", "Data6"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Cat", "Cat.1"})& TT,
#"Replaced Value" = Table.ReplaceValue(#"Removed Columns",null,"Grand Total",Replacer.ReplaceValue,{"Category"})
in
#"Replaced Value"


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please checkout my book, Power Query M Language Basics Kindle Edition
https://www.amazon.com/dp/B0DM73H3RV
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~