有关power query 中如何像Excel中的公式那样取数计算?

agilersu · 发表于 2021-3-31 18:50

本帖最后由 agilersu 于 2021-3-31 18:50 编辑

各位高手,我有如下问题,希望在power query 中像Excel中的公式那样取数计算, 即在表中设定取数逻辑后,然后PQ自动取数计算;

我自己考虑是通过先将分步骤将项目计算逻辑拆分转为列表, 然后分解到行中,添加负号判断; 然后再与原表左连接取得金额数据,最后统一完成分组求和汇总,但是感觉步骤太长了,请问各位是否有更好的办法,比如核心语句 List.TransformMany 之类快速转换呀 ? 在此先谢过各位!

ThxAlot · 发表于 2021-3-31 22:19

还是要按日期分组，然后直接在分组后的Table上用Table.ReplaceValue把计算结果替换进去即可，

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZTRSgJBFIZfJfZaZc45u7riA3TXC4hPsrgYJEp4IWQRURdBXgSZN2ZW4NM4g71FM7oV7MzONjYLOywL/8fMOWe+djtAhsDfFiBuh0ElOGahXBmoBVtVFrcA5TfU+KDHh4/idC4mSzGZy39Bp1KQVomEUcoa3V0WfpK8P7UmSS4n8j3KHpmV6dm1mKw3qxl/eP68WG7vRzkeMFaMDA1I5OPRZnXD1z1xN83B0AaLDDDaLl7F09plT3UDJhTnl9uXMxdMw4CJeH+5+bhywcS7djVTgH278Ds854N3a7ua2gZQtstQ24xkqy0wAyx3mgxDNgwYMKRG6LdFGaZpw6gZriZAKUTdVgL1FOJ9dSjb0t/vA+hTTbJMeYzaFZQVSZ9mkv0yobAMpc8yGQ6nUFSG0udZoQruGJTMI+hjTbWwiIZltNhAi4popNPwX2LEg8WIfsRoOcsBZrTQnNRo4Ti50cJxkqOF42JH9GlH9GNH9GNH9GhH9GdH9GdH9GdH9GpH9GpH9GDHzhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [日期 = _t, 报表名称 = _t, 项目代码 = _t, 项目计算 = _t, 项目名称 = _t, 金额 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"日期", type text}, {"报表名称", type text}, {"项目代码", Int64.Type}, {"项目计算", type text}, {"项目名称", type text}, {"金额", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"日期"}, {{"ar", each let amt = [金额]
in Table.ReplaceValue(
_,
null,
each Record.Field(
[
#"1" = #"2" + #"12" - #"8",
#"2" = List.Sum(List.Range(amt,2,5)),
#"8" = List.Sum(List.Range(amt,8,3)),
#"12" = List.Sum(List.Range(amt,15,3)) - List.Sum(List.Range(amt,12,3))
],
Text.From([项目代码])
),
Replacer.ReplaceValue,
{"金额"}
)}}),
#"Expanded ar" = Table.ExpandTableColumn(Table.RemoveColumns(#"Grouped Rows",{"日期"}), "ar", {"日期", "报表名称", "项目代码", "项目计算", "项目名称", "金额"}, {"日期", "报表名称", "项目代码", "项目计算", "项目名称", "金额"})
in
#"Expanded ar"

复制代码

看见星光 · 发表于 2021-4-1 07:42

本帖最后由看见星光于 2021-4-1 07:51 编辑

假设计算式是动态的，附件参考：

PQ 表如何类似Excel进行公式计算.rar (22.61 KB, 下载次数: 14)
图片.png

agilersu · 发表于 2021-4-1 09:15

非常感谢, 我的excel2016 显示无法识别名称“Text.Middle”,请确保其拼写正确。调整为Text.Range 则OK, 另外还可继续优化,比如支持项目代码未必是2位数字,可以是数字与字母的结合,是否是使用ListPostions, ListRange取得数据

agilersu · 发表于 2021-4-1 12:04

看见星光发表于 2021-4-1 07:42
假设计算式是动态的，附件参考：

非常感谢, 我的excel2016 显示无法识别名称“Text.Middle”,请确保其拼写正确。调整为Text.Range 则OK, 另外还可继续优化,比如支持项目代码未必是2位数字,可以是数字与字母的结合,是否是使用ListPostions, ListRange取得数据,比如金额后再增加一个客户数量等等.

agilersu · 发表于 2021-4-1 12:15

ThxAlot 发表于 2021-3-31 22:19
还是要按日期分组，然后直接在分组后的Table上用Table.ReplaceValue把计算结果替换进去即可，

谢谢,还是需要创建中间表供使用,谢谢!

deadzlq · 发表于 2021-4-1 13:35

PowerQuery参考解法（F16:F18和F35:F37范围错了）

ThxAlot · 发表于 2021-4-1 16:58

agilersu 发表于 2021-4-1 12:15
谢谢,还是需要创建中间表供使用,谢谢!

不知道你所谓的“中间表”是什么，那就给你个一步到位的吧，

let
Calculation = Table.ExpandTableColumn(Table.RemoveColumns(Table.Group(Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="G_04"]}[Content],{{"金额", Int64.Type}}), {"日期"}, {{"ar", each let amt = [金额]
in Table.ReplaceValue(
_,
null,
each Record.Field(
[
#"01" = #"02" + #"12" - #"08",
#"02" = List.Sum(List.Range(amt,2,5)),
#"08" = List.Sum(List.Range(amt,8,3)),
#"12" = List.Sum(List.Range(amt,15,3)) - List.Sum(List.Range(amt,12,3))
],
[项目代码]
),
Replacer.ReplaceValue,
{"金额"}
)}}),{"日期"}), "ar", {"日期", "报表名称", "项目代码", "项目计算", "项目名称", "金额"}, {"日期", "报表名称", "项目代码", "项目计算", "项目名称", "金额"})
in
Calculation

复制代码

agilersu · 发表于 2021-4-1 20:46

deadzlq 发表于 2021-4-1 13:35
PowerQuery参考解法（F16:F18和F35:F37范围错了）

在此谢过,你采用的list.sum list.range ,若改为改为3楼的动态模式就更好了

李大维 · 发表于 2021-4-2 08:50

搞了一版，有点绕

		自动登录	找回密码
密码			免费注册

[求助] 有关power query 中如何像Excel中的公式那样取数计算?