データクレンジングが Power Queryの主な役割だと考えるなら、避けて通れないのがデータの組み換え。
他のアプリからデータを読み込んだら、こんな形だったってことが時々ある。
いわゆるｎ行一組のデータ（画像は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",
Table.ColumnNames(行列入替{0}[Column1]), 項目名
),
型の変更 = Table.TransformColumnTypes(
列の展開, List.Zip({項目名,タイプ})
)行列入替してから Table.Comibneで結合する手もあるか。
タイプテーブル = type table [
氏名=text, 郵便番号=text, 住所=text,
電話番号=text, FAX番号=text, メールアドレス=text
],
見出し = Type.TableSchema(タイプテーブル)[Name],
ソース = Excel.CurrentWorkbook(){[Name="データ範囲"]}[Content],
テーブル分割 = List.Transform(Table.Split(ソース,6), Table.Transpose),
テーブル結合 = Table.Combine(テーブル分割),
型の変更 = Table.FromColumns(
Table.ToColumns(テーブル結合), タイプテーブル
)こっちのほうが分かりやすいように思う。
