はじめに

2020/07/05に行われたPower Platform Day Summer '20で、「PBIJPメディック第伍処置室 : Power BI 科」にずっといたのですが、その中で、「フォルダ内のファイルを一括で取得する」という話題が出てきたので、割と使えそうな考え方なので、ここにメモしておきます。

（本記事の内容は2020年7月現在の内容です。）

どういうことをするのか

フォルダの中にある同じフォーマットのファイルを一括で読み込んでみたいというネタは、結構あるあるな訳です。

やり方としては、ざっくりいうと、
1. 「データを取得」→「詳細」→「フォルダー」→「接続」
2. フォルダーのパスを指定
3. 「データの変換」でフォルダー内のファイル一覧を取得
4. [Content]列にある「ファイルの結合」ボタンを押して結合

という流れになります。その結果、図のようにクエリや関数がいろいろと自動的に作成されます。

ちなみに、ここで「サンプルファイルの変換」クエリを編集すると、「その他のクエリ」にある全ファイルを結合したクエリ（図の[customs202005]）に編集が反映されます。
（今回のサンプルでは、フォルダ内のファイルはすべて同じフォーマットのデータで、フォルダ内には結合しないファイルは含まれないものとしています。使用しているファイルは「貿易統計全国分品別国別表輸出 2020年5月」のCSVファイルです。）

何かご不満でも？～作られたものはなんだ！

このやり方で何ら問題ないのですが、こんなことを思ったりする人がいるものです。

なんか、クエリがいろいろできて、もっとすっきりできないのかい？？

ならば、これらの自動で作成されたクエリなどは、それぞれどんなことをやっているのか、を詳しく見ていきたいとおもいます。

パラメーター1

関数「ファイルの変換」の入力になるパラメータを定義しています。「サンプルファイル」という値が入っています。

サンプルファイル

これの内容を詳細エディターで見ると、

let
    Source = Folder.Files("[選択したフォルダーのパス名]"),
    Navigation1 = Source{0}[Content]
in
    Navigation1

となっています。Folder.Filesでフォルダー内のファイルの一覧を取得して、Source{0}[Content]はファイルの一覧の１行目の[Content]列の内容を表します。[Content]列の内容は、各ファイルの中身がバイナリで入っているので、この結果、フォルダ内のとある１つのファイルの内容そのものとなります。

ファイルの変換 & サンプルファイルの変換

「ファイルの変換」を詳細エディターで見ると、

let
    ソース = (パラメーター1 as binary) => let
        Source = Csv.Document(パラメーター1,[Delimiter=",", Columns=45, Encoding=932, QuoteStyle=QuoteStyle.None]),
        #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
    in
        #"Promoted Headers"
in
    ソース

そして、「サンプルファイルの変換」を詳細エディターで見ると、

