power查询中的迭代计算

Question

我有一个数据集：

Policy,     EXPIRY_DT,  TIMESTAMP, Investment
Prudential, 10/31/2019, 8/9/2019,  0
Prudential, 10/31/2019, 8/8/2019,  0
Prudential, 10/31/2019, 8/7/2019,  0
Prudential, 10/31/2019, 8/6/2019,  -20550
Prudential, 10/31/2019, 8/5/2019,  0
Prudential, 10/31/2019, 8/2/2019,  21300
Prudential, 10/31/2019, 8/1/2019,  30000
Prudential, 10/31/2019, 7/31/2019, 0
Prudential, 10/31/2019, 7/30/2019, 29250
Prudential, 10/31/2019, 7/29/2019, 0
Prudential, 10/31/2019, 7/26/2019, 33900

我的需求是：

对于特定“到期日”的特定“保单”，

如果今天的“投资”+前一天的“累计投资”< 0，

那么今天的“累积投资”= 0，

否则今天的“累计投资”=今天的“投资”+前一天的“累计投资”

输出应如下所示：

Policy,     EXPIRY_DT,  TIMESTAMP, Investment, Cumulative Investment
Prudential, 10/31/2019, 8/9/2019,  0,          93900
Prudential, 10/31/2019, 8/8/2019,  0,          93900
Prudential, 10/31/2019, 8/7/2019,  0,          93900
Prudential, 10/31/2019, 8/6/2019,  -20550,     93900
Prudential, 10/31/2019, 8/5/2019,  0,          114450
Prudential, 10/31/2019, 8/2/2019,  21300,      114450
Prudential, 10/31/2019, 8/1/2019,  30000,      93150
Prudential, 10/31/2019, 7/31/2019, 0,          63150
Prudential, 10/31/2019, 7/30/2019, 29250,      63150
Prudential, 10/31/2019, 7/29/2019, 0,          33900
Prudential, 10/31/2019, 7/26/2019, 33900,      33900

我可以在Excel工作表中实现，但是有没有办法在Powerquery中实现呢？

Answer 1

虽然我不知道您的伪代码将如何产生您指定的输出，但我相信这可能会帮助您入门。据我所知，在PowerQuery中做这件事并不是不可能做到的。完成此操作的最有效方法是使用以下代码创建计算列。

Cummulative Investment = 
    var _thisPeriod = 
        CALCULATE (
            SUM ( Investments[Investment] ) ;
            ALL ( Investments ) ; 
            Investments[Policy] = EARLIER( Investments[Policy] ) ; 
            Investments[EXPIRY_DT] = EARLIER( Investments[EXPIRY_DT] ) ;
            Investments[TIMESTAMP] = EARLIER( Investments[TIMESTAMP] ) 
        )
    var _prevPeriod = 
        CALCULATE (
            SUM ( Investments[Investment] ) ;
            ALL ( Investments ) ; 
            Investments[Policy] = EARLIER( Investments[Policy] ) ; 
            Investments[EXPIRY_DT] = EARLIER( Investments[EXPIRY_DT] ) ;
            Investments[TIMESTAMP] <= EARLIER( Investments[TIMESTAMP] ) - 1 
        )

RETURN
    IF ( _thisPeriod + _prevPeriod < 0 ; 0 ; _thisPeriod + _prevPeriod )

我不知道这是否是你想要的，但这应该是探索自我的好基础

Answer 2

在Power Query中，如果你不怕M语言，那么你可以在O(n)中高效地做到这一点。如果性能不是问题，在M as O(n^2)中有更简单的方法可以做到这一点。

This solution就是一个很好的例子，说明了如何使用List.Accumulate函数来实现这一点。

let
    StartingTable = <Table Source Goes Here>,
    SortAndBuffer = Table.Buffer(Table.Sort(StartingTable,{{"TIMESTAMP", Order.Ascending}})),
    TableType = Value.Type(Table.AddColumn(SortAndBuffer, "Cumulative Investment", each null, type number)),
    Cumulative = List.Skip(List.Accumulate(SortAndBuffer[Investment],{0},(cumulative,Investment) => cumulative & {List.Last(cumulative) + Investment})),
    AddCumulativeCol = Table.FromColumns(Table.ToColumns(SortAndBuffer)&{Cumulative},TableType)
in
    AddCumulativeCol

这段代码的关键部分是：

Cumulative =
    List.Skip(
        List.Accumulate(
            SortAndBuffer[Investment],
            {0},
            (cumulative,Investment) => cumulative & {List.Last(cumulative) + Investment}
        )
    )

List.Accumulate function从单个元素列表{0}开始，对于Investment列列表中的每个值，它获取该(最初为单值)列表，然后添加上一个值加上当前Investment值。

这将生成列表

{0,33900,33900,63150,63150,93150,114450,114450,93900,93900,93900,93900}

然后List.Skip去掉了开头的零。

最后，使用Table.FromColumns将此列表作为列添加到起始表中。

在DAX中，您可以编写一个相当简单的计算列，如下所示：

Cumulative Investment =
VAR CumulativeSum =
    CALCULATE (
        SUM ( 'Table'[Investment] ),
        ALLEXCEPT ( 'Table', 'Table'[Policy], 'Table'[EXPIRY_DT] ),
        'Table'[TIMESTAMP] <= EARLIER ( 'Table'[TIMESTAMP] )
    )
RETURN MAX(CumulativeSum,0)