パワークエリ１年生の苦悩シリーズ。
エクセル関数・VBA使いが、パワークエリをやってみて、文化の違いに苦戦しつつ七転八倒の末なんとか中の下レベルまではマスター。（したと思っている）
エクセルのシートの値をパワークエリで利用できると何かと便利。パワークエリ内で変化させたい値をエクセルシートに入力しておいて、フィルタの条件として利用したり、読み込みするファイルパスをシートに入力されている文字列を利用したり。パラメーターっちゅうヤツですかね。
あたふた苦労しながら、なんかやっとわかってきたので、それをここに記します。
１．エクセルシートの値を利用する方法
ワークシートの値をパワークエリ内に取り込んで利用するには大きく分けて以下の二つの方法があります（もっとあるかもだけど）
・テーブルとして読み込んでから利用する方法
・セルの名前を利用して読み込む方法
この二つについてやってみたいとおもいます。
２．まず確認しておくこと
ほかのクエリの値を利用する際は、プライバシーレベルを下げておかないとエラーになってしまいます。エラーが出たら、クエリのオプションから「常にプライバシーレベルを無視する」設定とします。
ただし、プライバシーレベルを下げることになるので、各人のご判断と責任のもと、設定可否を判断してください。
３．テーブルとして読み込んでから利用する方法
テーブルとして読み込んでから利用する方法について、以下の3つの方法をやってみました。
・ドリルダウン
・テーブルの行列で指定して取り出し
・リストを利用する
（１）ドリルダウン
①やり方
ファイルパスを入力してある範囲をヘッダーを含めてテーブル化しています。
テーブル名は「ファイルパス指定」としています。
このテーブルを選択した状態で、リボン データ＞テーブルまたは範囲から をクリックして、パワークエリに読み込みます。クエリ名はテーブル名の「ファイルパス指定」となっています。ここでクエリ名を変更することも可能です。今回はそのまま利用します。
テーブル内のセルの部分で 右クリック＞ドリルダウン をクリックします。
すると、テーブルではなく一つの値だけが表示された状態になり、クエリのアイコンもテーブルではなく文字列型のアイコンになっています。
さて、これでワークシートの値がパワークエリ内で使えるようになりました。クエリ名「ファイルパス指定」の中身は 「C:\Users\実績\202408.xlsx」 ということになります。
VBAでいうところの変数「ファイルパス指定」に値 C:\Users\実績\202408.xlsx を入れた感じです。
これをどのように使うかという例。
このフォルダから202407というファイル名のエクセルをパワークエリに読み込んでいたとします。
そのクエリの「詳細エディター」を開き、ファイルパスの部分をドリルダウンしたクエリ名「ファイルパス指定」に書き換えます。
そうすると、C:\Users\実績\202408.xlsx を読み込みするクエリに変わります。
202409のファイルを読み込みしたいときは、エクセルシートの値を 、C:\Users\実績\202409.xlsx に書き換えて、クエリの更新をクリックするとパワークエリエディタを開くことなく読み込みファイルを入れ替えすることができます。
②そもそもドリルダウンってなに？
ドリルダウンってなに？というと。。。
ドリルダウンとは、データの集計レベルを１つずつ掘り下げて集計項目をさらに詳細にする操作のこと。またはその機能。
例えば、国別に見ていたデータを都道府県別に集計し直すなどの操作がこれに当たる。
ドリルでガガガガと掘って下に行くイメージでしょうか。ここではテーブルの複数の値の中の一つの値に掘り下げる、という感じでしょうか。今回は値が一つだからイメージしにくいけど。
③ドリルダウンを数式バーで行う（行、列で指定する）
上記のドリルダウンした数式バーを見てみると、
=変更された型{0}[ファイルパス]
となっています。
つまり
=テーブル名{行番号}[列名]
で、ドリルダウン（＝テーブルの値の取り出し）ができる、ということですね。
ちなみに
=テーブル名[列名]{行番号}
でも同じ結果となります。
行番号は 0 始まりです。
それを踏まえて、右クリック＞ドリルダウン ではなく、行番号列名を指定して値を取り出す方法をやってみましょう。
下図のセル範囲のテーブルをパワークエリに読み込みします（データ＞テーブルまたは範囲から）
ここから2行目2列目の「社員名簿.xlsx」を取り出してみます。
数式バーの「fx」(ステップの追加)ボタンをクリックします。
=前のステップ名 と表示されます。（今回は =変更された型 ）
ステップ名に続けて取り出したい{行番号}[列名]を入力します。
2行目ですが、0始まりなので行番号は{1}
2列目の列名[ファイル名]と指定します。
=変更された型{1}[ファイル名]
すると、「社員名簿.xlsx」がドリルダウンされた状態となります。
（２）テーブルから行列で指定して値を取り出す
これで取り出しする方法はわかりました。ただ、複数の値を読み込みしてもドリルダウンすると一つになってしまいます。
複数の値を取り出すには
・複数回テーブルを読み込みする
・１回テーブルを読み込みしてテーブルの参照で複数の値を利用する
・使用したいクエリ内でテーブル名を指定して値を利用する
等が考えられます。
具体的にやってみましょう。
※上記で行ったドリルダウンは削除して、テーブルを読み込んだ直後の状態で行います。
①複数回テーブルを読み込みする
いちいち読み込みするのは面倒なので、テーブルの複製を利用します。
まずは、1行目の値の取り出し。フォルダパスとファイル名をいっぺんに取り出ししたいと思います。
数式バーのステップの追加ボタン(fx)をクリック
直前のステップ名が表示されるので、ここに数式を入力していきます。
= 変更された型{0}[フォルダパス] & “\” & 変更された型{0}[ファイル名]
チェックボタンクリック（またはエンターキー）で確定します。
するとフォルダパスとファイル名を \ でつないだ値の取り出しができました。
次に二つ目のファイルパスの取り出しを行います。
テーブル1を選択し、右クリック＞複製
複製された「テーブル1」を選択した状態で、数式バーの {0} を {1} に変更します。
チェックボタン（またはエンターキー）で確定。
取り出しされた値が2行目のものに変わりました。
このままでは使いにくいので、クエリ名を変更します。
テーブル1→ファイルパス1
テーブル1(2)→ファイルパス2
と変更しました。
他のクエリ内で使うときは下図のようにクエリ名で使用します。
②使用したいクエリ内でテーブル名を指定して値を利用する
前項①でやったことは取り消しして、テーブル1を読み込んだ直後の状態からやります。
ドリルダウンせず、ほかのクエリ内でテーブルの行番号列名を指定して値を持ってきます。
Sheet1の詳細エディタを開いて、下図の赤枠内を以下のように書き換えます。
わかりやすさは①、手間の少なさは②、という感じでしょうか。
テーブルを1回読み込みして参照を利用して複数の値を取り出しというのは今回はやりませんでしたが、こんな感じです。
（３）リストに変換して指定
テーブルを読み込み→リストに変換してリストの値をほかのクエリ内で利用する。というのをやってみようと思います。
①やり方
下図のテーブルを読み込みします。
読み込んだテーブルを選択し、リボン「変換」＞「リストに変換」をクリックします。
すると、ちょっとしゅっとした感じに変わります。クエリのアイコンもテーブルではなく細い感じのものになります。
リスト化はテーブルから1列を取り出しするドリルダウンであると思っています。
（今回は一列だけなので変わりないですが）
前項のドリルダウンはテーブルから行列を指定し1つのセルを取り出しました。
リスト化はテーブルから指定した1列を取り出しするドリルダウン。
ここから一つの値をとりだしするには、行番号を指定します。行番号は0始まりです。
ためしに =変更された型[条件] の後に {1} と指定すると2行目の値が取り出しされます。
※{1}は試した後は削除してください。
リストの中のほかのクエリで利用するには
リスト名{行番号} とします。
②使用例１ リストから値を取り出して利用
これを利用して、ほかのパワークエリのフィルター条件に利用してみたいと思います。
まず、下図の都道府県別面積のテーブルを読み込みます。
面積が8000以上のデータにフィルタします。
ここをリストの値を利用する書き方に変更します。
8000の部分を 条件{7} に書き換えます。
すると、同じ状態が保たれます。リストの中の8番目の値（インデックスで7）が指定されたことがわかります。
8000以上9000以下のフィルタ条件にしてみたいと思います。
数式バーの内容を以下のように書き換えます。
= Table.SelectRows(変更された型, each [面積] >= 条件{7} and [面積] <= 条件{8})
8000以上9000以下の面積のデータに絞り込みされました。
②使用例2 リストとして利用
ちょっと違ったやり方もやってみます。
下図のテーブルを新たに読み込みし、これもリスト化します。
リスト名は「条件2」です。
都道府県面積一覧クエリに切り替えし（①でやったフィルタは削除します）
都道府県名で適当にフィルタをかけます。
数式バーのeachの後ろを以下のように書き換えます。
List.Contains(条件2,[都道府県])
すると、ワークシートで指定した都道府県に絞り込みされました。
いったん、「閉じて読み込む」をクリックし、パワークエリエディタを終了します。
ワークシートの都道府県の条件の入力内容を変更し、クエリの「更新」をクリックすると、絞り込み結果が変わることがわかります。
List.Contains は、『リストに値が含まれるか』を判定する関数です。
List.Contains( リスト名 , 値 ) と指定しています。
一致する値がリスト内にあると TRUE が返ります。
each と組み合わせて List.Contains( リスト名 , [列名] ) とすることで、1行1行、指定した列の値があるかどうかというのを判定していきます。
Table.SelectRows はTrueの行をセレクトするので、結果、List.Containsの戻り値がTrueだった行のみ絞り込みされます。
ちなみに List.Contains は「一致」じゃないと、TRUEになりません。「含む」の検索条件にしたいときは、こちらを参照してみてください。
【PowerQuery】○○を含む行を抽出、含まない行を抽出を複数の条件でやりたい
４．セルの名前を利用して読み込む方法
田中先生のオフィスTANAKAの記事で主に勉強させていただきました。
セルの値でクエリを変化させる(パラメータクエリの基本)
セルの名前を定義しておくと、パワークエリで使えるんですね。
（１）名前を定義したセルのパワークエリ内に取り込む方法
まず、セルに名前を定義します。
名前を付けたいセルを選択した状態で、エクセルの左上の名前ボックスに定義したい名前を入力します。セルB2に「ファイルパス」という名前を定義しました。
パワークエリエディタを立ち上げます。
空のクエリを使って、単体で値を取得してみましょう。
ホーム＞データの取得＞その他のデータベース＞空のクエリをクリックします。
数式バーに以下の数式を入力します。
= Excel.CurrentWorkbook(){[Name="ファイルパス"]}[Content]{0}[Column1]
数式を要約すると
エクセルの、現在のワークブックを取得し、名前が「ファイルパス」の部分の1行目(0始まりなので{0})、1列目(列名はないので[Column1]) の値を持ってくる
という感じでしょうか。
わかりやすいように、クエリ名を「クエリ1」から「パス」に変更します。
テーブルの時と同様に、ほかのクエリ内ではこのクエリ名「パス」を指定すると、中身のデータの利用ができます。
（２）使用例 複数の値の読み込み
もうひとつ例をやってみます。
今度は直接ほかのパワークエリ内に取り込んで利用します。
セルB2に「フォルダパス」
セルB3に「ファイル名」
と名前を定義します。
セルB3には関数で前月をyyyymmのテキスト形式で表示するようにしています。
パワークエリを起動します。
全部書くのが面倒なので、目的のパスからファイルを取り込むクエリを作成し、書き換える形を取ります。
フォルダパスとファイル名をそれぞれ読み込みし、読み込みしたステップ名を利用してクエリ内で使用します。
セル範囲で名前を付けて行列を指定して取り出す方法でも同じ結果が得られます。
{0}、{1}などと、行番号を変化させることでセル範囲内の指定した値を取り出すことができます。
今回は1列なので変化のさせようがありませんが、列方向でセル範囲内の指定した値を取り出す場合は[Column1]を[Column2]などと変化させます。
（３）使用例 フィルタでの利用
もう一つ、別の例をやってみます。
今度はフィルターで使ってみたいと思います。
テーブルから、ワークシートで指定した日付を抽出して表示するクエリ、というのを作成してみます。
パワークエリにフィルタをかけたいテーブルを読み込んだ状態から始めます。
読み込みしたテーブル1を適当な日付でフィルタしておきます。
詳細エディターを開いて、コードを修正していきます。
以下のコードを追加します。「フィルターされた行」ステップの前であればどこでも大丈夫です。
おしりのカンマを忘れずに！
= Excel.CurrentWorkbook(){[Name="今日"]}[Content]{0}[Column1],
すると。。。あれ？結果が何にもなくなってしまいました。
名前を定義したセル値を読み込んだステップをクリックして選択すると、取得した結果が見ることができます。
日付と時分秒まで表示されているので、[日付時刻値]となっていることがわかりました。
テーブルの日付は[日付型]なので、[日付型]に揃えたいと思います。
再度詳細エディタを開いて、修正を加えます。
Date.From関数で日付時刻値から日付値を取り出しします。
すると、読み込みした日付値でフィルタすることができました。
Date.From関数を使用する箇所は、セル値の読み込みのところでなく、フィルタをかける部分でやっても大丈夫です。
エクセルの名前の定義、というのがパワークエリをやっているとちょいちょい気になるところです。田中先生のサイトでの解説で、理解が深まりました。
セルの名前について詳しく
５．最後に
エクセルのシートの値をパワークエリで利用できると何かと便利と私は思います。
作業月に応じて対応するファイル名が変化する場合などに、ワークシート関数でファイル名を変化させ、読み込みするファイルの指定を自動で変えることができるため便利です。実務ではよく使っています。
長くなってしまいましたが、お読みいただきありがとうございました。
６.参考文献、記事
Power Queryでソースのフォルダを臨機応変に変更したい！
内部リンク
【PowerQuery】○○を含む行を抽出、含まない行を抽出を複数の条件でやりたい
【PowerQuery】関数だと簡単にできるのにパワークエリではどうやるんだ?を集めた①文字列編
【PowerQuery】関数だと簡単にできるのにパワークエリではどうやるんだ?を集めた②日付編
【PowerQuery】ふんわりと理解するM言語
【PowerQuery】パワークエリ1年生～これは便利！と思ったもの
パワークエリはこちらの書籍で勉強しました。お勧めです！
コメント