久しぶりにPower Queryに関することについて書いてみたいと思います。
横に広がるデータを縦に繋げるためにはどうしたら良いだろうか？
というシンプルな質問に、答えられそうで答えられない場合が多いのではないかと思います。以下、分かりやすい例を紹介します。
※ 一番下に、解説したサンプルファイルをダウンロードできるようにしています。
ステップ解説
以下のテーブルが同じシートにあり、これらを1つのテーブルとして結合する場合、どうすれば良いのでしょうか？
これらのデータが異なるシート、もしくはファイルに存在するのであれば問題はすぐに解決できますが、前提として同じシート内に存在し、今後も右側に追加されていくパターンです。
データ処理のプロである方であれば、真っ先に
データの持ち方間違えているよ！
それ、Excelで手動で結合したほうが速いでしょ？
と指摘してくると思います。120%正しいので、今回の記事はこれにて終わり・・・にするのは勿体ないので、あくまで、このような状況からあえてPower Queryを使って処理した場合、どのようになるかを見てみたいと思います。
以下、ソースコードです（※ステップ名は全てスペースなしの英語に独自に変更済）
ステップ別に見ていきます
Source
Excel.CurrentWorkbookで現在のワークブック内のName列からDatasetという名前、かつContent列のセル（Table）からデータを抽出（結果は下図）。
なお、Excel.CurentWorkbook()単体では以下のようになりますが、その後に絞り込みとして、{[Name = "Dataset"]}（レコード）とし、名前がContentの中身を取得しています。
ちなみに、知っている方も多いと思いますが、同じExcel内からデータを取得しようとする場合、データがテーブル書式、もしくは「参照範囲に名前を定義」しておかないと範囲を取得できないことが殆どです（一部、プリント範囲を指定することはできますが、今回の例ではそれらは出現せず）。
デモ用にファイルを増やしたくなかったため、今回は範囲を指定して、Datasetという名前を定義しました。これで、上記のようにデータを取得することができるようになります。
Transposed
行列の入れ替えを行います（下図）。
RemoveBlankRows
こちらは以下のように、非常に難解な数式となっていますが、
RemoveBlankRows = Table.SelectRows(
Transposed,
each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))
)
手動で入れているわけではなく、以下のUIから操作から手軽に実現できます。
このステップは、Transposeした後、空白となっている行（全ての列がnullもしくは""）を全て削除してくれるステップとなります。詳述は割愛しますが、興味ある方は下記ブログが分かりやすく解説していますので、ご参照ください。
FilleDown
下図の通り、「下へフィル」されます。
TransposeBack
元の形にTransposeしなおします。
TableToColumns
ここが今回の”キーポイント”ですが、Table.ToColumnsというM関数を使用し、テーブル状態のデータをリストのリストに変換します。
具体的に何が起こったかというと、
テーブルの状態では12列あった各列のデータをそれぞれリスト化し、更にそれらを1つのリストに格納
させた状態となります。リストとなった状態のListの中身をクリックしてみると、1行目が元のテーブルの1列目の中の値、2行目が元のテーブルの2列目の中の値、3行目が・・・となっているのが分かります。なぜこれを行うかはステップ名「GroupByPeriod」で分かるようになります。
ConvertToTable
リストの状態だと操作しにくいので、テーブルへ変換しなおします（下図）。
すると、下図のように、列名 = Column1というテーブルに変換されています。
ExtractPeriod
ExtractPeriod = Table.AddColumn(ConvertToTable, "Period", each [Column1]{0})
というステップですが、Column1列内の一番最初の項目（Period）を各行から抽出するものとなっています。Power Queryのインデックスは1行目が0として扱われますので、
[列名]{0}
という形で対象セル内の値を取得をします。
これで、3つの異なる期間のそれぞれのPeriodが取得できました。
GroupByPeriod
最後の仕上げがGroupByPeriodステップになります。このステップで実現したいことは下図の通りです。Period列でグループ化し、3行のテーブルにグループ化を行い、Groupedという列の中のTableは元のExcelと同じ形に変換してあげることです。
しかし、普通にGroup化をしただけでは、以下のようになります。
列Periodを右クリック > グループ化すると、②のように、列名と操作という項目が出現します。図の通りにした後、OKをクリックすると、④のように3行だけのグループ化された結果が出現します。
ここで上図を見ますと、Column1の中はListという項目で占められていますが、このListは最初のほうでTable.ToColumnsでリスト化した各列の値が格納されています。そこで、この各列の値を元通りに戻せるのであれば、Grouped列のTable内にて、データを従来と同じ形で縦に持つことができるようになるわけです。図で見たほうが分かりやすいので、イメージは以下の通りになります。
ここからは、少し数式バーの中で、調整が必要になります。まず、Group化した構文を見てみますと、以下のようになっているはずです。
ExtractPeriod =
Table.Group( ExtractPeriod, {"Period"},
{
{"Grouped", each _, type table [Column1=list, Period=text] }
}
)
読み替えると、元のテーブル（前のステップ = ExtractPeriod）のそれぞれの行（each _）をPeriodという列でグループ化し、Groupedという新しい列を作って、Column1のデータ型をlist、Periodという列のデータ型をtextとして設定する、となります。
このままでは、上述した通り、Column1は全てlistの状態となりますので、この構文を以下のように書き換えます。
ExtractPeriod =
Table.Group( ExtractPeriod, {"Period"},
{
{"Grouped", each Table.FromColumns( _[Column1] ) }
}
)
紫色の文字が書き換えた部分ですが、下図のようになります。先ほどはGroupedの中に、Column1とPeriodの2列が存在していましたが、_[Column1]とすることで、1列だけを選択することができます。選択した状態から、Table.ToColumnsの逆を実現するTable.FromColumns関数で括ってあげることで、listとなっていた列を再び展開し、各Period別に元に戻すことができるわけです。
このまま展開した場合、最初の行（2019年4月～2020年3月まで）という文字列が邪魔になるので、これを消しつつ、その下の[順位]、[社名]の行のヘッダーを展開前に昇格したほうが良いので、上記M式を以下のように変更します。
GroupByPeriod = Table.Group(
ExtractPeriod,
{"Period"},
{
{"Grouped",
each Table.PromoteHeaders( Table.Skip( Table.FromColumns( _[Column1]), 1 ) ) }
}
)
Table.Skipを使って、最初の行をSkipした後、Table.PromoteHeadersでヘッダーを昇格させます。Excelのネスト関数に慣れている方であれば、それほど難しくはないのではと思います。これにより、このGroupByPeriodというステップは、下図のような書式で結果を返してくれます。
Expanded
最後にGrouped列を展開すれば、横並びだったテーブル書式のデータを全て結合することができます。
前職でこのような形のデータを扱う必要があったため、どうすれば良いのかを考えた結果、こうした変換手法となってしまいました。M言語に慣れるという意味において、Table.ToColumnsやTable.ToRowsは意外に重要だったりしますので、覚えておくと便利です。
おまけ
今回の例をうまく使えば、例えば以下のような横並びの列を1つの列に結合することも簡単にできます。
ソースコードだけ載せておきますので、興味ある方は下記ダウンロードと合わせて確認してみてください。
ファイルダウンロード
