PowerQuery 列のピボットの使い方｜解除・複数列・null処理

Power Query（パワークエリ）でテーブルを縦持ちから横持ちに（横持ちから縦持ちに）変更する方法を紹介します。それぞれ列のピボットとピボット解除を使って行っています。

テーブルの縦持ちとは？横持ちとは？

まずはテーブルの「縦持ち」と「横持ち」とはそれぞれ下図のようなイメージになります。属性データと値データの位置を変えることで変換することができます。

縦持ち・横持ちの特徴

縦持ちと横持ち（クロス集計表）を比較した場合の特徴とメリット・デメリットは以下の通りです。

Sponsored link

列のピボット（縦を横に変更）

縦持ちのテーブルを横持ちのテーブル（クロス集計表）に変換する方法を紹介します。このテーブル変換には「列のピボット」コマンドを使用します。

【手順】

1. 横持ちテーブルの列名として展開したい列を選択する
2. 「列のピボット」をクリックして実行する
3. 設定画面で、横持ちテーブルの値データとして展開したい列を選択してOKする。

データ準備

列のピボットの例として下図のデータを横持ちデータ（クロス集計表）に変換します。

列名として展開したい列データを選択する

横持ちデータに変換したときに列名として展開したい列データを選択します。ここでは「日付」を選択します。

複数の列を選択した場合は最初に選択した列データが列名として展開されます

列のピボットを実行する

1. 変換タブの「列のピボット」をクリックする。
2. 設定画面で、展開したい値の列を選択してOKをクリックする。ここでは「売上」を選択します。

詳細設定オプション

詳細設定では値の集計の有無と種類を選べます。重複するデータがある場合などはこちらも用いて集計して上でクロス集計表にすることもできます。

縦持ちから横持ちへのテーブル変換完了

以上でテーブルの縦持ち→横持ち変換は完了です。

Sponsored link

列のピボット解除（横を縦に変更）

横持ちのテーブルを縦持ちのテーブルに変換する方法を紹介します。

横持ちから縦持ちへの変換には「列のピボット解除」コマンドを使用します。列のピボット解除には以下の3つのオプションがあり、事前操作として選択する列に違いがあります。ここでは上の2つについて詳しく解説します。

「列のピボット」と「選択した列のみをピボット解除」は選択する列は同じですが、作成されるM言語が異なります。これらの違いについては後で解説します。

「列のピボット解除」の使い方

【手順】

1. ピボット解除したい列を選択する
2. 「列のピボット解除」をクリックして実行する

データ準備

列のピボット解除の例として下図の売上データを縦持ちデータに変換します。

ピボット解除したい列を選択する

ピボット解除したい列をCtrlキーを押しながらクリックして全て選択する。ここでは売上の数値データをピボット解除したいので「2023/7/1～2023/9/1」の列を選択します。

列のピボット解除を実行する

変換タブの列のピボット解除をクリックして実行します。

横持ちから縦持ちへのテーブル変換完了

以上でテーブルの横持ちから縦持ち変換は完了です。元々の列名だった日付データと売上の値データが縦に並んだテーブルとなっています。

「その他の列のピボット解除」の使い方

【手順】

1. ピボット解除したい列以外の列を選択する
2. 「その他の列のピボット解除」をクリックして実行する

データ準備

その他の列のピボット解除の例として下図の売上データを縦持ちデータに変換します。

ピボット解除したい列以外の列を選択する

ピボット解除したい列以外の列をCtrlキーを押しながらクリックして全て選択する。ここでは売上の数値データがある「2023/7/1～2023/9/1」の列をピボット解除したいので、それ以外の地域列を選択します。

その他の列のピボット解除を実行する

変換タブのその他の列のピボット解除をクリックして実行します。

横持ちから縦持ちへのテーブル変換完了

以上でテーブルの横持ちから縦持ち変換は完了です。元々の列名だった日付データと売上の値データが縦に並んだテーブルとなっています。

「その他の列のピボット解除」を使うと元の横持ちテーブルに新しい列（上記の例では2023/10/1や2023/11/1など）が追加された場合でも、選択範囲を変える必要がないためデータ更新の度にクエリを修正する必要はありません。データ更新時もエラーが発生しにくいためオススメです。

「列のピボット解除」と「選択した列のみをピボット解除」の違い

「列のピボット解除」と「選択した列のみをピボット解除」にテーブル変換の操作や結果には違いはありません。しかし、それぞれのコマンドを使用した際に作成されるM言語の式が異なります。

例として下図の選択状態でコマンドを実行した結果が下記です。

「列のピボット解除」の結果

Table.UnpivotOtherColumns(変更された型, {"地域"}, "属性", "値")

「選択した列のみをピボット解除」の結果

Table.Unpivot(変更された型, {"2023/7/1", "2023/8/1", "2023/9/1"}, "属性", "値")

結果としては、「列のピボット解除」の場合はTable.UnpivototherColumns関数が用いられ選択した以外の列を引数として使用しており、「選択した列のみをピボット解除」の場合はTable.Unpivot関数が用いられ選択した列が引数として使用されます。

これよりデータ更新時に列が追加される（変更される）場合を考えるとピボットする列以外の列名を引数として持つ「列のピボット」の方がエラーも発生しにくく使いやすいかと思います

Sponsored link

列のピボット後（列のピボット解除前）のnull処理

列のピボットや列のピボット解除を実行する場合、下記のような状況でnullを処理したい場合があります。

• 列のピボットにより新しい列が作成され、nullが多く発生する。
• 列のピボット解除を実行する前の横持ちデータに多くのnullが含まれる。

その場合は「値の置換」を使用して下記の手順で対応できますが、元データが更新されたことにより出来た新しい列のNULLが置換されないといった問題が発生します（または、データ更新時に列名が変わりエラーが発生してしまう）。

1. NULLを置換したい列を複数選択する
2. 「値の置換」をクリックする
3. 置換設定を行いOKをクリックする

対応方法

値の置換を行うM言語の式に固有の列名が含まれていることがエラー発生などの上記の問題点の原因となっているため、列名を含まない置換の式に変更します。

下記のように列名が指定されている式の部分{“2023/07/01,”2023/08/01”}を Table.ColumnNames(テーブル名) に置き換えます。これによりテーブルの列全てを指定するように変更でき、新しい列が追加されても処理できるようになります。

Table.ReplaceValue(ピボットされた列,null,0,Replacer.ReplaceValue,Table.ColumnNames(ピボットされた列))

また、横持ちデータにおいて結合セルによるnullが発生している場合は以下の記事を参考に処理してみてください。

DONOLOG

Power Query 空白セルを同じ値で埋める（行方向・列方向） | DONOLOG Power Query（パワークエリ）で結合セルが解除されたときにできる空白を埋める方法を紹介します。ここでは「フィル」コマンドを用いて行方向に結合されたセルの処理方法を…

Sponsored link

まとめ

今回はテーブルの向きの変換について解説しました。縦持ちから横持ちに変更する場合は「列のピボット」を使用して、横持ちから縦持ちに変更する場合は「列のピボットの解除」を使用します。

基本的な操作手順としては以下の2ステップです。

1. 列をクリックして選択する。
2. 「列のピボット」または「列のピボットの解除」を実行する。

列のピボット解除には複数のオプションがありますが、選択操作の簡易さやデータ更新への対応のしやすさから、個人的には「その他の列のピボット解除」を使うのがオススメです。