Power Query 分组，多列求和与计数

maryun

deadzlq 发表于 2021-1-14 14:48
PowerQuery参考解法

虽然看不大懂，但是解决了问题，谢谢

wangziqiang

开始学习了谢谢楼主

ThxAlot

凑热闹来一个

1. let
   
2.     Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
   
3.     #"Changed Type" = Table.TransformColumnTypes(Source,{{"城市", type text}, {"1月", Int64.Type}, {"2月", Int64.Type}, {"3月", Int64.Type}, {"4月", Int64.Type}}),
   
4. 
   
5.     #"Grouped Rows" = Table.Group(#"Changed Type", {"城市"}, {{"Count", each
   
6.         [
   
7.             tb = Table.RemoveColumns(_,{"城市"}),
   
8.             cols = Table.ToColumns(tb),
   
9.             sumup = List.Accumulate({1..List.Count(cols)}, {}, (s,c) => s&{List.Sum(cols{c-1})}),
   
10.             appd = sumup & {List.Sum(sumup), List.NonNullCount(sumup)},
    
11.             res = #table(Table.ColumnNames(tb)&{"销量汇总","覆盖月份数"},{appd})
    
12.         ][res]
    
13.     }}
    
14.     ),
    
15. 
    
16.     #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"1月", "2月", "3月", "4月", "销量汇总", "覆盖月份数"}, {"1月", "2月", "3月", "4月", "销量汇总", "覆盖月份数"})
    
17. in
    
18.     #"Expanded Count"

复制代码