powerbi - Power Query 条件 sumif

Question

我需要添加一个列，对所有具有共同 id 的列的值列求和。但是，任何 id = null 都不会相加，而是等于 value 列。

上面的示例应导致：

TopPaymendId                            JournalLineNetAmount    TopAmount
fcbcd407-ca26-4ea0-839a-c39767d05403    -3623.98                -7061.23
fcbcd407-ca26-4ea0-839a-c39767d05403    -3437.25                -7061.23
ce77faac-1638-40e9-ad62-be1813ce9031    -88.68                  -88.68
531d9bde-3f52-47f3-a9cf-6f3566733af2    -152.23                 -152.23
8266dfef-dd14-4654-a6d2-091729defde7    229.42                  229.42
f8b97a47-15ef-427d-95e0-ce23cc8efb1f    -777                    -777
                                null    -3.01                   -3.01
                                null    -2.94                   -2.94
                                null    3312.5                  3312.5

标签： powerbipowerquery

Answer 1

此代码应该可以工作：

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    group = Table.Group(Source, {"TopPaymendId"}, {"TopAmount", each List.Sum([JournalLineNetAmount])}),
    join = Table.Join(Source,{"TopPaymendId"},group,{"TopPaymendId"}),
    replace = Table.ReplaceValue(join,each [TopAmount],each if [TopPaymendId] = null 
              then [JournalLineNetAmount] else [TopAmount],Replacer.ReplaceValue,{"TopAmount"})
in
    replace