if then else で何とかする方って多く見受けられるのです。必要であればしょうがないけど他の方法を知らないのはよくないよねとは思うのです。例えば、null値を四則演算の解は評価不能でない限り null になってしまうので、if [Column1] = null then 0 else [Column1] という具合に列ごとに対処などしているわけです。計算の対象になる列が少なければよいですけど、多くなったり列名が変わったりしたとき大変じゃね？と。Power Query とエディターの機能は向上していて、以前できなかったことがさらっとできるようになっていることも多いですし。
で、わかりやすいところで、列の値を加算し列を追加するスタイルで。(Table.AddColumn)
どういうことか
Column1 から Column3 の和を列として追加。そのとき null は 0 として。
どうするか
集計の対象となる列を2つ以上選択し、[列の追加] - [統計] - [合計] もしくは、[列の追加] - [標準] - [加算]。列が2つの場合は [合計] を使う。
な！簡単でしょ！すべてではないけど十分なのかもしれない。
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
リストlist内の NULL 以外の値の合計を返します。リストに NULL 以外の値がない場合、NULL を返します。
そもそも null 値を無視した和を返すのでこのケースでもマッチするわけです。
どうするか、もっと
対象の列がすべて null 値になる場合も。
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 を追加する感じで。
ビューをこさえてもらった方がよいのだけど、どうしてもということであればクエリフォールディングを維持することもできる。
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
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
レコードrecord内のフィールド値のリストを返します。
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 を使うことを好まないだけなんですけど。