Power Query は Power Query エディターで比較的簡単に編集できるようになっています。それでも頻繁に設定を変更するといった使い方には向いていません。
業務で Power Query を使っていると、クエリの一部だけを変更して利用したいというニーズが出てくることがあります。
この記事ではこういったニーズへの対応として Power Query の処理をパラメータを使って変更する方法をご紹介します。必要な知識も含めて順を追って解説させていただきます。
この方法で Power Query の活用の幅がより広がると思いますので、是非参考にされて下さい。
では、いってみましょう！
Power Query のプログラムを理解する
Power Query の処理の一部を変更するにはどうしてもプログラムを編集する必要があります。
Power Query ではユーザーが処理した手順が M関数 というプログラム言語で記録されています。基本的に処理した順番に1行ずつ記録されいます。
プログラムを表示
では Power Query エディターで作成したクエリの中身（プログラム）を確認してみましょう。
Power Query エディターを開いて、メニューから「詳細エディター」をクリックします。
すると小ウィンドウが開き、プログラムが表示されます。
Power Query のプログラムの構成
Power Query のプログラムはVBAマクロのようなプログラムとは大きく異なり、図のような構成になっています。
ループ処理などが無く、上から順番に処理するだけのシンプルな作りなのが分かります。
M関数式を読み解く
また、M関数は以下のようになっており、比較的読み解き易い内容になっていますので、面倒がらずに中身を見てみましょう。丁寧に読めばすぐに理解できるかと思います。
設定変更したい部分を特定する
Power Query の処理はご自分で作られていると思いますので、処理手順のどの辺が設定変更したいかは、おおよそ検討がつくと思います。
また、前述の通りM関数の名称は処理動作の内容が英語になっているだけなので、これをヒントに設定変更したい部分を特定して下さい。
Power Query の変数の型
先ほどの図で解説したように、Power Query の処理は、次々と変数に値を代入していく処理になっています。
では変数にはどのような種類があるのでしょうか？変数には次のように値が一つのものから、テーブルのようにデータが沢山あるものまで様々です。
- 値：数値、日付、文字列など一つの値（”123.45″, ”2024/4/1″, ”Lorem Ipsum …” など）
- リスト：値が複数入ったもの（ {“Taro”,”Hanako”,”Daisuke”…} など）
- レコード：名前と値のセット（ [Name = “Taro”, Age = 22] ）
- テーブル：表
VBAなどのプログラム言語でも配列のように複数の値を持つものがありますから、似ているとも言えますが、やはりデータ処理に特化した M関数 ですから テーブルを丸ごと変数で扱えるところが特徴的 です。
この記事ではパラメータは 値 である前提で進めます。
パラメータ読み込み
ここでパラメータはセルに記入することにします。このセルの値を M関数で読み取る必要があります。そのコードが次になります。
val = Excel.CurrentWorkbook(){[Name="INPUT_CELL"]}[Content]{0}[Column1]このコードは “INPUT_CELL” という名前が付けられているセルの値を読み取り、”val” という変数に値を格納します。
このコードにより M関数式の中にセルの値を反映することができるようになります。
セルに名前を付ける方法は次のようになります。実際の操作は図のようになります。
- 名前をつけるセルを選択
- Excelのメニュー下の「名前ボックス」に名前を記入してエンター
パラメータを設定
セルから値を読み取った変数を先ほどの設定変更したい部位に入れ込めばパラメータの設定は完了です。実際にセルの読み取りのコードとパラメータを反映した結果は以下のようになります。
クエリを動作させる
ここまでできたら実際にクエリを動作させてみましょう。サイドウィンドウにクエリ一覧を表示させて、クエリ名の右側にある 「最新の情報に更新」のボタンを押します。
エラーへの対処
エラーが発生した場合は編集した行を良く点検してみて下さい。
エラー箇所を特定する
エラーが発生している箇所は Power Query エディターで図のように操作することで簡単に分かります。
また、良くあるエラーとしては以下のようなものがあります。
行末にカンマが無い
Power Query の M関数のプログラムは必ず１行ごとに最後にカンマを入れるのがルール になっています。これが無いとエラーになってしまいます。
また、letステートメントの最後の M関数式の行末にはカンマは入らない ことにも注意が必要です。
変数名が重複している
Power Query エディターでクエリを編集すると次々と自動で変数名が作成されていきます。これにユーザーが「詳細エディター」で編集した際に、既に使われている変数を指定してしまうと、エラーになります。
変数名は少々長くなってしまっても構わないので少し文字を加えるなどして重複を回避しましょう。
まとめ
Power Query の処理の内容は通常では Power Query エディターでしか変更することはできません。しかし、もう少し簡単に Power Query の処理の内容を変更できるようにしたいというニーズがあると思います。
この記事ではPower Query の M関数式でセルの値を読み込んでその値をパラメータとして活用することで、Power Query の処理内容を変更する方法をご紹介しました。
これをヒントに更に Power Query の活用方法を思い付いた方がおられるかもしれません。是非色々と工夫して Power Query をより便利に活用してみて下さい。