let
    Source = Csv.Document(パラメーター1,[Delimiter=",", Columns=45, Encoding=932, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
    #"Promoted Headers"

となっています。let以降の内容は、両者とも同じ内容になっています。実際、「ファイルの変換」を詳細エディターで開くと、

というメッセージが表示されます。直接変更すると「サンプルファイルの変換」を変更しても「ファイルの変換」に反映されなくなります。フォルダ内ファイルの変換の際にすべてのファイルについて、列の型の変換や列の削除、値の置換などを行いたい場合は、「サンプルファイルの変換」のクエリを変更してください。

「その他のクエリ」グループ下に作成されるクエリ

これまで紹介したクエリや関数などを使って、最終的にフォルダ内のファイルを結合したクエリを作成しています。これを詳細エディターで見ると、このようになっています。

let
    Source = Folder.Files("[選択したフォルダーのパス名]"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "ファイルの変換", each ファイルの変換([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "ファイルの変換"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプル ファイル")))
in
    #"Expanded Table Column1"

各ステップについてみていきます。

Source = Folder.Files("[選択したフォルダーのパス名]")では、フォルダ内のファイルの一覧のテーブルを作成します。
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true)では、フォルダ内のファイルから隠しファイルに関する行をフィルターをかけて除外しています。
"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "ファイルの変換", each ファイルの変換([Content]))では、ファイル一覧のテーブルに新たな列を追加します。そして、列の名前は[ファイルの変換]、列の中身は各行の[Content]列の内容を「ファイルの変換」関数に投入して帰ってくるテーブルが入ります。
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"})はファイル名が入っている[Name]列の名前を[Source.Name]に変更しています。
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "ファイルの変換"})は、ファイル名のある[Source.Name]列と変換したテーブルが入っている[ファイルの変換]列以外の列を削除しています。
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "ファイルの変換", Table.ColumnNames(ファイルの変換(#"サンプルファイル")))は、[ファイルの変換]列にあるテーブルの中身を展開しています。展開したテーブルの列は「サンプルファイル」を「ファイルの変換」関数で変換したテーブルの各列の名前Table.ColumnNames(ファイルの変換(#"サンプルファイル"))を使って指定しています（この例では、すべての列が指定されることになります。）。

コードを組み合わせてみる

さて、自動で作成されたクエリ等の内容が大体わかったところで、これらのコードを組み合わせて、フォルダ内のファイルを結合するクエリを新たに直接作成してみたいと思います。

まず、フォルダ内のファイルの一覧を作るところは、

    Source = Folder.Files("[選択したフォルダーのパス名]"),
    #"Filtered Hidden Files" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true)

でよいです。ここで、変換対象にしたくないファイルがあれば、さらにファイル名（[Name]列）、ファイルの拡張子（[Extension]列）、ファイルの作成日時（[Date created]列）、ファイルの更新日時（[Date modified]列）などをフィルター処理で除外することもできます。

つぎに、[Content]列の内容であるバイナリファイルをテーブルに変換する作業ですが、

"Invoke Custom Function" = 
    Table.AddColumn(
        #"Filtered Hidden Files", 
        "ファイルの変換", 
        each ファイルの変換([Content])
    )

のeach ファイルの変換([Content])の部分は、「ファイルの変換」のコードを使って、関数を入れ子にすると、

each Table.PromoteHeaders(                  // 「ファイルの変換」の#"Filtered Hidden Files"
    Csv.Document(                           // 「ファイルの変換」のSource
        [Content],                          // 変換するのは[Content]列の内容（元のファイルの内容・バイナリファイル）
        [Delimiter=",", Columns=45, Encoding=932, QuoteStyle=QuoteStyle.None]
    ),
    [PromoteAllScalars=true]
)

に書き換えることができます。

#"Invoke Custom Function" = 
    Table.AddColumn(
        #"Filtered Hidden Files", 
        "File converted",                   // 変換後のテーブルが入る列名
        each Table.PromoteHeaders(
            Csv.Document(
                [Content],
                [Delimiter=",", Columns=45, Encoding=932, QuoteStyle=QuoteStyle.None]
            ),
            [PromoteAllScalars=true]
        )
    )

今回の例では、CSVファイルの変換ですが、ほかにExcelファイル(Excel.Workbook)、XMLファイル(Xml.Document)、JSONファイル(Json.Document)など様々なファイル変換用のコネクタがあるので、変換したいファイル形式に合わせてeachの部分を書いてください。
（フォルダ内の1つのファイルをテーブルに変換するクエリを作成して、そこからコードを取得すると作りやすいです。ファイルの中身をバイナリとして入力してテーブルを返す関数が必要です。）

あとは、変換後のテーブルが入る列を残して、他の列を削除します。（ファイル名のある列も残すと便利な場合があります）

#"Removed Other Columns" = Table.SelectColumns(#"Invoke Custom Function", {"File converted"})

// ファイル名を残す場合
#"Removed Other Columns" = Table.SelectColumns(#"Invoke Custom Function", {"Name", "File converted"})

最後に、テーブルを展開します。展開にはTable.ExpandTableColumn関数を使います。その書式は、

Table.ExpandTableColumn(table as table, column as text, columnNames as list, optional newColumnNames as nullable list) as table

ここで、columnNamesには変換後のテーブルの展開したい列名のリスト、newColumnNamesには展開後の列名のリストを指定します（省略するとcolumnNamesと同じ名前になるが、他の列名と重複しないように注意）。展開したい列を直接指定することができるので、ここで必要な列だけを指定することができます。ここでは、一部の列だけを展開することにしてみます。

#"Expanded {0}" =
    Table.ExpandTableColumn(
        #"Removed Other Columns", 
        "File converted", 
        {"Exp or Imp", "Year", "HS", "Country", "Unit1", "Unit2", "Quantity1-Year", "Quantity2-Year", "Value-Year"}
    )

これで、フォルダ内のファイルを結合したテーブルが完成しています。このクエリでは、**先の自動で作成されたファイルを使うことなく結合に成功しています。以上のコードを全部つなげたものを以下に示します。

let
    Source = Folder.Files("[選択したフォルダーのパス名]"),
    #"Filtered Hidden Files" = 
        Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function" = 
        Table.AddColumn(
            #"Filtered Hidden Files", 
            "File converted", 
            each Table.PromoteHeaders(
                Csv.Document(
                    [Content],
                    [Delimiter=",", Columns=45, Encoding=932, QuoteStyle=QuoteStyle.None]
                ),
                [PromoteAllScalars=true]
            )
        ),
    #"Removed Other Columns" = 
        Table.SelectColumns(#"Invoke Custom Function", {"File converted"}),
    #"Expanded {0}" = 
        Table.ExpandTableColumn(
            #"Removed Other Columns", 
            "File converted", 
            {"Exp or Imp", "Year", "HS", "Country", "Unit1", "Unit2", "Quantity1-Year", "Quantity2-Year", "Value-Year"}
        )
in
    #"Expanded {0}"

これが基本形で、Table.SelectRowsやTable.ExpandTableColumnなどのところで、うまく設定してあげると、いろいろと応用が効くと思われます。

おわりに

今回はローカルPCのファイルフォルダーの中にあるファイルを対象にしていますが、ファイルの一覧を取得するコネクタはほかにもあるので、いろいろと応用してみてください。
[Power BI Tips] フォルダー系コネクタにはいくつかあるけどだいたい同じ!?

注意！ ここに書いてあることが全く分からないで、コードをコピペするだけでできるとは思わないほうが賢明です。

Power BI でフォルダ内のファイルを一括で取得する ~2nd Season~