前回の連載に引き続き、今回も「ステップの操作」について解説していこう。今回、紹介するのは、ステップの順番を並べ替える方法だ。この操作そのものは特に難しいものではないが、状況によってはエラーが発生してしまうケースがある。その理由を探るために、「ステップ」と「M言語」の関係についても簡単に紹介していこう。

ステップの並べ替え

クエリ(自動処理)の各ステップで実行される工程は、「M言語」と呼ばれるプログラムで記述されている。ということで、今回は「ステップ」と「M言語」の関係について簡単に紹介していこう。

  • ステップとM言語

今回も、フォルダー内にあるExcelファイルを「1つのデータ表」に結合するクエリ(自動処理)を例に解説を進めていく。その処理手順は、以下のようになっている。

＜処理手順＞
(1)「4月の売上」フォルダー内にあるファイルを結合してデータ表を作成する
(2)結合したデータ表から「合計の行」を削除する
(3)取得元のファイル名をもとに「日付」のデータを作成する
　(3-1)「抽出」コマンドで日付の文字を抜き出し、新しい列を作成
　(3-2)作成された列の名前を「日付」に変更
　(3-3)「日付」の列のデータ型を「日付」に変更
　(3-4)「日付」の列を左から2番目へ移動
　(3-5)「Source.Name」の列を削除する
(4)加工したデータ表をExcelに出力する

  • クエリに登録されているステップの一覧

各ステップが「どの工程を担当しているのか？」を把握しやすくするために、ステップ名を以下のように変更した状態で解説を進めていこう。

  • 各ステップの名前を変更

まずは、ステップの並び順を変更する方法だ。ステップの順番を並べ替えたいときは、各ステップを上下にドラッグ&ドロップすればよい。

  • ステップの順番を並べ替える操作

上図は、「合計の行を削除」のステップを一番下へ移動した例だ。この場合、自動処理の最後の工程で「合計の行」が削除されることになる。

状況を確認しておこう。最後から2番目のステップを選択する。この時点では、まだ「合計の行」を削除する工程は行われていない。よって、「合計の行」はデータ表に残ったままである。一方、「日付の列」を作成する処理は完了済みとなっている。

  • 各ステップの処理後の状況を確認(1)

続いて、最後のステップを選択する。ここで「合計の行」を削除する工程が実行され、データ表から「合計の行」が削除される。

  • 各ステップの処理後の状況を確認(2)

今回の例の場合、「合計の行」を削除する工程はどのタイミングで実行しても構わない。よって、順番を入れ替えても特に問題は生じない。ただし、状況によっては、ステップの並べ替えがエラーの原因になるケースもある。

ステップを操作したときに発生するエラー

ということで、エラーが発生する例を紹介していこう。今度は、「列名を日付に変更」のステップを一番下へ移動してみよう。

  • ステップの順番を並べ替え

すると、以下の図のようなエラー画面が表示される。この画面にある「エラーに移動する」ボタンをクリックすると……、

  • エラーが発生しているステップへ移動

エラーが発生しているステップが自動選択される。エラーの内容は、「テーブルの列‘日付'が見つかりませんでした。」と表示されている。

  • エラーの確認

普通に考えると、列名を「日付」に変更する工程は「どのタイミングで行っても問題ないはず……」と思うかもしれない。しかし、実際には上手くいかない。この挙動を理解するには、M言語について学んでおく必要がある。

ステップとM言語の関係

各ステップの処理内容は「M言語」と呼ばれるプログラミング言語で記述されている。このM言語は「Power Query エディター」で処理手順を指定した際に、自動的に記述される仕組みになっている。このため、M言語の書き方を知らなくてもパワークエリを活用することが可能である。これが「パワークエリはノーコードでも使える」と言われる所以である。

ただし、エラーに対応するために、「M言語がどのように記述されているか？」を確認しなければならないケースもある。先ほどのエラーを例に詳しく解説していこう。

まずは、エラー発生時よりも1つ前のステップから見ていこう。1つ前のステップを選択すると、画面上部に「=Table.AddColumn……」というコードが表示されるのを確認できる。これが、各ステップのM言語となる。なお、M言語の記述が途中までしか表示されていない場合は、以下の図に示したアイコンをクリックすると、M言語の領域が拡張され、M言語の記述全体を確認できるようになる。

  • 各ステップのM言語を確認(1)

  • 各ステップのM言語を確認(2)

このM言語には、「Source.Name」の列から「"の"より前にある文字」を抽出して、新しい列を作成する、という処理が記述されている。また、新しく作成する列の名前は、「区切り記号の前のテキスト」と指定されている。

