なんでもエクセル(Nandemo Excel)

何でもエクセルでやってしまう そこのあなた。ようこそ

Power Query:縦に並んだ n行一組の項目別データを組み替える


他のアプリからデータを読み込んだら、こんな形だったってことが時々ある。

いわゆる n行一組のデータ(画像は6行)。これを項目別に組み換える場合、「数式かマクロか」になりがちだけど、クエリでももちろんできる。

よく見かける記事はインデックス列を追加→整数除算列を追加→剰余を求める→列のピボット……というパターン。セル範囲が「データ範囲」と名前定義されているものとしてクエリを作成する。

let
    項目名 = {"氏名","郵便番号","住所","電話番号","FAX番号","メールアドレス"},
    タイプ = {Text.Type,Text.Type,Text.Type,Text.Type,Text.Type,Text.Type},
    ソース = Excel.CurrentWorkbook(){[Name="データ範囲"]}[Content],
    連番列の追加 = Table.AddIndexColumn(ソース, "連番", 0, 1, Int64.Type),
    整数除算列の追加 = Table.AddColumn(連番列の追加, "整数除算", each
        Number.IntegerDivide([連番], 6), Int64.Type
    ),
    剰余を計算 = Table.TransformColumns(整数除算列の追加,
        {"連番", each Text.From(Number.Mod(_, 6)), type text}
    ),
    列のピボット = Table.Pivot(
        剰余を計算, List.Distinct(剰余を計算[連番]), "連番", "Column1"
    ),
    列の削除 = Table.RemoveColumns(列のピボット,{"整数除算"}),
    列名の変更 = Table.RenameColumns(列の削除,
        List.Zip({{"0","1","2","3","4","5"},項目名})
    ),
    型の変更 = Table.TransformColumnTypes(
        列名の変更, List.Zip({項目名,タイプ})
    )
in
    型の変更

ひとまずはこれでできる。

ただどうも不細工な気がする。分割をもうちょっとスマートにできないものか。例えば Table.Splitを使ってやると下記のようにできる。ソース以下の記述を下記に変更。

    テーブル分割 = Table.FromList(
        Table.Split(ソース,6), Splitter.SplitByNothing(), null
    ),
    行列入替 = Table.TransformColumns(
        テーブル分割, {"Column1", Table.Transpose}
    ),
    列の展開 = Table.ExpandTableColumn(行列入替, "Column1", 
        Record.FieldNames(行列入替{0}[Column1]{0}), 項目名
    ),
    型の変更 = Table.TransformColumnTypes(
        列の展開, List.Zip({項目名,タイプ})
    )

こっちのほうが分かりやすいように思う。