前回に引き続き、今回もエラーの対処方法を紹介していこう。今回は「列名」を変更したことにより発生するエラーや、「データ型」に違反しているデータ(Error)への対処方法を紹介する。パワークエリのエラーに対処する方法のひとつとして、参考にして頂ければ幸いだ。

  • エラーの対処法(列名の変更、データ型の違反)

列名の変更が原因のエラー

パワークエリで自動処理を実現した後に「列名」(フィールド名)を変更すると、ほぼ100％の確率でエラーが発生する。今回は、こういったエラーに対処する方法を紹介していこう。また、「データ型」に違反しているデータ(Error)の扱いについても補足しておこう。

今回も具体的な例を用いながら解説を進めていく。以下の図は、ある企業の社員名簿をまとめたデータ表だ。

  • データ取得元のExcelファイル

このデータ表を「Power Query エディター」に取得し、「所属」の列を「支社」と「部署」に分割する処理を行うと、以下の図のようになる。

  • パワークエリで処理したデータ表

「社」の文字を基準にデータを2列に分割しただけの簡単な処理ではあるが、このクエリを例に「列名」を変更したときの挙動について説明していこう。

たとえば、クエリを作成した後に組織の改編があり、部署名のデータを以下の図のように変更したとしよう。また、それにあわせて列名(フィールド名)を「所属」から「支社・チーム」に変更したとする。

  • 部署名を変更したデータ

この場合、「すべて更新」をクリックして自動処理を実行しようとすると、「テーブルの列 '所属' が見つかりませんでした。」というエラーが表示されてしまう。

  • 列名が見つからないことを示すエラー

これまでと同様に「社」の文字を基準にデータを分割するだけの処理なので、「クエリの内容を変更しなくてもよい」と思うかもしれないが、このままでは正しくクエリが機能してくれない。

というのも、クエリの処理内容を記したM言語は、「列名」で処理するデータを識別する仕様になっているからだ。つまり、以前は存在していた「所属」という列に何らかの処理を施そうとしても、現時点では「所属」という列が存在しないため、「処理対象の列が見つからない」というエラーが発生してしまうのだ。

これを正しく動作させるには、現在の「列名」に合わせてM言語を書き換えてやる必要がある。その手順を紹介しよう。

列名を修正するときの対処法

エラーが発生しているクエリをダブルクリックして「Power Query エディター」を開くと、クエリ名の左に「警告マーク」が表示されているのを確認できる。また、画面中央にはエラーの内容を示すメッセージが表示されている。ここに「エラーに移動する」ボタンが表示されている場合は、このボタンをクリックする。

  • 「警告マーク」と「エラーに移動する」ボタン

すると、エラーが発生しているステップが自動選択される。このステップのM言語を詳しく見ていこう。以下の図に示したアイコンをクリックし、M言語の領域を拡張する。

  • エラーが発生しているステップが自動選択される

M言語の記述がすべて表示される。通常、この記述には「古い列名」が残っているはずだ。これを「新しい列名」に変更する。今回の例の場合、「所属」→「支社・チーム」という修正を行えばよい。

  • 列名の修正(1)

  • 列名の修正(2)

ちなみに、上図に示したステップでは、取得したデータ表の1行目をヘッダー(列名)に昇格した際に、各列のデータ型を自動指定する処理が行われている。具体的には、「氏名」の列にtype text(テキスト型)、「所属」の列にtype text(テキスト型)、……という具合に、各列のデータ型を指定していく処理が行われている。この処理を正しく完了させるには、M言語に「現在のデータ表に存在する列名」が記述されていなければならない。よって、「所属」→「支社・チーム」の修正が必要となる。

このように「各列のデータ型」を自動指定する処理にも「列名」が使用されている。仮に、列名を変更した列が処理の対象になっていなかったとしても、「データ型」を自動指定する時点で「列名の不一致」が生じてしまう。このため、列名を変更すると、ほぼ100％の確率でエラーが発生する。

話を元に戻して、エラーの修正手順の解説を進めていこう。今回の例の場合、まだ修正作業は完了していない。クエリ名の左には依然として「警告マーク」が表示されている。この場合は「最後のステップ」を選択しなおすと、再び「エラーに移動する」ボタンが表示される。「エラーに移動する」ボタンをクリックして、エラーが発生しているステップへ移動する。

  • 別のエラーを確認する操作

今度は「区切り記号による列の分割」のステップが自動選択された。このステップのM言語にも「古い列名」が残っている。これを「新しい列名」に変更する。

  • 列名の修正(1)

  • 列名の修正(2)

