Power Query 中的 DAX EARLIER() 函数

Question

在M/Power Query中是否有类似于EARLIER的等效函数？

比如，我有一个表格，其中DATE列包含很多不同的日期，LETTER列只包含较少的字母。现在我想要每个字母对应的最大日期。

在DAX中，我会使用类似于CALCULATE(MAX([Date]),FILTER(ALL(Table),[Letter]=EARLIER([Letter])))的语句。

那么在M中该怎么做呢？

谢谢！

- Tony

---

感谢您的回答。我明白了，它们需要比我所希望的更多的技术洞察力...我会尝试测试它们并标记为已解决。 - Tony

Answer 1

下面的代码中有2个解决方案。请注意，每个解决方案都以“PreviousStep”为基础，因此它们是独立的解决方案。

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    PreviousStep = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Letter", type text}}),

    // 1. Add a column to the original table with the MaxDate for each letter
    //    "earlier"  is just the name of a function parameter; it could as well have been "x" or "MarcelBeug"
    AddedMaxDate = Table.AddColumn(PreviousStep, "MaxDate", (earlier) => List.Max(Table.SelectRows(PreviousStep, each [Letter] = earlier[Letter])[Date])),

    // 2. Group by letter and get the MaxDate for each letter
    GroupedOnLetter = Table.Group(PreviousStep, {"Letter"}, {{"MaxDate", each List.Max([Date]), type date}})
in
    GroupedOnLetter

- MarcelBeug

Answer 2

简而言之，此功能没有完全匹配。但是，您可以使用其他方法来产生相同的结果。

为了重现由Microsoft提供的有关EARLIER函数的帮助所提供的示例，您可以使用以下代码（table1等于在排名之前给出的示例中给定的表）：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TVNNaxtBDP0rxuSoivn+uMYlLSUFE4f2YHIYd4d48Xq3rO1C/n01o1mc4+i9kZ6epP1+LcMa1o/94cvuOM3XCz0epHUgnccQ1m+wXytXGae8ekl/TpWhlACvBHrBDL8wdtc0dpWiLTgV0EVm1CrT9Trky4ooq016z5VnI2ij0OjKs402nVePM1XLrMgEcEaj8ZVU9czpxAmcAik1SlcxGSm2SX/5m4eoDVpToSJyc0z9WLEAwXgUrcX6a8hpzDNb4CAEhU5VuIjfzGk8XZoeGSVYpVBwd+X31zynfhjyjRM4A9FZ1NyWFhR7ymPX0hsJ0RuUbJ+s6DSzt96QtR4d96MK9m2Y/uVmfABtNVrWbSj2newc8iEtwjUoS401O2Rh5NQtyq0HZyNGFq4ZHs6Lz1aCjAopXmFV4I9uTtd+GlfbZfyR3IkafTOvJPlBneUPbj1GMCouMFkA6+f+/VhLcKjofp5aNmlBkKQ23JLs53QbrzSoVdkp3iYDWlgIzqBi6VJ9Jj7N6cxMA1ZSE16ga/XLTm3TOPZsPv8uora5SwNLMIIkK1Q8EF02bHs78xZJBS5alK1bCr1Mqbtro7+WfHPRoeZNk2Yh3XVpcNqBjgE9myuLrl3qaHg8GUUr5RYbVKlzP0kdLHhBJ9kOrsjfLQaWndCEWcZK8dfF7wcZIrkRUXNe7Ss6tzN8vR2WxTIQtMLQJl9Y023ux/d7o1JTHVOH0MyQ7hPv3isdh7F01gYFH5Aqvf7KF5akyLEYBYrmVpH0+5jz0C4nADEq+vYf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ProductSubcategoryKey = _t, EnglishProductSubcategoryName = _t, TotalSubcategorySales = _t]),
    table1 = Table.TransformColumnTypes(Source,{{"ProductSubcategoryKey", Int64.Type}, {"EnglishProductSubcategoryName", type text}, {"TotalSubcategorySales", Currency.Type}}, "en-US"),
    AddCount = Table.AddColumn(
              table1, 
              "SubcategoryRanking", //(a) is a parameter for function, which equals current record, and function should return value for new cell of "SubcategoryRanking"
              (a)=> Table.RowCount( 
                        Table.SelectRows( 
                        table1, //(b) equals whole table1. This function returns table filtered by given criteria
                        (b) =>  b[TotalSubcategorySales] < a[TotalSubcategorySales]) 
                    ) + 1,
              Int64.Type)
in
    AddCount

- Eugene

Answer 3

我认为你可以使用GroupBy函数按字母分组数据，并找到日期列的最大值。因此，你的代码应该如下所示。

= Table.Group(#"Previous step", {"Letter"}, {{"Max Date", each List.Max([Date]), type date}})

- Gangula