セルの値でクエリを変化させる(パラメータクエリの基本)
先日、VBAのセミナーを受講された山内さんと、セミナーが終わってから雑談していたのですが、その中でPower Queryの話題になりました。実務でPower Queryを便利に使っているのですが、ときどき、あるいは定期的に、対象のファイル名や、保存されているフォルダ名などが変更されると。それを毎回手動で修正しなければならないので、なんとかなりませんかね？みたいな。これ、実現可能だということは知っていました。パラメータクエリという仕組みを使えば、セルの中に入力してある値によって、クエリの内容を自動的に変化させることができます。対象のファイル名やパスが変わったら、セルの内容を変えることで対応できるんです。知ってはいましたが、この手のやり方は、セミナーでは解説してきませんでした。理由は「そのためには、自分でM言語を書き換える」必要があるからです。私は、「ExcelユーザーはM言語まで手を出さない方がいい」と考えています。M言語って、すごく難しいからです。そこまでして何かの仕組みを作るより、運用や、Excelの機能やマクロで対応すればいいと。それが、ExcelユーザーがPower Queryを活用する上での"超えるべきではない一線"のようなものだと感じています。でも、Power Queryを実務で使う機会が増えてくると、どうしてもこういう仕組みが必要です。それも分かります。なので、パラメータクエリの基本的な考え方や、よくやるであろう例をいくつか解説します。まずは、基本的なやり方です。
やり方は2種類ある
まず大事なことは、パラメータクエリの方法は2種類あるということです。どちらがいい、という話ではありません。ケースに応じて使い分けてください。ここでは、2種類とも紹介します。
・パラメータを別のクエリとして作る
ここでは、上図のCSVファイルを扱います。「C:\Work\Sample.csv」です。まずは、CSVファイルをPower Queryエディタで取得します。
「名前」列を"田中"で絞り込みます。
Excelのワークシート上に読み込みます。
今回、絞り込みで指定した"田中"を、ワークシート上で変更できるようにしてみます。
まず、上図のように名前を入力する場所を作ります。今回は、この入力欄をテーブルに変換します。
テーブルには、もれなく名前が定義されます。標準では「テーブル1」「テーブル2」などですが、ここでは、テーブルの名前を「UserName」に変更します。この後、このテーブルをPower Queryエディタで取得するのですが、そのときのクエリ名称も「UserName」になります。
アクティブセルをテーブル「UserName」内に置いて、[データ]タブ[テーブルまたは範囲から]を実行します。
実行するとテーブル「UserName」がPower Queryエディタで取得されます。クエリの名前が「UserName」になっていることを確認してください。このクエリ名は、あとで入力します。さて、取得したテーブルの"名前が入力されているところ"(ここでは"早見")を右クリックして、表示されるメニューからドリルダウンを実行します。
下図のような結果になります。
「ドリルダウン」とは何かは、ちょっと難しい話になりますので詳細は割愛します。ザックリとしたイメージで言うと、テーブルを値に変換するような作業です。数式バー(みたいなところ)に表示されている
= 変換された型{0}[担当]
の「変換された型」というのは、前のステップで実行された結果のテーブルを意味しています。「{0}」はデータの先頭行を表しています。先頭が0で、以降1・2・3…と続くインデックス(行番号みたいなもの)です。最後の「[担当]」は、読み込んだテーブル「UserName」に入力されていた"列見出し(タイトル)"です。つまり、前のステップで読み込んだテーブルのうち、[担当]列の先頭行(0行目)を値に変換した、みたいな意味です。さて、このクエリ「UserName」の元になるテーブルは、すでにブック上で存在していますから、あらためてブックに読み込む必要はありません。そこで、Power Queryエディタ[ホーム]タブ左端にある[閉じて読み込む]ボタンの[閉じて次に読み込む...]で「接続の作成のみ」を選択して[OK]ボタンをクリックします。
さぁ、準備は整いました。Excel画面の右端に表示されている[クエリと接続]で「Sample」をダブルクリックし、Power Queryエディタを起動してください。クエリ「Sample」が選択されている状態で、[ホーム]タブの[詳細エディター]をクリックします。
これからの操作は、数式バー(みたいなところ)でも可能ですが、今後のことを考えて、詳細エディタでの作業をご紹介します。表示された詳細エディタに表示されている内容が、このクエリの実体です。
let式の、最後の1行
フィルターされた行 = Table.SelectRows(変更された型, each ([名前] = "田中"))
の「"田中"」部分を、「UserName」に書き換えます。
フィルターされた行 = Table.SelectRows(変更された型, each ([名前] = UserName))
この行では、最初「[名前]列を"田中"という文字列で絞り込みなさい」という指示でしたが、それを「[名前]列を、クエリ「UserName」の結果で得られる文字列を使って絞り込みなさい」に変更したわけです。[OK]ボタンをクリックすると、この時点ですでに"早見"で絞り込まれます。
Power Queryエディタ[ホーム]タブ左端にある[閉じて読み込む]ボタンをクリックしてExcelに戻ると、ワークシート上の表も"早見"で絞り込まれています。
セルE2の名前を変更して、[データ]タブの[すべて更新]などを実行すると、セルE2の名前で絞り込まれた状態に更新されます。
これは、次のようなイメージです。
本来の目的であるクエリ(ここでは"Sample")とは別に、パラメータを受け取る専用のクエリ(ここでは"UserName")を作り、パラメータ受け取り用のクエリで取得したクエリの結果(ここでは"日高")を、本来のクエリ内で使用して、その結果をExcelに読み込みます。ポイントは「パラメータを受け取る専用のクエリ」を用意することです。
・パラメータをM言語で取得する
先の方法は、ワークシート上で変更する情報(ここでは名前)を、テーブル形式で作成しておき、そのテーブルを別の新規クエリとしてPower Queryに取得し、取得したクエリの結果を絞り込みで利用するという方法でした。書き換える箇所も少ないですし、特に問題がないのでしたら、この方法で十分でしょう。
セル内で変化させる値(パラメータ)を取得して、クエリの内容を変更する方法は、もうひとつあります。どちらがいい、という話ではなく、どちらもケースバイケースで使います。それどころか、この方法でないと実現できないケースもあります。それは、パラメータを受け取る専用のクエリを使うのではなく「既存クエリの内容を書き換えることで、既存クエリ内で直接セルの値を取得して使う」方法です。イメージとしては、次のような感じです。
M言語を直接編集するので、ちょっと難しいかもしれませんけど、この方法でないとできないケースもありますので覚えておきましょう。
では、手順を解説します。まず、変化させるセル(パラメータ)に「名前」を定義します。セルやセル範囲に名前が定義されていないと、Power Queryでは扱えないからです。名前を定義するにはいくつかの方法がありますが、最も簡単な方法のひとつは、名前を定義したいセルを選択して、名前ボックスに定義したい名前を入力します。
ここでは「UserName」という名前を定義しました。さて、今回も上記で使った「C:\Work\Sample.csv」の「名前」列を絞り込むという例で解説します。ここでは、そのクエリ「Sample」が作成済みだとします。
Power Queryエディタでクエリ「Sample」を開き、「詳細エディター」をクリックします。実行すると[詳細エディター]が開きます。
「フィルターされた行 = 」と書かれた先頭にカーソルを置いてEnterキーを押します。
この「フィルターされた行 = 」のところで絞り込みの条件を「"田中"」に特定していますが、この「"田中"」を可変にしたいので、その前の行(ステップ)で、セルの値を取得します。実行すると空の行が挿入されますので、図のように入力してください。括弧などの記号がややこしいので、何なら下記の文字列をコピーしてください。
取得した名前 = Excel.CurrentWorkbook(){[Name="UserName"]}[Content]{0}[Column1],
このとき、行末のカンマ(,)を忘れないように注意してください。
let式は、次のようになっています。
最後に「"田中"」部分を「取得した名前」に書き換えます。
これで完成です。セルE1の名前を変更して、クエリを更新すれば、セルE1の名前で絞り込みが行われます。
最後に、入力した
取得した名前 = Excel.CurrentWorkbook(){[Name="UserName"]}[Content]{0}[Column1],
を解説します。
Power Queryでのクエリは、ひとつのステップが1行で書き表されます。イコール記号(=)の右側で行った操作の結果が、イコール記号の左側に書いた変数内に格納され、次以降のステップで、その変数を活用します。今回記述したコードでは、Excelのブックから「UserName」という名前が定義されているセル範囲を取得し、その結果を変数「取得した名前」に入れています。この変数名は自由に決められますが、Power Queryエディタの右端にある[適用したステップ]リストに変数名が表示されることで、どんな処理を行ったかが分かりますので、処理の内容が分かるような変数名が望ましいでしょう。
「Excel.CurrentWorkbook()」はM言語に用意された関数で、Excelのブックから指定した名前が定義されているテーブル(表)を取得します。
セルの値をPower Queryに読み込むには、そのセルまたはセル範囲に「名前」が定義されていなければなりません。Excelの「名前」機能に関しては、下記のページで詳しく解説していますので、ご存じない方はご覧ください。ただし、先にお断りしておきますが、メチャクチャ長文です。たぶんですけど、Office TANAKAサイトで最も長い解説かと。その覚悟で、がんばってお読みください。
今回は「UserName」という名前を定義したセルを取得しています。
この[Content]は、取得したテーブルの値部分、みたいな意味です。ちょっと難しい概念なので、とりあえず"お約束"と考えてもいいでしょう。少しだけ解説すると。
上図のようなテーブルをPower Queryで読み込んだとします。このときのコードが下図です。
ここで、行末の[Content]を削除すると、こうなります。
この[Content]は、「"テーブル1"という名前の値はTable形式(リストや表のこと)である」を表しています。行末で指定した[Content]は、このTable形式を展開するというような意味です。よく分からない方は、聞かなかったことにしてください。
今回「UserName」という名前を定義したセルは、セルE1ひとつだけです。しかし、Power Queryは、たとえひとつのセル(値)であっても、それをデータベース形式として扱います。
たとえば上図は、セルA1に「Data」という名前を定義しました。このセルA1をPower Queryエディタで取得すると、次のようになります。
データベース形式ですから、各列には「列見出し(タイトル)」が必要です。今回は列見出しがありませんから、Power Queryが便宜的に「Column1」という名前をつけました。その[Column1]列にある「先頭行の値」を表しているのが「{0}」です。行の位置は、「1」からではなく「0」からカウントされます。したがって、先頭行は「0行目」つまり{0}です。ちなみに、中括弧{}はインデックス(行の位置)を表します。
まとめ
セルの値によってクエリの内容を変化させる"パラメータクエリ"は、ケースによっては重宝するでしょうし、この仕組みを使わないと実現できないこともあるでしょう。しかし、Power Queryエディタを操作するのとは異なり、少なからずM言語を編集しなければなりません。本コンテンツではパラメータクエリの基本として
- パラメータを受け取る専用のクエリを使う方法
- パラメータをM言語で取得する方法
の2種類をご紹介しました。くれぐれも、よく分からない方は、無理をしてチャレンジなどしないでくださいね。VBAと同じです。意味も分からずコピペして使っていると、第三者に引き継ぐときなどに困ります。使うときは、ご自身がしっかりと理解してください。
なお、取得するファイルの名称やパスを変化させたり、日付で絞り込むなどの例は、別のコンテンツとして書きます。