Power Queryでフォルダから複数のファイルを効率的に取り込む方法：ステップバイステップガイド

この記事では、Power Queryを使用して、フォルダ内の複数のExcelファイルを効率的に取り込む方法について解説します。特に、ファイルの見出しや不要な行が混在しているような、少し複雑な状況に対応するための具体的な手順と、実用的なテクニックを紹介します。

パワークエリでフォルダから複数のファイルを取り込みたいです。元となる台帳ファイルは２か月ごとに区切られていて、様式は全く同じです。①台帳、②台帳、③台帳、④台帳・・・・と続いています。

台帳ファイルは単体で印刷して使うこともあるため、最初の数行にデータとは違う見出しが入っています。

イメージは以下です。

1 ○○台帳

2 ＠月＠日～＠月＠日分

3 担当：＠＠

4 （空白行）

5 （ここでデータの見出し）

6 （以降、実際のデータが入力されている）

これをクエリで結合・取り込む際に、サンプルとして①台帳の冒頭４行を削除、1行目をヘッダーとして使用まではわかるのですが、そうすると②台帳、③台帳・・・の1～4行目が一緒に取り込まれてしまいます。①台帳でも４行削除、②台帳でも４行削除・・・・という風にしたいのですが、一個一個フィルターかけて消すのが普通のやり方なのでしょうか？それとも一括でできたりしますか？

例えば３行目担当などは月によって変化するため、台帳が更新される都度、フィルターをやり直さなければいけなくなります。

よろしくお願いいたします。

この質問は、Power Queryを使用して複数のExcelファイルを効率的に処理したいというニーズを具体的に示しています。特に、各ファイルに異なる見出しや不要な行が含まれている場合に、それらを一括で処理する方法について焦点を当てています。この記事では、このような課題を解決するための具体的なステップと、Power Queryの高度な機能を利用した効率的なデータ処理方法を解説します。

1. Power Queryの基本：フォルダからのデータ取得

まず、Power Queryを使用してフォルダから複数のExcelファイルを取り込む基本的な手順を説明します。これは、データ分析やレポート作成の最初のステップとなります。

1. Power Queryの起動: Excelを開き、「データ」タブから「データの取得と変換」グループにある「データの取得」をクリックし、「ファイルから」→「フォルダーから」を選択します。
2. フォルダーの選択: ファイルが格納されているフォルダーを選択し、「OK」をクリックします。
3. データのプレビュー: フォルダ内のファイルの一覧が表示されます。ここで「データの変換」をクリックして、Power Queryエディターを開きます。

2. 不要な行の削除とヘッダーの設定

次に、各ファイルに共通して存在する不要な行を削除し、正しいヘッダーを設定する手順を説明します。これは、データのクレンジングと整形において非常に重要なステップです。

1. サンプルファイルの選択: 最初のファイル（例：①台帳）を開き、不要な行を削除します。具体的には、最初の4行を削除し、5行目をヘッダーとして設定します。
2. クエリの適用: 削除とヘッダー設定の操作をクエリに適用します。Power Queryエディターの「ホーム」タブにある「最初の行をヘッダーとして使用」をクリックします。
3. カスタム関数の作成: この操作をすべてのファイルに適用するために、カスタム関数を作成します。Power Queryエディターの「表示」タブから「詳細エディター」を開き、M言語のコードを編集します。

この段階で、特定のファイルに対して行った一連の操作（例：最初の4行の削除、ヘッダーの設定）を、他のすべてのファイルに自動的に適用できるようなカスタム関数を作成します。これにより、各ファイルを手動で編集する手間を省き、効率的にデータ処理を行うことができます。

3. カスタム関数の詳細とM言語の活用

カスタム関数を作成する際には、M言語の知識が役立ちます。M言語を使用することで、より柔軟で高度なデータ処理が可能になります。

以下に、カスタム関数を作成する際の基本的なM言語のコード例を示します。

let
    Source = (Parameter) => let
        Source = Excel.Workbook(Parameter, null, true),
        Sheet1 = Source{[Item="Sheet1",Kind="Sheet"]}[Data], // シート名を「Sheet1」に修正
        #"Removed Top Rows" = Table.Skip(Sheet1, 4),
        #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}}),
        #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "日付"}, {"Column2", "顧客名"}, {"Column3", "商品名"}, {"Column4", "数量"}, {"Column5", "単価"}, {"Column6", "金額"}, {"Column7", "備考"}, {"Column8", "担当者"}, {"Column9", "部門"}, {"Column10", "ステータス"}, {"Column11", "納期"}, {"Column12", "注文番号"}, {"Column13", "住所"}, {"Column14", "電話番号"}, {"Column15", "メールアドレス"}, {"Column16", "商品コード"}, {"Column17", "カテゴリ"}, {"Column18", "サイズ"}, {"Column19", "色"}, {"Column20", "その他"}})
    in
        #"Renamed Columns"
in
    Source

このコードは、特定のExcelファイルを読み込み、最初の4行を削除し、5行目をヘッダーとして設定します。また、列名を変更する操作も含まれています。このコードを参考に、ご自身のファイルの構造に合わせて調整してください。

M言語のコードを理解し、カスタマイズすることで、より複雑なデータ処理にも対応できます。例えば、特定の条件に基づいて行をフィルタリングしたり、新しい列を追加したりすることも可能です。

