(2022/02/18に記述しました)

　#14のように、まとめようとするエクセルファイルの形式が異なっている場合、どのようにまとめていくかを考えます。
　スライドは、Speaker Deckを参照してください。

元のファイルと目標のデータテーブル

　文部科学省の学校基本調査のデータをe-Statからダウンロードして使っていきます。
　今回使うエクセルファイルを見てみると、以下のようなものがありました。

　1つ目はA1から始まる表になってて使いやすそうです。その次は、分類が増えて見栄えもよくした感じで、最後のはやたら空の列や行を入れてます。これを以下のようなデータテーブルに変換したいと思います。

1.フォルダーパスをパラメータに設定

　対象のエクセルファイルが入ったフォルダーのパスを、パラメータに設定します。

2.フォルダー内のファイルを表示

　フォルダー内のファイルを表示するクエリーを作成します。

Query

let
    Source = Folder.Files(FolderPath)
in
    Source

3.サンプルファイルを作成

　2で表示された1番目のエクセルファイル(1976.xls)をサンプルファイルとして登録します。

SampleFile1976

let
    Source = Folder.Files(FolderPath){[Name="1976.xls"]}[Content]
in
    Source

4.サンプルファイルへのパラメータを作成

　サンプルファイルを加工するクエリを作る前に、パラメータを作成します。

このパラメータは、サンプルファイルを渡すためのものなので、種類はバイナリにします。

5.サンプルファイルの変換クエリを作成

　変換作業を行う前に、サンプルファイルを読み込む部分を書きます。

ConvertSampleFile1976

let
    Source = Excel.Workbook(Parameter1976),
    WorkSheet = Source{0}[Data]
in
    WorkSheet

Parameter1976 →パラメータで入力ファイルを指定
{0} → ここでは、データの入っているシートは1番目のものを指定
[Data] → Data(シート内のデータ)とName(シート名)の2つの項目のうちDataを指定

6.変換作業を記述していく

　サンプルファイルを読み込んだ後の処理をGUIで操作していきます。

ConvertSampleFile1976

let
    Source = Excel.Workbook(Parameter1976){0}[Data],
    PromotedHeaders = 
        Table.PromoteHeaders(Source, 
        [PromoteAllScalars=true]
    ),
    ChangedType = 
        Table.TransformColumnTypes(
            PromotedHeaders,{
                {"区分", type text}, 
                {"国立", Int64.Type}, 
                {"公立", Int64.Type}, 
                {"私立", Int64.Type}
            }
        ),
    UnpivotedOtherColumns = 
        Table.UnpivotOtherColumns(
            ChangedType, 
            {"区分"}, 
            "属性", 
            "値"
        ),
    // エラー値を0に置換
    ReplacedErrors = 
        Table.ReplaceErrorValues(
            UnpivotedOtherColumns, 
            {{"値", 0}}
        )
in
    ReplacedErrors

7.関数の作成

　出来上がったクエリを右クリックし、関数の作成を選択し、関数を作ります。

8.各ファイルに関数を使ってみる

　最初に作成したフォルダー内のファイルを表示させるクエリに、関数を適用する項目を追加します。

Query

let
    Source = Folder.Files(FolderPath),
    // Excelからテーブルを取得
    Step1 = Table.TransformColumns(
        Source,
        {
            "Content",
            ConvertFx1976
        }
    )
in
    Step1

　1行目は正しく変換されていますが、2行目はエラーとなっています。

9.別の関数を作成

　最初の関数では処理できないので、新しく関数を作成するため、３～７を行います。

　ここで作成したクエリは以下のようなものでした。

ConvertSampleFile1977

let
    // エクセルの最初のシートを開く
    Source = Excel.Workbook(Parameter1977){0}[Data],
    ChangedType = 
        Table.TransformColumnTypes(
            Source,
            {
                {"Column1", type text}
            }
        ),
    // 有効な行を選択
    FilteredRows = 
        Table.SelectRows(
            ChangedType, 
            each (
                [Column1] = "区分" or 
                [Column1] = "男女ともにいる学校" or 
                [Column1] = "男のみの学校" or 
                [Column1] = "女のみの学校" or 
                [Column1] = "生徒のいない学校"
            )
        ),
    // 1行目をヘッダーに
    PromotedHeaders = 
        Table.PromoteHeaders(
            FilteredRows, 
            [PromoteAllScalars=true]
        ),
    FilteredRows1 = 
        Table.SelectRows(
            PromotedHeaders,
            each ([区分] <> "区分")
        ),
    // 不要な列を削除
    RemovedOtherColumns = 
        Table.SelectColumns(
            FilteredRows1,
            {"区分", "国立", "公立", "私立"}
        ),
    // データ型を指定
    ChangedType1 = 
        Table.TransformColumnTypes(
            RemovedOtherColumns,
            {
                {"区分", type text}, 
                {"国立", Int64.Type}, 
                {"公立", Int64.Type}, 
                {"私立", Int64.Type} 
            }
        ),
    // ピボット解除
    UnpivotedOtherColumns = 
        Table.UnpivotOtherColumns(
            ChangedType1, 
            {"区分"}, 
            "属性", 
            "値"
        ),
    // エラー値を0に置換
    ReplacedErrors = 
        Table.ReplaceErrorValues(
            UnpivotedOtherColumns, 
            {{"値", 0}}
        )
in
    ReplacedErrors

10.再度関数を当ててみる

　クエリーを書き換えて、もう一度確認してみます。

Query

let
    Source = Folder.Files(FolderPath),
    // Excelからテーブルを取得
    Step1 = Table.TransformColumns(
        Source,
        {
            "Content",
            each 
                try ConvertFx1976(_)
                otherwise ConvertFx1977(_)
        }
    )
in
    Step1

　まだ1行、エラーが発生しています。

11.3つ目の関数を作成して当ててみる

　３～７を繰り返して関数を作ります。
　クエリに新たに作った関数を追加して適用させます。

Query

let
    Source = Folder.Files(FolderPath),
    // Excelからテーブルを取得
    Step1 = Table.TransformColumns(
        Source,
        {
            "Content",
            each 
                try ConvertFx1976(_)
                otherwise try ConvertFx1977(_)
                otherwise ConvertFx2008(_)
        }
    )
in
    Step1

12.テーブルを結合させて完成

　クエリの続きを記述し、完成です。

Query

let
    Source = Folder.Files(FolderPath),
    // Excelからテーブルを取得
    Step1 = Table.TransformColumns(
        Source,
        {
            "Content",
            each 
                try 
                    ConvertFx1976(_)
                otherwise  try 
                    ConvertFx1977(_)
                otherwise
                    ConvertFx2008(_)
        }
    ),
    // ファイル名をエクセル表に追加して結合
    Step2 = Table.AddColumn(
        Step1,
        "Workbooks",
        (x)=>
            Table.AddColumn(
                x[Content],
                "Year",
                each Number.FromText(Text.Select(x[Name],{"0".."9"})),
                Int64.Type
            )
    ),
    Step3 = Table.Combine(Step2[Workbooks])
in
    Step3

　このデータを使って以下のグラフを作成しました。

注意点

　今回は、作成した関数が正常化エラーかで判断していますが、エラーにならないけど正常値を返さないパターンもあると思います。その場合は、どちらでも正常値になるようにクエリ内の処理を追加するか、エラーをわざと発生させるかの対応が必要になります。