powerbi - Power BI 中的按类别索引相当于分区上的 SQL row_number
问题描述
如何在 Power BI 的 M 中按类别添加索引并按列排序。我寻找等效的 SQL:
ROW_NUMBER() over(partition by [Category] order by [Date] desc
假设我们有一张桌子:
+----------+-------+------------+
| Category | Value | Date |
+----------+-------+------------+
| apples | 3 | 2018-07-01 |
| apples | 2 | 2018-07-02 |
| apples | 1 | 2018-07-03 |
| bananas | 9 | 2018-07-01 |
| bananas | 8 | 2018-07-02 |
| bananas | 7 | 2018-07-03 |
+----------+-------+------------+
期望的结果是:
+----------+-------+------------+-------------------+
| Category | Value | Date | Index by category |
+----------+-------+------------+-------------------+
| apples | 3 | 2018-07-01 | 3 |
| apples | 2 | 2018-07-02 | 2 |
| apples | 1 | 2018-07-03 | 1 |
| bananas | 9 | 2018-07-01 | 3 |
| bananas | 8 | 2018-07-02 | 2 |
| bananas | 7 | 2018-07-03 | 1 |
+----------+-------+------------+-------------------+
表的 PBI 代码:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyEktVtJRMgRiIwNDC10Dc10DQ6VYHSQ5I2Q5I1Q5Y2Q5Y7BcUmIeEIIkzZElTdAkLZAlTdEkLZElzZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, Value = _t, Date = _t])
in
Source
解决方案
感谢 Foxan Ng 和 Alexis Olson 提供有趣的 PBI 函数方法。我想为该集合添加其他方法。
PBI 方法,没有功能:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyEktVtJRMgRiIwNDC10Dc10DQ6VYHSQ5I2Q5I1Q5Y2Q5Y7BcUmIeEIIkzZElTdAkLZAlTdEkLZElzZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, Value = _t, Date = _t]),
#"Grouped rows" = Table.Group(Source, {"Category"}, {{"NiceTable", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Descending}} ), "Index",1,1), type table}} ),
#"Expanded NiceTable" = Table.ExpandTableColumn(#"Grouped rows", "NiceTable", {"Value", "Date", "Index"}, {"Value", "Date", "Index"})
in
#"Expanded NiceTable"
该解决方案受到 ImkeF 解释的启发:https ://community.powerbi.com/t5/Desktop/Custom-column-Index-or-Ranking-by-other-column/td-p/33864/page/3
这是我最喜欢的 R 方法。需要dplyr
包。我喜欢它的简单性。
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyEktVtJRMgRiIwNDC10Dc10DQ6VYHSQ5I2Q5I1Q5Y2Q5Y7BcUmIeEIIkzZElTdAkLZAlTdEkLZElzZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t, Value = _t, Date = _t]),
#"Run R Script" = R.Execute("library(dplyr)#(lf)output <- dataset %>% group_by(Category) %>% mutate(row_no_by_category = row_number(desc(Date)))",[dataset=Source]),
output = #"Run R Script"{[Name="output"]}[Value]
in
output
推荐阅读
- html - materializecss 的透明导航栏
- java - 如何将 InputStream 用于 RandomAccessFile 的一部分?
- c++ - 使用 C++ CommonAPI 编写服务器时,服务器进程中创建了多少个线程?
- java - 如何比较 2 LinkedHashMap 并获得键和值的差异?
- java - 使用 java io 从远程机器读取文件
- c# - CurrentDirectory 和 Excel-DNA
- javascript - 使用 jquery 更新图像源无法正常工作
- angular - Angular 编译器需要 TypeScript >=2.7.2 和 <2.8.0 但找到的是 2.8.3
- asynchronous - MySQL 和 Redis 的 Tornado 性能问题
- linux - 查明网络应用程序或服务器是否受到威胁