4. クエリの結合とデータの整形

カスタム関数を作成し、不要な行の削除とヘッダーの設定が完了したら、すべてのファイルを結合し、データの整形を行います。

1. ファイルの結合: Power Queryエディターで、すべてのファイルを結合します。これは、「ホーム」タブにある「クエリの結合」→「クエリのマージ」または「クエリの追加」を使用して行います。
2. データの整形: 結合されたデータに対して、必要に応じてデータの型変換や列の追加、削除を行います。
3. クエリの適用: データの整形が完了したら、「閉じて読み込む」をクリックして、Excelシートにデータを読み込みます。

この手順により、複数のExcelファイルからデータを効率的に取り込み、分析やレポート作成に利用できる状態にすることができます。

5. エラーハンドリングとトラブルシューティング

Power Queryを使用する際には、エラーが発生することがあります。エラーが発生した場合の対処法を理解しておくことは重要です。

• エラーの原因特定: エラーが発生した場合は、エラーメッセージを確認し、原因を特定します。よくある原因としては、ファイルの形式の違い、データの型の不一致、ファイルパスの間違いなどがあります。
• エラーの修正: エラーの原因に応じて、クエリを修正します。例えば、データの型を変更したり、ファイルパスを修正したりします。
• トラブルシューティングのヒント:
  • クエリを段階的に実行して、エラーが発生する箇所を特定します。
  • M言語のコードを直接編集して、より詳細なエラー情報を確認します。
  • Power Queryのヘルプを参照したり、オンラインで情報を検索したりして、解決策を探します。

エラーが発生した場合でも、諦めずに原因を特定し、修正することで、Power Queryを使いこなせるようになります。

6. 実践的な応用例：月次レポートの自動化

Power Queryの活用例として、月次レポートの自動化を紹介します。この例では、毎月更新されるExcelファイルをPower Queryで取り込み、レポートを自動的に更新する方法を説明します。

1. データの準備: 毎月同じ形式のExcelファイルが作成されるようにします。
2. クエリの作成: 前述の手順に従って、Power QueryでExcelファイルを取り込み、データの整形を行います。
3. レポートの作成: Power Queryで取り込んだデータを使用して、ピボットテーブルやグラフを作成し、レポートを作成します。
4. レポートの更新: 毎月、新しいExcelファイルを同じフォルダに保存するだけで、Power Queryが自動的にデータを更新し、レポートも最新の状態に保たれます。

この方法により、月次レポート作成にかかる時間を大幅に削減し、データ分析の効率を向上させることができます。

7. Power Queryの高度なテクニック

Power Queryには、さらに高度なテクニックがあります。これらのテクニックを習得することで、より複雑なデータ処理が可能になります。

• パラメーターの使用: パラメーターを使用すると、クエリの柔軟性を高めることができます。例えば、ファイルパスや日付範囲をパラメーターとして設定し、簡単に変更できるようにします。
• 関数の作成: カスタム関数を作成することで、繰り返し行う処理を効率化できます。
• データの結合: 複数のデータソースを結合し、統合されたデータセットを作成します。
• データのピボット: データをピボットして、分析に適した形式に変換します。

これらの高度なテクニックを習得することで、Power Queryの利用範囲を広げ、データ分析の能力を向上させることができます。

8. Power Queryと他のツールとの連携

Power Queryは、他のMicrosoft製品や外部ツールとも連携することができます。これにより、データ分析の幅が広がります。

• Power BIとの連携: Power Queryで作成したクエリをPower BIにインポートし、ダッシュボードやレポートを作成します。
• SQL Serverとの連携: Power QueryでSQL Serverのデータを取得し、分析を行います。
• CSVファイルとの連携: CSVファイルなどのテキストデータをPower Queryで取り込み、加工します。

これらの連携により、Power Queryはデータ分析の強力なツールとして、さらに活用できます。

9. まとめ：Power Queryを使いこなすためのステップ

Power Queryを使いこなすためには、以下のステップを意識することが重要です。

1. 基本操作の習得: データの取得、クエリの編集、データの整形など、Power Queryの基本操作を習得します。
2. M言語の理解: M言語の基本的な構文を理解し、カスタム関数を作成できるようになることが望ましいです。
3. 実践的な練習: 実際にデータを取り込み、加工する練習を重ねることで、Power Queryのスキルを向上させます。
4. エラーへの対応: エラーが発生した場合、原因を特定し、修正する能力を身につけます。
5. 継続的な学習: Power Queryは進化し続けているため、常に新しい情報を学び、スキルをアップデートすることが重要です。

これらのステップを踏むことで、Power Queryを効果的に活用し、データ分析の効率を格段に向上させることができます。

10. 結論：Power Queryでデータ処理を効率化する

この記事では、Power Queryを使用して、フォルダ内の複数のExcelファイルを効率的に取り込む方法について解説しました。不要な行の削除、ヘッダーの設定、カスタム関数の作成、クエリの結合、データの整形など、具体的な手順とテクニックを紹介しました。

Power Queryを使いこなすことで、データ分析の時間を大幅に短縮し、より高度な分析に集中することができます。ぜひ、この記事で紹介した内容を参考に、Power Queryを活用してデータ処理の効率化を図ってください。

“`