Power Query 第21回目です。
フォルダーからソースデータを取得する際に、読み込むファイルを絞り込む方法を検討します。
フォルダーからソースファイルを読み込む際、一番最初のステップ「ソース」です。この部分でフィルターをかければ、読み込むファイルの絞り込みができそうですので検討してみましょう。
時々あるんですよね～。CSV に Excelファイルが混ざっている場合とか？
わかりました！ よろしくお願いしますm(__)m
【この記事でわかることは】
・フォルダーから読み込む [ソース] をフィルターで絞り込む方法
前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ
・前回の内容は 基本的なフォルダーの設定や管理方法について解説しています。
記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
・Power Query for Excel のヘルプ
・Excel の Power Query について
・データのフィルター処理 (Power Query)
なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替えをお願いします(^^)/
[ソース] フォルダー内のファイルの一覧
はじめに [ソース] フォルダー内のファイルの一覧を詳しく確認してみましょう。
[ソース] フォルダーの設定は、次のパラメーターテーブルからカスタム関数で設定しています。
[ソース] のプレビュー画像がこちらです。
ファイル一覧の列構成を確認します
前回使用したExcelファイルをそのまま継続して使っています。
プレビューで列名を確認してみます
フォルダーパス内のファイル一覧がプレビューされています。その列名は次のとおりです。
| 列名 | 日本語 | 内容 | データ型 |
|---|---|---|---|
| Content | 中身 | ファイルそのもの（Binary） | バイナリ |
| Name | 名前 | ファイル名（拡張子含む） | テキスト |
| Extension | 拡張 | ファイル拡張子 | テキスト |
| Date accessed | アクセス日 | ファイルにアクセスした最新年月日時刻 | 日付/時刻 |
| Date modified | 更新日 | ファイルを更新した 最新年月日時刻 | 日付/時刻 |
| Date created | 作成日 | ファイルを作成した年月日時刻 | 日付/時刻 |
| Attributes | 属性 | ファイルの属性（Record） | レコード |
| Folder Path | フォルダーパス | フォルダーのフルパス | テキスト |
フィルターで絞り込む列を検討します
[Content] 列
・各ファイルのバイナリデータが入っているこの列は絞り込みできせん。
・各ファイルの結合のみ可能となっています。
[Name] 列
・ファイル名やファイル名の特徴で絞り込むことが可能です。
[Extension] 列
・ファイルの拡張子で絞り込むことが可能です。
・フィルタで絞り込んでおけば、違う種別のファイルが混入していても除外できます。
[Date accessed][Date modified][Date created] 列
・[アクセス][更新][作成]それぞれで絞り込みは可能です。
・でも、[Date modified（更新）] 列で絞り込むことが多いと思います。
・指定した日時以降や期間を絞り込むことも可能です。
[Attributes] 列
・各ファイルのテーブルレコードが入っています。属性の展開のみ可能です。
・「属性」を展開後にフィルターで絞り込むことが可能です。
・例えば、隠しファイルを除外したい場合は Hidden（隠し属性）で展開します。
そして、展開後に False で絞り込めば True が除外されます。
【2021/8/17追記】————————————————————-
・フォルダーから取得のヘルパークエリの中に、フォルダ内のファイルから隠しファイルに関する行をフィルターをかけて除外している Mコード があることを確認しました。
フィルター選択された非表示のファイル1 = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
こういう書き方ができるんですね。[Attributes]?[Hidden]? の「?」は、これがついている場合エラーで null が返るということです。
【2021/8/17追記はここまで】————————————————-
[Folder Path] 列
・特定のフォルダーだけに絞り込むなどが可能です。
絞り込み可否
先ほどの列名一覧で「絞り込み」の可否等を自分なりの主観で整理しました。
| 絞込可否 | 列名 | 絞り込み例 | データ型 |
|---|---|---|---|
| × | Content | 絞り込み不可 | バイナリ |
| 〇 | Name | ファイル名やファイル名の特徴で絞り込み可能 | テキスト |
| 〇 | Extension | 違う種別のファイル混入を除外できます | テキスト |
| △ | Date accessed | 絞り込み可能ですが利用頻度は少ない | 日付/時刻 |
| 〇 | Date modified | 日付等の絞り込みはほとんどが更新を使います | 日付/時刻 |
| △ | Date created | 絞り込み可能ですが利用頻度は少ない | 日付/時刻 |
| △ | Attributes | 隠しファイルの除外などで利用されます | レコード |
| 〇 | Folder Path | フィルダー名での絞り込みが可能です | テキスト |
フィルターで絞り込み
では、実際に[ソース]のプレビューに手動でフィルターをかけてみます。
[Folder Path] をフィルターで絞り込む
・最初のプレビュー画像でも確認できるとおり「2021」フォルダー以下のサブフォルダーの全てが表示されています。
・赤丸で囲んだ部分をクリックし、フィルター設定を行います。
・フィルター設定ダイアログが表示されます。
・[検索] も使用できますが細かく指定する場合は
・[テキスト フィルター] をクリックします。
・すると項目が表示されるので、ここでは
[指定の値を含む…] を選択します。
・「ステップの挿入」ダイアログで注意メッセージが表示されます。
・そのまま[挿入] を選択します。
・デフォルトでは下の「基本」ダイアログが表示されます。
・このままでも設定できますが「詳細設定」に切り替えます。
・「値」にフィルターで絞り込むテキスト「\08\」のように入力しました。
・「\08\」は８月のフォルダを指定する設定です。
・設定完了後に [OK] を選択すると、すぐにプレビューに反映されます。
・８月のフォルダーだけに絞り込みされています。
・「適用したステップ」に [フィルターされた行] が追加されています。
・追加された「指定の値を含む」テキストフィルターの Mコード がこちらです。
フィルターされた行 = Table.SelectRows(ソース, each Text.Contains([Folder Path], “\08\”)),
[Extension] をフィルターで絞り込む
・手順は先ほどと同じです。
・[Extension] 列でフィルターを設定ます。
・「基本」→「指定の値に等しい」→ 「値」に「.csv」と入力 → [OK] を選択します。
・Mコードは「指定の値に等しい」で設定した場合のコードになっています。
フィルターされた行1 = Table.SelectRows(フィルターされた行, each [Extension] = “.csv”),
・こうしておけば、「.csv」以外のファイルがフォルダー内に混入していても大丈夫です。
その他のフィルター処理
・フィルター処理については、そのほか「数値」「日付」など各種の設定があります。
・実際に一度設定してみて確認してみることをお勧めします。
・追加された [ステップ] を削除してしまえば、すぐに元に戻すことができます。
・詳しい情報は、MSのヘルプデータのフィルター処理 (Power Query) を参照してください。
まとめ（おわりに）
以上、[ソース]をフィルターで絞り込む方法について、簡単な例を紹介しました。
サンプルファイルにフォルダー設定と見本のCSVファイルをまとめて登録しておきますので、そのほかの設定をいろいろ試してみてください。
追加作成される [ステップ] を削除してしまえば元に戻せますので、ぜひ練習してみてください。
まとめと感想など
[ソース]をフィルターで絞り込む方法について勉強しました。いかがでしたか？
今回は手動で設定しました。次は今回手動で設定して作成された Mコード の部分をパラメーターに置き換える勉強をしましょう(^^)/
すごくわかりやすかったです。サンプルを使ってもっといろいろ試してみたいと思います。
次回が楽しみです(^^♪
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
【今後の記事について】
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/
過去記事のサンプルファイルをダウンロードできます
今回記事のサンプルファイルを登録しています！
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら（このリンク先）からご利用ください
【今回わかったことは】
・[ソース] をフィルターを使って絞り込む方法がわかりました