Excel Power Query index列で最新データ取得

Power Query 第23回目です。
日別の販売データを共有フォルダに投入しておき、Power Query で更新しているような場合、最新日のデータだけを取り込みたいことがあります。そんな場合どのようにしたらよいか検討します。

くるみこ
くるみこ

共有フォルダに投入された最新のファイルだけを抽出する方法を検討してみましょう。
最新ファイルの判別をどのようにするのかが重要です。

ファイルのタイムスタンプで確認するんじゃないんですか?
よろしくお願いしますm(__)m

【この記事でわかることは】
・フォルダー内の最新データファイルだけ取り込む方法がわかります
・インデックス列の使い方がわかります

前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ

・前回内容はフィルターの絞り込みにパラメーターを使う方法ついて解説しています。

記事作成にあたっては次の Microsoft office のサポートの情報などを参照しています。
Power Query for Excel のヘルプ
Excel の Power Query について
インデックス列を追加する

なお、この記事の画像は Microsoft 365 で取得した画像です。Excel2016 のリボンやメニューなどバージョンによって違うようなので、適宜読み替えをお願いします(^^)/

フォルダー内最新ファイルの判別

最新データのソースファイルであるかどうかを判別するにはどうしたらよいでしょうか。

フォルダー内ソースファイルの列で判別できそうなものを抜粋してみました。

列名日本語内容データ型
Name名前ファイル名(拡張子含む)テキスト
Date accessedアクセス日ファイルにアクセスした年月日時刻日付/時刻
Date modified更新日ファイルを更新した年月日時刻日付/時刻
Date created作成日ファイルを作成した年月日時刻日付/時刻

ファイル名で判別できる可能性は?

・「Name」列でソートして最新を判別できる可能性もあります。

・ファイル名が、サーバーからデータをダウンロードした際に日時が付加されている場合です。
 例えば「ダウンリード者のID+年月日時刻」=「ID00123456780_2021081010301503」
 のようになっていれば可能ですね。

・でも、待ってください! 担当者が変わる場合IDも変わりますね。

タイムスタンプで確認する?

・ファイルのタイムスタンプは「作成日時」「更新日時」「アクセス日時」の3種類があります。

・ファイルのタイムスタンプ変更についてのVBA記事を書いていますのでよかったら見てください。

「作成日時」は上の記事のようなマクロなどで強制的に書き換えない限り変更されません。

・一方、「更新日時」「アクセス日時」変わる可能性があります特に「アクセス日時」については、ファイルが読み取り専用属性であっても、一度開いてしまえばその時点の「日時」に書き換えられてしまいますので注意が必要です。

・特に「更新日時」は注意が必要です。ファイル内容は変更せず、開いただけなのに上書き保存された場合、「日時」が更新されてしまいます。

・状況に応じて使い分ける必要がありそうですね。

フォルダー内の最新ファイルを取得します

最新ファイルだけ取得するための手順を確認します。

最新ファイルだけ取得するための手順

  1. 最新判定をどの列のデータでおこなうのか決めます
  2. 列を最新のデータが最初の行になるようにソートします
  3. インデックス列を追加します
  4. 最初のインデックス番号だけを指定してフィルターをかけます
  5. その結果、抽出されたデータ1行だけが表示されます

最新判定をどの列のデータでおこなうのか決めます

・前段の「フォルダー内最新ファイルの判別」で検討していますが、ここでは「作成日時」列を使うこととします。「作成日時」は強制的に書き換えない限り変更されないからです。

画像はクリックで拡大表示できます!

「作成日時」列でソートします

・「作成日時」列を選択します。

・最新日を上にするのだから「降順」でソートを実行し、データを確認します。

・[並べ替えられた行] のステップが作成されています。

画像はクリックで拡大表示できます!

インデックス列を追加します

・インデックス列は、次の手順で追加します。

  1. リボン「列の追加」タブ →「インデックス列」の右端にある▼ボタンを押します
  2. 開始番号を指定します「0から」「1から」「カスタム」のいずれかを押します
    【注意】▼ボタンを押すのではなく直接「インデックス列」を押すと「0」開始になります

※「カスタム」の場合、ダイアログで開始数値と増分を入力指定できます。

画像はクリックで拡大表示できます!

・「0」開始選択後「1」開始に選び直しているGIF画像です。

・ステップ [追加されたインデックス] が作成されました。

「インデックス」列でフィルターをかけます

画像はクリックで拡大表示できます!

・最小値のインデックス「1」でフィルターすると、最新データの1行だけが抽出されました。

・ステップには [フィルターされた行] が追加されました。

詳細エディターで作成されたMコードを確認してみます
画像はクリックで拡大表示できます!

これで、最新ファイルだけに絞り込めたのでこのファイルを展開していけばOKですね。

ファイルの展開はヘルパークエリ無しにしたい

絞り込んだ最新ファイルを展開してクエリを作成するには2つ方法があります。

ヘルパークエリで展開する方法

・[Content] 列の 赤丸の「File の結合」アイコンをクリックするだけです。

・GIF画像を見てわかるとおり、ヘルパークエリを使って作成されました。

・一緒にたくさんの「ヘルパークエリ」と「ステップ」が作成されたのも確認できますね。

ヘルパークエリ無しで展開する方法

・「ヘルパークエリ」と「ステップ」 が自動作成されるのが嫌いな方はこちらの方法です。

・この方法については、下のブログカードの記事で紹介しました。

ヘルパークエリ無しで展開する方法の部分へのリンクはこちらをクリックしてください。

・この方法ならば、「ヘルパークエリ」が作成されることはありませんのでお試しください。

スポンサーリンク

まとめ(おわりに)

以上、インデックス列を利用してフォルダー内のファイル群から最新のファイルを抽出する方法について解説しました。

今回も、サンプルファイルにフォルダー設定と見本のCSVファイルをまとめて登録しておきますので、いろいろな設定で試してみてください。

追加作成された [ステップ] を削除すれば、簡単に元に戻せますのでぜひ練習してみてくださいね。

まとめと感想など

くるみこ
くるみこ

フォルダーからのデータ取得は非常に便利ですから、ファイルを絞り込むこんな方法を知っておけば、いろいろ活用できそうですね。いかがでしたか?

共有フォルダーなどでは、ファイルの管理方法がしっかりしていないと支障が出ることもよくわかりました。ヘルパークエリ無しの方法も、この機会にもう一度復習しておこうと思いました。サンプルを使っていろいろやってみま~す(^^♪

【今回わかったことは】
・フォルダー内の最新データファイルだけ取り込む方法がわかりました
・インデックス列の使い方がわかりました

★★★ ランキング参加中! クリックしてね(^^)/ ★★★

【今後の記事について】

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてる」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

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

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

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