念のため、このステップの処理内容について補足しておこう。このステップでは、「社」の文字を基準に「所属」の列を2列に分割する処理が行われていた。ただし、現在のデータ表に「所属」という列はなく、「支社・チーム」という列名になっている。よって、「所属」→「支社・チーム」の修正が必要になる。

以降も同様の手順で作業を進めていく。今回の例の場合、先の修正を行った時点で「警告マーク」が解消され、「最後のステップ」を選択してもエラーメッセージは表示されなくなった。

  • エラーを解消したクエリ

以上でM言語の修正は完了。「閉じて読み込む」をクリックしてデータ表をExcelに出力すると、以下の図のようになる。クエリによる自動処理が正しく完了し、「社」の文字を基準にデータが2列に分割されていることを確認できるだろう。

  • 出力したデータ表を更新した様子

なお、分割後の列名は特に修正していないので、これまでと同様に「支社」と「部署」という列名になっている。これを「支社」と「チーム」などに変更したい場合もM言語の修正が必要になる。今回の例の場合、最後のステップで列名を変更する処理を行っているので、このM言語を書き換えてやればよい。

  • 分割後の列名を変更する場合

このように、各ステップで「何の処理を行っているか?」を考えながら対処していかなければならないケースもある。単にエラーを解消するだけなら「エラーに移動する」ボタンで対応できるが、それ以上の修正を行うには、ある程度、M言語を読み取りながら作業を進めていかなければならない。

データ型の違反エラーに対処するには?

最後に、「データ型」に違反しているために生じるエラーについて紹介しておこう。今度は、以下のExcelファイルを例に解説を進めていく。

  • データ取得元のExcelファイル

このExcelファイルを「Power Query エディター」に取得し、「売上」の列に「整数」ののデータ型を指定すると、もともと「定休日」と入力されていたセルがErrorになる。「定休日」はテキスト型のデータであり、整数としては扱うことはできないので、これは当然の結果と考えられるだろう。

  • データ型の違反を示すエラー

このエラー(Error)を解消する方法は、「エラーの削除」と「エラーの置換」の2種類ある。これらのコマンドは、列名の右クリックメニューにも用意されている。

  • 右クリックメニューに用意されているエラー処理

「エラーの削除」を選択した場合は、エラーが発生している行を削除する処理が行われる。「定休日のデータ」を残しておく必要がないのであれば、この方法でErrorを解消してやればよい。これが、ひとつめの対処法となる。

  • 「エラーの削除」を選択した場合

「エラーの置換」を選択した場合は、以下の図のような設定画面が表示される。ここでは「Errorをどんな値に置き換えるか?」を指定すればよい。通常、定休日の売上は0(ゼロ)になるはずなので、「0」を指定するのが妥当といえるだろう。

  • 「エラーの置換」を選択した場合(1)

すると「Error」→「0」の置換が行われ、すべてのデータを整数として扱えるようになる。これが、ふたつめの対処法となる。

  • 「エラーの置換」を選択した場合(2)

そのほかの対処法として、「Errorをそのまま放置する」という手もある。これまでに紹介してきたエラーとは違い、各データのErrorはクエリが動作しなくなるほど深刻なものではない。Errorのまま放置しておいても、以降の処理を問題なく進められるケースもある。

たとえば、「カスタム列」を使って客単価(売上/客数)の計算を行ったとしよう。「売上」がErrorの行は「客単価」もErrorになってしまうが、クエリ(自動処理)そのものは問題なく動作してくれる。

  • 「客単価」を計算する列を追加

その後「閉じて読み込む」をクリックしてExcelにデータ表を出力すると、Errorのセルは空白セルとして扱われるようになる。つまり、最終的にはデータがnull(空白)であった場合と同じ結果になる。

  • ErrorのままExcelに出力した例

このように、各データのErrorは放置しておいても大丈夫なケースもある。安易に数値の0(ゼロ)に置換してしまうと、置換後の「0」も数値データとして扱われるため、平均などの計算に影響を及ぼしてしまう恐れがある。

Errorを「削除する/置換する/放置する」の問題は、以降の処理内容に応じてベストな対処方法を選択しなければならない。よって、一概に「こうすべき」とは言えない。状況に応じて、各自で対処方法を検討していく必要がある。

ということで、本連載は今回で最終回となる。パワークエリを利用することで、さまざまな処理を自動化、もしくは関数なしで処理できる、と実感できたのではないだろうか? さらにM言語を学習すれば、より高度な処理も実現できるようになる。

データを処理する際に「パワークエリを使う」という選択肢も選べるようになれば、もっともっと便利にExcelを活用できるようになる。この機会にぜひ、パワークエリの活用も視野に入れてみるとよいだろう。