続いて、エラーが発生したステップを選択する。このステップのM言語は、以下の図のように記述されている。

  • データ型を「日付」に変更するM言語

このM言語には、「日付」の列のデータ型をdate(日付)に変更する、という処理が記述されている。一方、1つ前のステップで作成した列の名前は「区切り記号の前のテキスト」になっている。つまり、現時点において「日付」という名前の列は存在しないことになる。これがエラーの原因だ。

本来であれば、列の名前を「区切り記号の前のテキスト」→「日付」に変更する処理が先に実行されているはずだが、このステップを最後に移動してしまったため、「日付」の列が見つからない……、というエラーが発生してしまう。

このエラーを解消するには、列名を「日付」に変更するステップを元の位置に戻してあげる必要がある。

  • ステップの順番を元に戻す

すると、列の名前を「区切り記号の前のテキスト」→「日付」に変更する処理が先に実行され、その後、「日付」の列のデータ型をdate(日付)に変更する、という処理手順になり、矛盾が解消される。

このように、M言語は処理内容の中に「列名」が記述されているケースが多い。このため、「列名を変更するステップ」の並び順を変更すると、エラーが発生してしまう可能性が高くなる。念のため、注意しておく必要があるだろう。

参考までに、他のステップについてもM言語の記述を確認しておこう。次のステップは「列の並べ替え」を行う工程だ。

  • 列の順番を並べ替えるM言語

このM言語には、列の並び順を「Source.Name」→「日付」→「提供方法」→「分類」→「数量」→「売上」の順番に変更しなさい、という処理が記述されている。

ちなみに、このM言語にある「データ型を日付に変更」の部分はステップ名を示しており、「どのデータ表に対して処理を行うか？」を指定する部分となる。言い換えると、1つ前のステップで得られた結果(データ表)に対して「列の並べ替え」を行う、という処理になる。

以降のステップについても見ていこう。「Source.Nameの列を削除」のステップには、以下の図のようにM言語が記述されている。これにより「Source.Nameの列」を削除する処理が実行される。

  • Source.Nameの列を削除するM言語

最後のステップとなる「合計の行を削除」には、以下の図のようなM言語が記述されている。

  • 合計の行を削除するM言語

このM言語には、「数量」の列が"合計"でない行だけを抽出(表示)する、という処理が記述されている。すなわち、「合計の行」を削除する、という処理になる。

クエリ全体のM言語を表示

各ステップではなく、クエリ全体についてM言語の記述を確認することも可能だ。この場合は、「ホーム」タブにある「詳細エディター」をクリックすればよい。

  • 「詳細エディター」の表示

最初は、各行の記述が折り返されずにM言語が表示されている。「表示オプション」をクリックし、「ワードラップを有効にする」をONにすると、各行の記述が折り返して表示され、M言語の全体像を確認できるようになる。そのほか、空白文字の記号を表示したり、行番号を表示したりすることも可能となっている。

  • 表示オプションの指定

「詳細エディター」を閉じて元の画面に戻るときは、「キャンセル」ボタンをクリックすればよい。

  • 「詳細エディター」の終了

ちなみに、「詳細エディター」でM言語を直接修正することも可能となっている。この場合は、「完了」ボタンをクリックして修正内容を反映すればよい。

「詳細エディター」は上級者向けの機能となるため、この画面は参考程度に見ておけば十分だ。基本的には、プログラミングに慣れている方が「細かなカスタマイズを行うために使用する画面」と捉えておけばよいだろう。

もちろん、M言語の記述方法を知らなくてもパワークエリを使用することは可能である。「Power Query エディター」で処理手順を指定すると、そのM言語が自動的に記述されるため、「自分でM言語を記述する機会は滅多にない」と考えて構わない。

「Power Query エディター」の終了

最後に、「Power Query エディター」を終了するときの操作について紹介しておこう。これまでは「閉じて読み込む」のアイコンをクリックして「Power Query エディター」を終了していた。

そうではなく、単純に「Power Query エディター」を終了したいときは、ウィンドウの右上にある「×」をクリックすればよい。すると、以下のような確認画面が表示される。

  • 変更の保持を尋ねるダイアログ

この画面で「破棄」ボタンをクリックすると、今回、「Power Query エディター」で編集した内容が破棄され、クエリを編集前の状態に戻すことができる。

「保持」ボタンをクリックすると、今回の編集内容をクエリに反映させた状態で「Power Query エディター」を閉じることができる。

ただし、「保持」は「保存」ではない点に注意すること。「保持」ボタンをクリックしても「Power Query エディター」で編集した内容がExcelファイルに保存される訳ではない。編集内容をファイルに保存するには、続けて「上書き保存」を実行しておく必要がある。忘れないように注意しておこう。