完全に備忘＆使いどころは謎ですが、縦に積んだテーブルをPowerQueryで横に積みなおした（この時点で何言ってんだというところですが…）ときのお話です。
背景
アンケートを定期的に実施していて、アンケートテーマによって質問数は様々。
それらをひとつのテーブルに積んでいくために以下のような構成をとっています。
- アンケートの実施日
- アンケートのテーマ
- 回答者の氏名
- 質問の順番
- 質問
- 回答
データはAIに生成させたサンプルです。実際のアンケートとの関連はありません。
これ👆を…こう👇したい。
手順
- 「質問」と「回答」を選択して右クリック「列のマージ」、区切り記号を「タブ」にします。
質問と回答がひとつの列にまとまりました。
- 「質問の順番」を選択し、「列のピボット」。値列に先ほど作成した「結合済み」、値の集計関数を「集計しない」とします。
実施日、テーマ、回答者毎に行がまとまりました。
- 以下のステップを追加。「1」の列から順に”質問”と”回答”に分割し、「1_質問」「1_回答」「2_質問」「2_回答」…となるように変形します。
= let
Source = ピボットされた列, // 前ステップ
ExcludeColumns = {"実施日", "テーマ", "回答者"}, // 分割対象にしたくない列を列挙
ColumnsToSplit = List.RemoveItems(Table.ColumnNames(Source), ExcludeColumns), // 分割対象外の列を除く
// 各セルを分割して Record を返す関数（質問/回答）
SplitCellToRecord = (val as any) as record =>
let
txt = try Text.From(val) otherwise null,
parts = if txt <> null then Text.Split(txt, "#(tab)") else null,
cnt = if parts <> null then List.Count(parts) else 0,
q = if cnt > 0 then parts{0} else null, // 先頭＝質問
a = if cnt > 1 then parts{cnt - 1} else null // 末尾＝回答
in
[質問 = q, 回答 = a],
// すべての分割対象列を同じロジックで処理（Record化→展開）
Result =
List.Accumulate(
ColumnsToSplit,
Source,
(state as table, currentColumn as text) =>
let
// 1) 列を Record に変換（質問と回答に分割）
Transformed =
Table.TransformColumns(
state,
{
{ currentColumn, each SplitCellToRecord(_), type record }
}
),
// 2) Record 列を2列に展開（質問/回答）し、元列名は消える
Expanded =
Table.ExpandRecordColumn(
Transformed,
currentColumn,
{"質問", "回答"},
{ currentColumn & "_質問", currentColumn & "_回答" }
)
in
Expanded
)
in
Result
完成です。
回答者に絞って、各テーマでどのような回答をしているかを並べて見られるようになりました。
おわりに
列を区切って分割する、それと同時に元の列名を含んだ列名を動的につけ、かつその対象がいくつあるかわからないというところで苦心しましたが、Copilotさんの力を（かなり）借りて、作ることができました。一発で正解は出なくとも、ゼロから作らなくてよいところが本当にありがたい…。できあがったものから関数や使い方を調べることが多くなったこの頃です。