Excel Power Query ソースをフィルターで絞り込む

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\」は8月のフォルダを指定する設定です。

・設定完了後に [OK] を選択すると、すぐにプレビューに反映されます。

・8月のフォルダーだけに絞り込みされています。

・「適用したステップ」に [フィルターされた行] が追加されています。

・追加された「指定の値を含む」テキストフィルターの 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

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にご期待ください(^^)/

過去記事のサンプルファイルをダウンロードできます

今回記事のサンプルファイルを登録しています
過去の記事で使用したサンプルファイルをダウンロードできるようにページを設置していますので、こちら(このリンク先)からご利用ください