見出し画像

Power Queryで月別フォルダに保存されたCSVを統合してみたハナシ

まる🍑

CSVファイルの統合。WinActorか、Power Queryか…

ただのCSVファイルの統合ならばPower Queryが断然いいのはわかっていたけど、今回はそこにちょっと ひと手間加えたい。

そもそもそれが実現可能なのか？ノンプロ研の有識者に質問。

できるとの回答をいただけた！！😍 しかも、

keitaroさんが行っている「百人組手」で教えていただけることに😭
(リンク開くと怪しいマスクマンが出て来ますが、決して怪しい人ではありませぬｗ)
優しい世界…😭✨
「百人組手」は、以下「組手」と呼びますが、ついて来てくださいｗ🤣

では！自分の覚えのために、記録しておきますよ～✊

① 事前準備

CSVファイルを、ひとつのフォルダの中に 年月別で保存しておきます。

② やりたいこと

このCSVファイルをひとつのExcelにファイルに統合したい訳ですが、ただ転記したいだけではなく 条件があります。

  1. CSVファイルの更新日時順の昇順で統合したい

  2. ファイル名の一部(テストデータでいう「10001」の部分)を統合したExcelファイルに新たな列として追加したい

  3. どの年月フォルダのレコードなのか、わかるようにしたい

1.2.は、私がPower Queryで実現可能なのか わからなかったこと。
3.は、やりたいことの実現のために組手の中で提案いただいたこと。
です。
有識者のアドバイス、大変ありがたい…😭✨

③ 中身が空っぽのExcelファイルを用意する

CSVファイルを統合するExcelファイルを用意します。

このExcelファイルに、Power Queryを仕込んでゆきます。

④ フォルダごと読み込む

あ、ちなみに私のExcelは2016です…
Power Queryは、Excelのバージョンによって かなり表示される項目が違うようです。
そして2016でPower Queryやるのは微妙な感じでしたが、今回の内容は問題なく作業できました✨

データメニュー→新しいクエリ→ファイルから→フォルダーから を選択。
(ここの表示が、すでに他のバージョンと違いますがご勘弁を🙇‍♀️)

転記元データフォルダを選択して「開く」。

こんな感じで、年月フォルダ内のCSVファイルが ごそっと読み込まれます。

データはこちらから生成したダミーデータです

右下の「結合」から「データの結合と変換」。

「Fileの結合」ウィンドウは、そのまま「OK」。

⑤ 年月フォルダのパスと、CSVファイルの更新日時を表示する

こんな感じでデータが読み込まれる訳ですが、このままだと年月と更新日時がわかりません。

右側の「適用したステップ」を変更して、Power Queryが気を利かせて削除してくれた列を変更します。

「Data modified」と「Folder Path」にチェックを入れて「OK」

おぉ👀 出た！！

Power Queryはソートに時間がかかるので、CSVファイルの中身を展開する前に並べ替えを行っておいた方がいいそうです。

年月フォルダ内で更新日時順に並べ替えたい。
先にFolder Pathで昇順に並べ替え。

お、なんか言われた。

やらねばならぬので「挿入」。
続いて Data modified でも昇順に並べ替え。

またステップの挿入の確認が出るので「挿入」。
そうそう、この順番！

⑥ ファイル名の一部を取り出す

Source.Nameを利用して、数字の「10001」の部分のみ取り出します。

Source.Nameを選択して、変換タブ→抽出→区切り記号の間のテキスト。

またステップの挿入の確認が出るので「挿入」。

区切り記号の間のテキストを、こんな感じに設定して「OK」。

すごいっ！！👏✨

同じようにFolder Pathも年月のみの表示に変更します。

いいね いいね！！👏✨

⑦ データの確認

「適用したステップ」の「変更された型」に戻ります。

データ型は、Power Queryが よしなに判断してくれています。
もし変更したかったら、列名の横をクリックして変更。

列名は、ダブルクリックで変更できます。

ダミーデータなので、列名とデータが合ってなさ過ぎですがお許しを🤣

列の順番は、ドラッグ＆ドロップで変更できます。

⑧ 読み込み

最後に、ホームメニュー→閉じて読み込む！！

(*ﾟДﾟ)*ﾟДﾟ)(*ﾟДﾟ)ｫｫｫｵｵ
(2016では、なにもせずともテーブルになりました)

年月別で表示したいときは、フィルタすればOK🙆‍♀️

⑨ データの更新

転記元データに新しい年月フォルダが出来たときは…

「最新の情報に更新」を押せばOK✨

じゃんっ✨

⑩ 所感

できた！早い！！🙌✨
「区切り記号の間のテキスト」が便利すぎて最高！！

いいっすね～、Power Query。
これ以上のことは、今の知識でやるとエラー起きたときに対処ができないので、とりあえず ここまでで十分かな🎵

卒業LTでaliceさんが発表していた内容、うなずける。
まさに拍手喝采！👏✨

Power Queryのいいところ。

自分でやるなら、断然Power Queryを使いますが…
業務ユーザーにやってもらうとなると、Power Queryの画面自体に抵抗感ありそうかな～と感じました。

でもこの便利さを知ってもらったら、Power Query使ってみる！ってなるかな？

改めて ノンプロ研のみなさま、ありがとうございました！！
百人組手 最高✨ 押忍！！

いいなと思ったら応援しよう！

まる🍑
事務員が少しの背伸びでできる効率化を目指す🌈✨ 自分の好きなものを、感じたままに楽しく発信していきます🤤