Power BIで複数ファイルをインポートしたい

Power BIで複数ファイルをインポートしたい

同じ情報を持つ（カラム定義が同じなど）複数ファイルを束ねて活用したいことがある。
複数ファイルをインポートする手順について記載する。

複数ファイルをインポートする手順

前提

• Power BI DesktopのPower Queryエディターで実施している。
• インポートしたい複数ファイルはローカルに存在し、同じフォルダー内にあるものとする。
  • multiple_filesフォルダーに3つのCSVファイル
    • hoge.csv/fuga.csv/piyo.csv
    • それぞれidとname列をもつ

手順1.新しいソースを選択

手順2.フォルダーコネクタで接続してフォルダパスを指定

• フォルダーパスには複数ファイルが存在するフォルダーを指定する。

フォルダーコネクタについて

手順3.データの結合と変換を選択

• フォルダー内の各ファイルに関する情報をもつテーブルが表示される。

手順4.ファイルの結合の各項目を適切に選択

• 各項目
  • サンプルファイル：どのファイルを基準に結合するか。（fuga.csv）
  • 元のファイル：ファイルの保存形式にあわせる。
  • 区切り記号：ファイル形式にあわせる。
  • データ型検出：各列のデータ型を推測するときに利用されるデータセット範囲

手順5. OKを選択

• その他のクエリグループにmultiple_filesというクエリができる。
  • 複数ファイルが結合された結果を含むテーブル
    • Source.Name列は、どのファイルに存在していたかを示す。
  • クエリ名は、デフォルトではインポートしたフォルダー名となる。
• ヘルパークエリグループにクエリが自動で作成される。

Appendix

上記手順を実施したことで具体的にどのようなクエリが作成され、実行されているか記載する。

パラメーターやカスタム関数などPower Queryについて基本知識がある前提で記載している。
不明な用語があれば調べながら読んでいただきたい。

クエリの関係性

クエリの主な関係を示す。

パラメーター1

パラメーター1クエリはパラメーターであり、バイナリデータ型でその既定値としてサンプルファイルが設定されている。

パラメーターとは、Power Queryの再利用できる値を管理するための機能である。

必ずパラメーター1という名前になるわけではない。
すでに同じクエリ名がある場合は、パラメーター2と数字がインクリメントされたパラメーター名となる。

サンプルファイル

手順4で指定したファイルのバイナリデータを取得している。
本手順では、パラメーター1にfuga.csvのバイナリデータが設定されている。

サンプルファイル

let
    // 指定されたフォルダー（multiple_files）の情報をテーブルとして読み込む
    ソース = Folder.Files("C:\Users\XXXX\Desktop\import_multiple_files\multiple_files"),
    // 指定されたファイル（fuga.csv）のバイナリデータ（[Content]）を返す
    #"C:\Users\XXXX\Desktop\import_multiple_files\multiple_files\_fuga csv1" = ソース{[#"Folder Path"="C:\Users\XXXX\Desktop\import_multiple_files\multiple_files\",Name="fuga.csv"]}[Content]
in
    #"C:\Users\XXXX\Desktop\import_multiple_files\multiple_files\_fuga csv1"

ファイルの変換

ファイルの変換はカスタム関数であり、パラメーター1を引数としてパラメーター1に格納されているバイナリデータからCSVファイルの中身を取得する関数である。

ファイルの変換

let
    // JavaScriptのアロー関数のような形式
    ソース = (パラメーター1) => let
        // `サンプルファイルの変換`のクエリと連動（詳細はサンプルファイルの変換で記載）
        ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=2, Encoding=932, QuoteStyle=QuoteStyle.None]),
        昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])
    in
        昇格されたヘッダー数
in
    ソース

カスタム関数とは再利用できる関数であり、同じ変換を異なるクエリや値に適用したい場合に便利な機能である。

サンプルファイルの変換のクエリと連動している。
サンプルファイルの変換のクエリを変更すると自動でファイルの変換のクエリも変更される。
そのため、ファイルの変換のクエリを直接編集しようとすると、連動を解除して直接編集を許可するか確認ダイアログが表示されるため、必要に応じて判断する。

サンプルファイルの変換

サンプルファイルをどう読み込むかのクエリが記載されている。
このクエリに基づいて各ファイルを読み込むため、各ファイル共通で実施したい処理はここに記載する。

サンプルファイルの変換

let
    // CSVを指定された設定で読み込み
    ソース = Csv.Document(パラメーター1,[Delimiter=",", Columns=2, Encoding=932, QuoteStyle=QuoteStyle.None]),
    // 最初の行をヘッダーとして利用
    昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true])
in
    昇格されたヘッダー数

はじめに記載されているクエリは、手順4で指定した設定に基づいた処理となる。

結合された結果を含むクエリ

結合された結果を含むクエリは下記のようになる。

結合された結果を含むクエリ

let
    ソース = Folder.Files("C:\Users\XXXX\Desktop\import_multiple_files\multiple_files"),
    #"フィルター選択された非表示の File1" = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),

    // バイナリデータから`ファイルの変換`関数を使って中身をテーブルとして取得し`ファイルの変換`列として追加
    カスタム関数の呼び出し1 = Table.AddColumn(#"フィルター選択された非表示の File1", "ファイルの変換", each ファイルの変換([Content])),
    
    #"名前が変更された列 1" = Table.RenameColumns(カスタム関数の呼び出し1, {"Name", "Source.Name"}),
    削除された他の列1 = Table.SelectColumns(#"名前が変更された列 1", {"Source.Name", "ファイルの変換"}),
    
    // `サンプルファイル`のバイナリデータを`ファイルの変換`関数でテーブルを取得し、列名を取得
    // その列名を使って`ファイルの変換`列に格納されているテーブルを展開
    展開されたテーブル列1 = Table.ExpandTableColumn(削除された他の列1, "ファイルの変
    換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル"))),
    
    変更された型 = Table.TransformColumnTypes(展開されたテーブル列1,{{"Source.Name", type text}, {"id", Int64.Type}, {"name", type text}})
in
    変更された型

参考ページ

https://learn.microsoft.com/ja-jp/power-query/connectors/folder

https://learn.microsoft.com/ja-jp/power-query/power-query-query-parameters

https://learn.microsoft.com/ja-jp/power-query/custom-function

https://support.microsoft.com/ja-jp/office/複数のファイルを含むフォルダーからデータをインポートする-power-query-94b8023c-2e66-4f6b-8c78-6a00041c90e4