如何用PQ进行汇总

cjh-01

突发奇想，上午的一个帖子能否用PQ解决？原贴：https://club.excelhome.net/thread-1604613-1-1.html

ThxAlot

这倒不是什么奇想，典型“中国式报表”思维而已，把该放的不该放的都挤在一起。

微软把PQ和DAX集成在PBI里显然是经过考量的，很多统计功能二者都能实现，但显然二者的分工却是非常明确。

你说的这些统计PQ不是不能实现，是不如DAX来得简洁，DAX就是为了“筛选-聚合”而生的。

1. let
   
2.     Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Werqh/9ncPiUdJUN9Q30jAyNDENPAwEApVgcs+byvGyxpBJM0MkVIPt2zACxpDNeJJAkx1gghaWSAKWkBk7RAN9VI3xImZ4xFo6EB3E4jdK3GSHYaoms1RtJqBLY1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Qty = _t]),
   
3.     #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Qty", Int64.Type}}),
   
4.     #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Date", Order.Ascending}}),
   
5.     #"Grouped by Month" = Table.Group(#"Sorted Rows", {"Name", "Date"}, {"Count", Table.RowCount}, 0, (x,y) => Number.From(Date.Month(x[Date])<>Date.Month(y[Date]))),
   
6.     #"Grouped by Name" = Table.Group(#"Grouped by Month", "Name", {"Rmk", each let rs=Table.ToRecords(_) in Text.Combine(List.Transform(rs, each Date.ToText([Date], "yyyy年M月"&": "&Text.From([Count]))), ", ") & " | 总计: " & Text.From(List.Sum([Count]))})
   
7. in
   
8.     #"Grouped by Name"

复制代码

渡渡orz

如何用PQ进行汇总.rar (14.88 KB, 下载次数: 10)

2021-11-1 21:32 上传

点击文件名下载附件

抛砖引玉吧

cjh-01

谢谢给位的无私指教！

questionsome

用PQ做的话其实逻辑不复杂 只是合并各种类型数据有点烦

cjh-01

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Werqh/9ncPiUdJUN9Q30jAyNDENPAwEApVgcs+byvGyxpBJM0MkVIPt2zACxpDNeJJAkx1gghaWSAKWkBk7RAN9VI3xImZ4xFo6EB3E4jdK3GSHYaoms1RtJqBLY1FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Qty = _t]),

我的数据如何转换成"i45Werqh/9ncPiUdJUN9Q30jAyNDENPAwEApVgcs+byvGyxpBJM0MkVIPt2zACxpDNeJJAkx1gghaWSAKWkBk7RAN9VI3xImZ4xFo6EB3E4jdK3GSHYaoms1RtJqBLY1FgA=" base64编码？