久しぶりの更新になってしまいました。
今回は、パワークエリでピボット変換した後のステップで、指定したい列が存在しない問題について書いてみたいと思います。
この記事でやりたいこと
まず、この記事でやりたいことを確認しておきましょう。
今回の問題は、パワークエリでピボット変換した後にまだ加工ステップがある場合に起きやすい問題と言えます。
まずこちらをご覧ください。
左のデータから右のデータに変換するクエリを作ったとします。
クエリのログはこの通りです。
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"No", Int64.Type}, {"日付", type datetime}, {"商品コード", type text}, {"数量", Int64.Type}}),
ピボットされた列 = Table.Pivot(変更された型, List.Distinct(変更された型[商品コード]), "商品コード", "数量", List.Sum),
挿入された合計 = Table.AddColumn(ピボットされた列, "合計", each List.Sum({[A001], [A002]}), Int64.Type)
in
挿入された合計
取り込んだデータをピボット変換で商品コードを列に展開し、その合計列を追加しています。
ちなみに商品コードはA001とA002の2種類しかありません。
で、もし取り込むデータの商品コードがA001かA002のどちらかしかない場合はどうなるのでしょうか。
このように合計欄は空欄となり、編集画面で見ても合計欄がエラーになっています。
理由は、合計するはずのA002列が無いためこのようなエラーが発生します。
これをどうするか、というのが今回のテーマです。
ピボット変換後のエラー対応方法
今回のエラーはA002列が無いから起きるのです。
であれば、A002列を作ればいいですよね。
であれば取込データとは別に、「フォーマットデータ」なるものを用意して取込データに追加すればいいのです。
フォーマットデータについて
フォーマットデータはこのようなテーブルを用意すればOKです。
この時の注意点がこちら。
- テールの列数・列名は取込データに合わせる。
- ピボット展開したい項目はすべて入れる。
商品コードが追加された場合はここに追加すればOKです。
パワークエリの操作
取込データとフォーマットデータをパワークエリに取り込みます。
そして、取込データにフォーマットデータを「追加」します。
取込データの編集画面で「クエリの追加」からフォーマットデータ（ここでは「テーブル7」）を選択します。
取込データにフォーマットデータが追加されたのが下の図で、この状態でピボット変換します。
ピボット展開する列[商品コード]を選択した状態で、変換タブ「列のピボット」を選択します。
次に値列には[数量]を選択し、詳細設定オプションより集計関数が合計になっていることを確認してOKします。
そして実行結果がこちらです。
このようにピボット展開されました。
あとはフィルタでフォーマットデータの行を外し、合計列を追加して完成です。
合計列の追加について
合計列の作り方については、列の追加タブの「統計」から「合計」を選択する方法、「標準」の「加算」を選択する方法の二つがありますが、「加算」はお勧めしません。
やってみると分かるのですが、「加算」だとnull値の場合計算されないためです。
ですのでnull値でも計算される「合計」を使うことをお勧めします。
ちなみに、今回のようにA002列がすべてnull値の場合、A002列はデータ型がテキストとして認識されているので、いったん数値型に変換してからA001列とA002列を選択し、統計ボタンから合計を選択することで合計列を追加することができます。
最後に
ピボット変換した場合、その後のステップで列が存在しない場合の対処法についてお届けしました。
ポイントは「ないなら作ってしまえ！」これに限ると思います。
今回のケース、パワークエリでなくても取り込んだデータをピボットテーブルで処理して同じ表を作ることは可能かもしれません。
しかし、表のフォーマットが決まっている場合など、データの加工をパワークエリで完結したい場合も考えられますので、頭の片隅に入れておきたいテクニックかなと感じました。
最後まで読んでくださり、ありがとうございました！
