[Power Query] １列に積み重なったデータを表形式にするベストプラクティスは？ (4) Table.AlternateRows → Table.FromColumns

※この記事は 2023/7/22 に作成しました。
※オプションで、「地域の設定」の「クエリステップ」は、「常に英語」に設定されています。日本語で使用している場合は、各自環境で作成されるコードに読み替えてください。

以下のように、複数の項目が1列になっているデータをテーブルにする方法の4番目です。

Index

• (1) Table.AddIndexColumn → Table.Pivot
• (2) Table.Split → Table.Transpose → Table.Combine
• (3) List.Split → Table.FromRows
• (4) Table.AlternateRows → Table.FromColumns
• (5) List.Alternate → Table.FromColumns
• (6) まとめ

解法4: Table.AlternateRowsを使う

Table.AlternateRows

　まず最初に、Table.AlternateRows関数の動作について説明します。
　「ホーム」タブで「行の削減」グループの「行の削除」→「代替業の削除」を選択します。
　表示されたダイアログの「削除する最初の行」に２、「削除する行の数」に２、「保持する行の数」に１を入力して実行します。

この操作で生成されるコードは、以下のようになります。

RemovedAlternateRows = 
    Table.AlternateRows(
        Source,
        1,
        2,
        1
)

　構文は以下のようになっています。

構文

Table.AlternateRows(
    table as table, 
    offset as number, 
    skip as number, 
    take as number
) as table

　第二引数のoffsetが「削除する最初の行」になりますが、入力した値は 2 ですが、コードでは 1 になっています。これは、コードの上では 0 からスタートしているためです。

　この関数を使って、それぞれの列の値を作成していきます。

　2列目の著者名を抜き出すためには、「削除する最初の行」に3を入れればいいのではと思うかもしれませんが、そうすると一番最初の書籍名が残ってしまいます。

　なので、最初にテーブルの最初のレコードをスキップして処理を行うようにします。

RemovedAlternateRows = 
    Table.AlternateRows(
        Table.Skip(Source, 1),
        1,
        2,
        1
    )

　同様に、出版日も最初の2つのレコードをスキップして処理を行い、3つの項目の作成方法が出来上がります。

Table.FromColumns

　これを1つのテーブルにまとめるのには、Table.FromColumns 関数を使います。前回使用した Table.FromRowsが横に展開したのに対して、Table.FromColumnsは縦に展開します。ここからは、詳細エディタを使用してコードを書き込んでいきます。

構文

Table.FromColumns(
    lists as list, 
    optional columns as any
) as table

　第一引数は、入れ子になったリストにしなければなりません。3項目をつなぎ合わせると、以下のようなコードになります。

Column1 = Table.ToList( Table.AlternateRows( Source, 1,2,1 ) ),
Column2 = Table.ToList( Table.AlternateRows( Table.Skip( Source, 1 ), 1,2,1 ) ),    
Column3 = Table.ToList( Table.AlternateRows( Table.Skip( Source, 2 ), 1,2,1 ) ),    
TableAlternateRows = 
    Table.FromColumns( 
        {
            Column1, 
            Column2, 
            Column3 
        }
    )

　関数を入れ子にすると、以下のようになります。

TableAlternateRows = Table.FromColumns(
    {
        Table.ToList(
            Table.AlternateRows(Source, 1,2,1)
        ),
        Table.ToList(
            Table.AlternateRows(
                Table.Skip(Source,1), 
                1,2,1
            )
        ),
        Table.ToList(
            Table.AlternateRows(
                Table.Skip(Source,2), 1,2,1
            )
        )
    }
)

　列ごとに並べて記述するのが見苦しいと思うなら、List.Transformで繰り返し処理を行うこともできます。Table.Skipの第二引数の _ は、0～2までの値が入ります。

TableAlternateRows = Table.FromColumns(
    List.Transform(
        {0..2},
        each Table.ToList( 
            Table.AlternateRows( 
                Table.Skip( Source, _), 
                1, 2, 1
            ) 
        )
    )
)

　Table.ToListを使わずに、以下のように書くこともできます。

TableAlternateRows = Table.FromColumns(
    List.Transform(
        {0..2},
        each Table.AlternateRows( 
                Table.Skip( Source, _), 
                1, 2, 1
        )[Column1]
    )
)

テーブルをTable.ToColumns関数でテーブルを入れ子になったリストに変換し、List.Firstでリスト内の最初の項目のみを抜き出してリストにするという方法もあります。

TableAlternateRows = Table.FromColumns(
    List.Transform(
        {0..2},
        each List.First(
            Table.ToColumns(
                Table.AlternateRows( 
                    Table.Skip( Source, _), 
                    1, 2, 1
                )
            )
        )
    )
)

ソースコード

let
    // ファイルの読み込み
    Source = Table.FromColumns(
        {
            Lines.FromBinary(
                File.Contents( "** File Name **" ),
                null,
                null,
                932  // シフトJIS
            )
        }
    ),
    // 変換処理
    TableAlternateRows = Table.FromColumns(
        List.Transform(
            {0..2},
            each Table.AlternateRows( 
                    Table.Skip( Source, _), 
                    1, 2, 1
            )[Column1]
        )
    ),
    // 項目名の変更
    RenamedColumns =  Table.RenameColumns(
        TableAlternateRows,
        {
            {"Column1", "書名"}, 
            {"Column2", "著者名"}, 
            {"Column3", "出版日"}
        }
    )
in
    RenamedColumns