excel - 如何根据分组变量计算 PowerQuery 中的百分位数?
问题描述
我有几列数据,我需要将“PERCENTILE”的excel版本转换为Powerquery格式。
我有一些代码作为函数添加,但不能准确应用,因为它不允许按类别和年份对数据进行分组。因此,Full Discretionary 1.5-2.5 AND 2014 中的任何内容都需要添加到百分位数数组中,同样,Full Discretionary 2.5-3.5 AND 2014 中的任何内容都需要添加到不同的百分位数数组中
let
Source = (list as any, k as number) => let
Source = list,
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Sorted Rows" = Table.Sort(#"Converted to Table",{{"Column1", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "TheIndex", each Table.RowCount(#"Converted to Table")*k/100),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Index] >= [TheIndex] and [Index] <= [TheIndex]+1),
Custom1 = List.Average(#"Filtered Rows"[Column1])
in
Custom1
in
Source
所以预期的结果是任何在 2 列 (Year,Category) 上匹配的东西都应该应用在同一个数组中。目前调用上述函数只会给我错误。我也尝试过使用分组和输出“最小值、中值和最大值”输出,但我还需要 10% 和 90% 的百分位数。
先感谢您
解决方案
根据其他网站上的一些发现和大量谷歌搜索(大多数人只想使用 DAX,但如果你只使用 Power Query,你就不能!)有人发布了一个非常有帮助的答案:
基本上:
/PercentileInclusive Function
(inputSeries as list, percentile as number) =>
let
SeriesCount = List.Count(inputSeries),
PercentileRank = percentile*(SeriesCount-1)+1, //percentile value between 0 and 1
PercentileRankRoundedUp = Number.RoundUp(PercentileRank),
PercentileRankRoundedDown = Number.RoundDown(PercentileRank),
Percentile1 = List.Max(List.MinN(inputSeries,PercentileRankRoundedDown)),
Percentile2 = List.Max(List.MinN(inputSeries,PercentileRankRoundedUp)),
Percentile = Percentile1+(Percentile2-Percentile1)*(PercentileRank-PercentileRankRoundedDown)
in
Percentile
以上将复制 Excel 中的 PERCENTILE 函数 - 您使用“新查询”和高级编辑器将其作为查询传递。然后在分组数据后调用它 -
Table.Group(RenamedColumns, {"Country"}, {{"Sales Total", each List.Sum([Amount Sales]), type number}, {"95 Percentile Sales", each List.Average([Amount Sales] ), 输入数字}})
在上面的公式中,RenamedColumns 是脚本中上一步的名称。更改名称以匹配您的实际情况。我假设预分组销售额列是“销售额”。分组列的名称是“Sales Total”和“95 Percentile Sales”。
接下来修改组公式,将 List.Average 替换为 PercentileInclusive:
Table.Group(RenamedColumns, {"Country"}, {{"Sales Total", each List.Sum([Amount Sales]), type number}, {"95 Percentile Sales", each PercentileInclusive([Amount Sales],0.95 ), 输入数字}})
这适用于我的数据集并匹配类似
推荐阅读
- java - Java - 类存在两次(在类路径和应用程序 jar 上)。LinkageError:ClassCastException
- c# - 在 IIS 服务器中从 PHP 运行 .exe?
- module - 如何从 PrestaShop 1.7+ 中的另一个模块调用模块静态函数
- rust - Rust 和 trait 边界约定 (?Sized)
- c++11 - 排列组合c++逻辑错误
- python - 带有 Pandas 数据框千位分隔符的 XlsxWriter
- sql-server - Pentaho 无法连接到 SQL Server 2017
- c++ - C++:构建选项“-j”是什么意思?
- python - 在python中将矩阵的值更改为高于阈值
- python - 在python中迭代一维数组的每个元素