Power Query で null 値を含む列の合計をどうするか

if then else で何とかする方って多く見受けられるのです。必要であればしょうがないけど他の方法を知らないのはよくないよねとは思うのです。例えば、null値を四則演算の解は評価不能でない限り null になってしまうので、if [Column1] = null then 0 else [Column1] という具合に列ごとに対処などしているわけです。計算の対象になる列が少なければよいですけど、多くなったり列名が変わったりしたとき大変じゃね？と。Power Query とエディターの機能は向上していて、以前できなかったことがさらっとできるようになっていることも多いですし。

で、わかりやすいところで、列の値を加算し列を追加するスタイルで。(Table.AddColumn)

# どういうことか
Column1 から Column3 の和を列として追加。そのとき null は 0 として。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/193509/68cb8434-8d0e-76c2-b1b9-1c41944f959a.png)

## どうするか
集計の対象となる列を2つ以上選択し、[列の追加] - [統計] - [合計] もしくは、[列の追加] - [標準] - [加算]。列が2つの場合は [合計] を使う。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/193509/8aa8159a-a7e4-0900-2aac-7f9297a12337.png)
な！簡単でしょ！すべてではないけど十分なのかもしれない。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/193509/0a22d07f-c401-23d5-6694-f1722ad373f6.png)

```text:生成されたスクリプト
let
    Source = Table1,
    InsertedSum = Table.AddColumn(
        Source,
        "Addition",
        each
            List.Sum(
                {[Column1], [Column2], [Column3]}
            ),
        Int64.Type
    )
in
    InsertedSum
```
ポイントは、[Column1] + [Column2] + [Column3] ではなく、List.Sum が使われていること。

>**List.Sum**
>リスト <code>list</code> 内の NULL 以外の値の合計を返します。リストに NULL 以外の値がない場合、NULL を返します。

そもそも null 値を無視した和を返すのでこのケースでもマッチするわけです。

## どうするか、もっと
対象の列がすべて null 値になる場合も。

```text:対応させたスクリプト
let
    Source = Table1,
    InsertedSum = Table.AddColumn(
        Source,
        "Addition",
        each
            List.Sum(
                {[Column1], [Column2], [Column3]} & {0}
            ),
        Int64.Type
    )
in
    InsertedSum
```

List.Sum の引数のリストがすべて null でなければよいので、リストアイテムが 0 のリストを追加すればよい。

## クエリ フォールディングは維持できるか
Azure SQL database で試したらできた。
List.Sum の引数のlist に リストアイテムを追加する方法は クエリ フォールディングが効かなくなるので、値 0 の列を一時的に追加し 集計対象に 0 を追加する感じで。
ビューをこさえてもらった方がよいのだけど、どうしてもということであればクエリフォールディングを維持することもできる。

```text
    Source = Sql.Databases("****.database.windows.net"),
    ***** = Source{[Name="****"]}[Data],
    dbo_TEST1 = ****{[Schema="dbo",Item="TEST1"]}[Data],
// 0 の列を追加
    AddedCustomColumn = Table.AddColumn(
        dbo_TEST1,
        "AddedZero",
        each 0, 
        Int64.Type
    ),
    InsertedSum = Table.AddColumn(
        AddedCustomColumn,
        "Addition",
        each 
            List.Sum(
                {[Column1], [Column2], [Column3], [AddedZero]}
            ),
        Int64.Type
    ),
    RemovedOtherColumns = Table.SelectColumns(
        InsertedSum,
        {"Column1", "Column2", "Column3", "Addition"}
    )
in
    RemovedOtherColumns
```

```sql:発行されるSQL
select [_].[Column1] as [Column1],
    [_].[Column2] as [Column2],
    [_].[Column3] as [Column3],
    case
        when (([_].[Column1] is null and [_].[Column2] is null) and [_].[Column3] is null) and [_].[AddedZero] is null
        then null
        else (((case
            when [_].[Column1] is null
            then 0
            else [_].[Column1]
        end) + (case
            when [_].[Column2] is null
            then 0
            else [_].[Column2]
        end)) + (case
            when [_].[Column3] is null
            then 0
            else [_].[Column3]
        end)) + (case
            when [_].[AddedZero] is null
            then 0
            else [_].[AddedZero]
        end)
    end as [Addition]
from 
(
    select [_].[Column1] as [Column1],
        [_].[Column2] as [Column2],
        [_].[Column3] as [Column3],
        0 as [AddedZero]
    from 
    (
        select [Column1],
            [Column2],
            [Column3]
        from [dbo].[TEST1] as [$Table]
    ) as [_]
) as [_]
```
## 列名に依存しない方法
その時点ですべての列を対象としてしまうけれども、行は record として参照できるので。

>**Record.FieldValues**
>レコード <code>record</code> 内のフィールド値のリストを返します。

```text
let
    Source = Table1,
    InsertedSum = Table.AddColumn(
        Source,
        "Addition",
        each
            List.Sum(
                Record.FieldValues(_) & {0} // ← ココ
            ),
        Int64.Type
    )
in
    InsertedSum
```

# 思ったこと🙄 
table を扱う関数は使い機会も多く割と早くに覚えることはできるでしょう。ただ、それだけではできなかったり面倒だったりすることが思いのほか多く存在するのではないかと。なので、list や record を扱う関数も隔たりなく理解しておくと幸せになれるのかもしれない。少なくとも、使い道はわからなくてもどのような処理ができる関数が揃っているのか眺めておくだけでもかな。

そもそも Power Query で if then else を使うことを好まないだけなんですけど。

# その他
- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)