テーブルに縦方向に入力したデータをグループ分けして横に並べたい時。
Excelを使っている人の多くは、この逆パターンで悩むことが多いんだけど、そっちは Power Queryだと「列のピボット解除」で一発解決するので、敢えて逆パターンを取り上げる。
横並びの列が固定の場合は、グループ化の際に Text.Combineで結合しておいて「列の分割」で分ける方法もあるにはある。ただこれだと列数が増えた時に対応が利かないのと、データがテキストデータでないとデータ型が変わってしまって面倒なことになる。
やはり基本は「行のグループ化」→「グループ列にインデックス列を追加」→「展開して列のピボット」かと思う。なるべくマウス操作だけでできる方法を書いてみる。
ひとまずテーブルを読み込んでクエリを作成し、Power Query エディタを開いたところからで。
品番列の見出しを右クリック［グループ化］新しい列名：グループ 操作：すべての行 →［OK］
作成されたグループ列内のテーブルにインデックス列を追加する。ここだけは手作業が必要。
数式バーを広げて「each _」となっているところを 「each Table.AddIndexColumn(_, "連番", 1, 1)」に変更 「type table [……]」の中に「, 連番=number」を追加
これをやっておくと新規列を追加して、後で削除する手間が減るのでお勧め。
作成されたグループ列の見出し右にあるボタンをクリック「店舗名」「連番」にだけチェックを入れ ［元の列名をプレフィックスとして使用します］のチェックを外す →［OK］
先の操作で「, 連番=number」を追加しておかないと、展開する時、見出しリストの中に「連番」が表示されないので注意。
ここまででこの状態。この後、連番列は見出しとして使うので「店舗1」「店舗2」……となるように加工する。
連番列を選択した状態で ［編集］→［書式］→［プレフィックスの追加］ 値に「店舗」と入力して［OK］
前に文字列を追加した結果。後ろに付けたい場合は「サフィックスの追加」で。ここまできたら後は列のピボットを実行するだけ。
値列：店舗名 値の集計関数：集計しない →［OK］
これで完成。後は「閉じて次へ読み込む」で好きなところに出力すればＯＫ。
尚、詳細エディタで直接記述するならこんな感じ。
列数固定の場合
let
列数 = 3,
ブック = Excel.CurrentWorkbook(),
ソース = ブック{[Name="テーブル1"]}[Content],
型の変更 = Table.TransformColumnTypes(
ソース, {{"品番", type text}, {"店舗名", type text}}
),
行のグループ化 = Table.Group(
型の変更, {"品番"},
{"グループ", each Text.Combine([店舗名], ","), type text}
),
列の分割 = Table.SplitColumn(
行のグループ化, "グループ",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
List.Transform({1..列数}, each Number.ToText(_, "店舗0"))
)
in
列の分割
列数不定の場合
let
ブック = Excel.CurrentWorkbook(),
ソース = ブック{[Name="テーブル1"]}[Content],
型の変更 = Table.TransformColumnTypes(
ソース, {{"品番", type text}, {"店舗名", type text}}
),
行のグループ化 = Table.Group(
型の変更, {"品番"}, {"グループ",
each Table.AddIndexColumn(_, "連番", 1, 1),
type table [連番=number, 店舗名=text]}
),
グループ列の展開 = Table.ExpandTableColumn(
行のグループ化, "グループ", {"連番", "店舗名"}
),
連番列の編集 = Table.TransformColumns(
グループ列の展開,
{"連番", each Number.ToText(_, "店舗0"), type text}
),
列のピボット = Table.Pivot(
連番列の編集,
List.Distinct(連番列の編集[連番]), "連番", "店舗名"
)
in
列のピボット
