この記事ではExcelのPower Query機能を利用してフォルダ内の複数ファイルを一括で編集加工する方法について紹介します。
毎日掃き出される同じ形式データの集計を楽にしたい…、複数ファイルに分かれている伝票データを一つのファイルにまとめたいと考えたことはありませんか？ ExcelのPower Query機能を利用することで、新しいファイルをフォルダに加えて更新ボタンを押すだけで、自動でデータの集計を行う事が可能です！
Power Query機能はルーチン業務や複数データを一括で簡単に編集・加工するのにもってこいのテクニックですので、ぜひこの記事を最後までご覧ください！
ExcelでのPower Queryの使用方法についてはこちらのQiita記事を参照ください。
Excel で Power Query を使い始めるには
https://qiita.com/PowerBIxyz/items/52e563037b855a099ca9
Power Queryの操作方法はPower BIとほぼ同じのため、簡単な操作方法はこちらの記事も合わせてご覧ください。
まとめ
- データリボンのデータの取得→ファイルから→フォルダからを選択して、読み込みたいフォルダを指定する
- csvファイルの場合はそのまま取り込み可、Excelの場合は取り込みたいシート名を指定してPower Queryで取込
- Power Query側でデータ型を整えて完成
以上の3ステップです！
それでは解説していきます。
ExcelのデータからPower Queryを使用してフォルダ内のファイルを取り込む
まずはデータの取込先の設定を行います。
今回取り込むのは↓のような日別の商品売上データ (Excel)です。
加工編集を行いたいファイルは必ず同じ形式のデータ (集計データ1、2、3)をtestフォルダに格納します！
※Power Queryではフォルダを参照してデータの取込を行うため、保存フォルダを別にする場合は、複数フォルダを指定して編集する必要があります。
Excelのデータリボンのデータの取得→ファイルから→フォルダからを選択します。
フォルダパスを直接または参照する方法がありますが、今回は参照を押して該当するフォルダを選択します。
フォルダを選択してOKを押すとフォルダパスが入力されるのでOKを押して次に進みます。
新しく取り込むファイルの一覧がポップアップするのでデータの変換を選択します。
※この状態では各ファイルが一行のBinaryファイルになっているため、読み込みを選んでしまうとExcel内のデータが展開されません！必ずデータの変換を選択してPower Query側で展開しましょう。
Power Queryエディター画面でフォルダ内のファイルの展開とデータ型の変更
Contentの下↓を選択して、各Excelファイルのデータを展開します。 展開する際には各Excelの同名シートを取得するので取り込みたいシート名はそろえておく必要があります。
今回はどのExcelファイルもSheet1しかないため、Sheet1を指定してOKを押します。
OKを押すとPower Queryが自動的にデータの取得と各列のデータ型を変更してくれます！
取得元のExcel名が入ったSource.Name、販売先、商品名は文字列、日付の列はDate型、数量、単価は整数型に変更されています。
自動変換されたデータ型を変更する場合は列上部の数式バーまたは、列を選択して変更リボンのデータ型から変更します。
数式バーで変更の場合、
文字列は｛“列名”, type text｝
整数型は｛“列名”, Int64.Typet｝
日付型は｛“列名”, type date｝ と記述します。
今回はデータ型に問題が無いのでこのままExcelシートにPower Queryのテーブルを反映します。
変更を反映する場合は“ホームリボンの閉じて読み込む”をクリックします。
正常に読み込まれるとExcelに新しいシートが作成され、ファイルを結合したテーブルが作成されます！
Power Queryで結合ファイルを作成する利点は、基のファイルに変更や追加があった場合、更新を押すだけで変更が反映される点です！
例えば、元ファイルの集計データ１の2021/1/5日の売上数量を変更した場合、クエリリボンの更新を押すと、Power Queryで取り込んだテーブルも変更されます！
さらに便利な点として、新しく集計データ4というファイルが追加された場合も、フォルダ内にファイルを格納して更新を押せば、テーブルに集計データ4のデータが追加されます！！
このように、一度取り込むファイルの設定をPower Query側で行っておくことで、ファイルの追加があった場合も更新を押すだけで簡単にデータの追加が可能です！
Power Queryで作成したテーブルを基にピボットテーブルなどで集計を行うことで、更新を押せば即座に集計が完了します！
マクロよりも簡単かつ迅速にデータの処理が行えるのでぜひ活用してください！
また、Power BI上のPower QueryではExcelよりも大きいデータを扱うことが出来るので、データの容量や集計結果をグループ内で共有する場合などはPower BIを使用することもお勧めします！
Power BIの操作については他の記事で説明していますので、合わせてご覧ください。
【初心者向け】Power BIの使用方法（まとめ）
Power Queryを使用したデータの前処理【Power BI初心者向け】
Power BIに接続可能なデータ/サービスとグラフの作成方法【初心者向け】
Power BIの特徴とインストール方法について
日々の何気ないルーチン業務の手間を削減してどんどん効率化を図りましょう！
コメントを残す