Zenn
🤹‍♂️

見出しと内容が多段のテーブルを何とかする関数

2024/09/05に公開
Power Query
tech

目的

Xで面白そうなクイズを見かけたので、できるだけ汎用的な関数として作成できないかチャレンジしてみました。

https://x.com/excelspeedup/status/1831154240116645918

要は、こういうあまり出会いたくないテーブルを

Column1 Column2 Column3 Column4 Column5 Column6 Column7
取引No 日付 商品コード 商品名 単価 数量 金額
取引先コード 取引先名
1 45293 A380 テレビ 44474 1 44474
S565 向日葵
2 45294 A885 加湿器 33650 5 168250
S428 DAI
3 45294 A885 加湿器 29792 4 116168
S565 向日葵
4 45304 A142 冷蔵庫 128845 4 515380
S184 M&M
5 45306 A885 加湿器 34858 1 34858
S223 エルム

こうしたいってコトですね。

取引No 日付 商品コード 商品名 単価 数量 金額 取引先コード 取引先名
1 45293 A380 テレビ 44474 1 44474 S565 向日葵
2 45294 A885 加湿器 33650 5 168250 S428 DAI
3 45294 A885 加湿器 29792 4 116168 S565 向日葵
4 45304 A142 冷蔵庫 128845 4 515380 S184 M&M
5 45306 A885 加湿器 34858 1 34858 S223 エルム

コード

MergeTableWithMultipleHeaders
(table as table, headerRows as number) =>
let
    interval = headerRows - 1,
    eachTables =
        List.Transform(
            {0..interval},
            (n) =>
            let
                offset = Table.RemoveFirstN(table, n),
                alternated = Table.AlternateRows(offset, 1, interval, 1)
            in
                Table.PromoteHeaders(alternated)
        ),
    toRecords = List.Transform(eachTables, Table.ToRecords),
    zip = List.Zip(toRecords),
    combine = List.Transform(zip, Record.Combine),
    columns =
    let
        records = Table.ToRecords(Table.FirstN(table, headerRows)),
        values = List.TransformMany(records, Record.FieldValues, (x, y) => y)
    in
        List.RemoveNulls(values)
in
    Table.SelectColumns(
        Table.FromRecords(combine),
        columns
    )

解説

このコードは、Power Queryで特定のテーブルからヘッダー行を考慮してデータを加工し、新しいテーブルを生成するためのM言語の関数です。table という入力テーブルと headerRows というヘッダー行数を受け取り、そのデータを加工して適切にヘッダーを割り当て、結果として新しいテーブルを返す処理を行っています。

パラメータ

  • table: 加工の対象となるテーブル。
  • headerRows: テーブルの先頭何行がヘッダー行であるかを示す数。

処理の流れ

  1. interval の計算:

    interval = headerRows - 1
    • headerRows から 1 を引いた値が interval になります。後の Table.AlternateRows で間隔を指定するために使用します。

  2. 各テーブルの作成:

    eachTables =
     List.Transform(
         {0..interval},
         (n) =>
         let
             offset = Table.RemoveFirstN(table, n),
             alternated = Table.AlternateRows(offset, 1, interval, 1)
         in
             Table.PromoteHeaders(alternated)
     )
    • {0..interval} は 0 から interval までのリストを生成します。これはヘッダーの行数に基づいて、複数のテーブルを作成するために使います。
    • List.Transform により、それぞれのリスト要素に対して次の操作を行います：
      • Table.RemoveFirstN(table, n) で最初の n 行を削除します。
      • Table.AlternateRows(offset, 1, interval, 1) で、最初の行から始まり、interval 行ごとに 1 行を取り出したテーブルを作成します。
      • Table.PromoteHeaders(alternated) で、取り出したテーブルの最初の行をヘッダーに昇格させます。

  3. テーブルからレコードのリストへの変換:

    toRecords = List.Transform(eachTables, Table.ToRecords)
    • 生成された複数のテーブルを、各行がレコード形式（キーと値のペアのコレクション）に変換します。

  4. リストのジップ処理とレコードの結合:

    zip = List.Zip(toRecords)
combine = List.Transform(zip, Record.Combine)
    • List.Zip で複数のリストをジップ処理し、同じ位置にあるレコードをまとめます。
    • List.Transform(zip, Record.Combine) で、ジップされた各レコードを統合して1つのレコードに結合します。

  5. ヘッダー列名の取得:

    columns =
 let
     records = Table.ToRecords(Table.FirstN(table, headerRows)),
     values = List.TransformMany(records, Record.FieldValues, (x, y) => y)
 in
     List.RemoveNulls(values)
    • 最初の headerRows 行を取得して、それをレコードに変換します。
    • List.TransformMany を使い、各レコードのフィールド値を取り出してリスト化します。
    • List.RemoveNulls(values) で null 値を除去し、ヘッダーとして使える列名を取得します。

  6. 最終的なテーブルの生成:

    Table.SelectColumns(
     Table.FromRecords(combine),
     columns
)
    • combine で結合されたレコードリストから、新しいテーブルを生成します (Table.FromRecords)。
    • そのテーブルから、columns で抽出したヘッダーに該当する列だけを選択します。

概要

このコードは、指定されたヘッダー行数を基にして、複数の部分テーブルを生成し、それらをマージして一つのテーブルを作成するプロセスです。また、正しいヘッダー行を割り当てた上で、必要な列だけを選択する処理も含まれています。

使用例

MergeTableWithMultipleHeaders(tableToMerge, 2)

Discussion