【パワークエリ】フォルダ内の複数データを一括取り込み、結合

PowerQuery PowerBI フォルダ内 複数データ 取り込み 一括PowerQuery/Pivot
スポンサーリンク

今回はPowerQueryでフォルダ内の複数シートを一括で取り込み、結合する方法を紹介します。

【こんな方におすすめ!】
フォルダ内の複数シートを
 PowerBIやPowerQueryに取り込みたい
取り込んだ複数データをマージ(結合)し、編集したい

PowerBIもPowerQuery(パワークエリ)も同様です。
フォルダに同様のExcelなどが毎回追加されるような場合のデータ更新を自動化する参考になればと思います!

PowerQueryでフォルダ内の複数ファイルの読み込みと結合

PowerBIやPowerQueryで
フォルダ内の複数ファイルを読み込む方法は、
「データの取得からフォルダーを指定する」
だけです。

それでは、具体的に手順を説明していきます。

下記のような日別の売上データExcelがフォルダ内に複数あることを想定します。1ファイルにその日の売上がのっているExcelシートを想定しています。

取り込みデータ PowerBI PowerQuery ファイル

フォルダは下記のような日付名で管理され、このフォルダに毎日売上データが更新されるとします。

PowerBI フォルダ取り込み 複数ファイル

このフォルダ内にあるExcelデータを全て取り込み、一つの結合シートをクエリ内に作成する設定をしていきます。

今回はフォルダ内全てを取り込みたいので、フォルダを指定します。
「データを取得」→「フォルダー」→「接続」をクリックします。

「参照」から取り込みたいフォルダを選択しOKをクリックし、フォルダーパスを設定します。

すると、下記のような取り込むExcelの画面が開くので、
「結合」のプルダウンから「データの結合と変換」をクリックします!

PowerQuery フォルダ 複数データ 取り込み 複数ファイル 結合

Fileの結合という画面が開くので、
取り込みたいシートを選択後、プレビューで問題ないかを確認して「OK」をクリックします。

Fileno結合 PowerQuery PowerBI フォルダ 複数Excel 複数データ

これで、フォルダ内の複数のExcelデータが結合された状態でPowerQueryが立ち上がります。

ファイル内の複数ファイルの一括で取り込みと一つに結合する設定は完了です! ただ、これでは複数のExcelデータを結合しただけなので、クエリ内で使える形に編集が必要です!

複数データを取り込んで結合する方法は下記でも解説していますので、ご参考ください。
▶PowerBIでデータの連携/結合(クエリのマージ/クエリの追加)

【結論】
・フォルダ内の複数ファイルの読み込みは
「データの取得」から「フォルダー」を指定する!
・複数ファイルのマージは「データの結合と変換」をクリック!

スポンサーリンク

PowerQueryで複数ファイルを結合後にクエリで編集

続いて、複数ファイルを一つに結合したデータをクエリ内で使える形に編集していきます。

現状取り込んだだけだと、下記のように「列名が入っていない」「データ結合部にいらない行が入る」などの問題があります。これをクエリ内で編集して正しくマージされたシートにしていきます。

PowerQuery PowerBI クエリ編集

まずは列名の設定をしていきます。
2行目を列名にもってきたいので、1行目を消去します。
「行の削除」→「上位の行の削除」から
1行目を消去したいので、行数に「1」と入力しOKをクリック!

PowerQuery クエリ編集 行削除 1行目をヘッダー

一行目が削除されました!

続いて、この1行目をヘッダーにもってくれば元のExcelデータ通りの列名を設定可能です。

「1行目をヘッダーとして使用」をクリックすればOKです。

PowerQuery クエリ PowerBI 1行目をヘッダーとして使用

これで列名が正しくなりました!

PowerQuery 編集 1行目をヘッダー

結合データでは1列目が読込ファイル列となるので、名前を変更しておきます。

PowerQuery PowerBI クエリ 列名変更

しかし、まだ各Excelデータをつなげただけなので、データ結合部にいらない行が入るのでフィルターをかけていきます。

売値の列に数値以外は不要な表なので、売値列の右の「▼」をクリックし不要な行のチェックボックスを外します。

PowerQuery PowerBI フィルター 解除 フィルター設定 クエリ編集

各データを結合したマージデータができました!

PowerQuery クエリ編集 完成

これで、フォルダ内の複数データを結合、クエリ内で使える形に編集ができました!

【結論】
結合したデータをクエリで編集しマージデータを作成!
行の消去、列名の修正フィルターで必要な行のみにしておく!

スポンサーリンク

(番外編)DAX関数でファイル名から日付への変換

このマージデータでは、どの日の売上かがわかりません。
そこで、取り込んだファイル名からDAX関数を用いて、日付に変換します。

PowerBIならクエリ画面を閉じて適用し、データシートを確認します。
ここに、日付列を追加していきます。

PowerBI データ 列追加

読込ファイル名から、DAX関数で年、月、日を取得していきます。

【年/月/日列の取得】
年 = LEFT(‘売上フォルダ'[読込ファイル],4)
月 = MID(‘売上フォルダ'[読込ファイル],6,2)
日 = MID(‘売上フォルダ'[読込ファイル],9,2)

LEFT関数で読込ファイルの名前の左から4文字を取得、MID関数で6文字目から2文字取得して!と指示を出しています。

PowerBI 年 月 日 MID LEFT RIGHT関数

▶参考:LEFT関数
▶参考:MID関数
▶参考:RIGHT関数

あとは、この年、月、日を使って日付列を作成すればOKです。

【日付データへの変換】
日付 = DATE(‘売上フォルダ'[年],’売上フォルダ'[月],’売上フォルダ'[日])

DATE関数で年、月、日を日付のデータに変換しています。

読込ファイルの名前から結合したマージデータに日付列を追加することができました!

PowerBI DATE関数 DAX 日付

▶参考:DATE関数

DAX関数 リファレンス Microsoft

https://learn.microsoft.com/ja-jp/dax/dax-function-reference

【結論】
LEFTやMID関数DATE関数などでファイル名を日付に変換

今回のような日付の変換方法として、日付列からFORMAT関数で年、月、日、曜日を取得する方法などを下記で詳しく説明しています。ぜひ、ご参考ください!

▶PowerBI FORMAT関数で日付の変換

スポンサーリンク

Power Queryでフォルダ内の一括取り込み まとめ

今回はPowerBIやPowerQueryでフォルダ内の複数データの一括取り込み方法として、

フォルダ内の複数シートの一括取り込み
取り込んだ複数シートの結合、編集
ファイル名からDAX関数で日付に変換
を説明しました。

データ分析時は、複数データのマージ(結合)をする場面が多々ありますが、PowerQueryを用いれば簡単にできます。また、ファイルが追加されてもフォルダ場所を指定しているので、ワンクリックで自動で取り込んでくれるので非常に便利です!ぜひご参考ください!

下記でパワークエリやPowerBIの便利機能とDAX関数をまとめていますので、ぜひご参考下さい。

▶パワークエリ、パワーピポットまとめ

▶PowerBI、PowerQueryの便利機能と学習方法まとめ

▶新Excelで使用必須なDAX関数まとめ

よかったら、下記☟のクリック応援もよろしくお願いします!

にほんブログ村 サラリーマン日記ブログへ
にほんブログ村

最後まで読んでいただきありがとうございました!!

コメント

タイトルとURLをコピーしました