代码之家 › 专栏 › 技术社区 › Przemyslaw Remin

Power BI中按类别索引,相当于分区上的SQL行数

m powerquery powerbi

Przemyslaw Remin · 技术社区 · 6 年前

如何在按列排序的Power BI中按类别添加索引。我寻找等效的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

2 回复 | 直到 6 年前

Alexis Olson 6 年前

链接@foxann为此提供了有效的方法。这是您需要的M代码:

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]),
    AddRanking = (table, column, newColumn) =>
        Table.AddIndexColumn(Table.Sort(table, {{column, Order.Descending}}), newColumn, 1, 1),
    #"Grouped Rows" = Table.Group(Source, {"Category"}, {{"Data", each _, type table}}),
    Transformed = Table.TransformColumns(#"Grouped Rows", {{"Data", each AddRanking(_, "Date", "Rank")}}),
    #"Expand Data" = Table.ExpandTableColumn(Transformed, "Data", {"Value", "Date", "Rank"}, {"Value", "Date", "Rank"})
in
    #"Expand Data"

Przemyslaw Remin 6 年前

感谢FoxanNg和AlexisOlson,他们提供了有趣的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