列のピボットを解除する

Power Query では、列を属性と値のペアに変換して、列を行にすることができます。

左側のテーブルに、空白の列と複数の行のある左側のテーブルと、列ヘッダーとして Attributes 値 (A1、A2、A3) を示す図。 この表では、A1 列に V1、V4、V7 の値が含まれています。 A2 列には、V2、V5、V8 の値が含まれています。 A3 列には、V3、V6、V9 の値が含まれています。 列がピボット解除された図の右側のテーブルには、空白の列と複数の行があり、Attributes 列には A1、A2、A3 が 3 回繰り返された 9 つの行と、Values 列には値 V1 から V9 が含まれています。

たとえば、次のようなテーブルがあるとします。このテーブルでは、country の行と日付列によって値のマトリックスが作成されますが、データをスケーラブルな方法で分析するのは困難です。

Text データ型に設定されている Country 列と、整数データ型として設定された 2023 年 6 月 1 日、2023 年 7 月 1 日、2023 年 8 月 11 日の日付の 3 つの列を含むテーブルのスクリーンショット。 Country 列には、行 1 に USA、行 2 に Canada、行 3 に Panama が含まれています。

代わりに、次の図に示すように、このテーブルをピボット解除された列を持つテーブルに変換できます。 変換されたテーブルでは、日付を属性として使用してフィルター処理する方が簡単です。

Text データ型として設定された Country 列と Attribute 列、整数データ型として設定された Value 列を含むテーブルのスクリーンショット。 Country 列には、最初の 3 行に USA、次の 3 行に Canada、最後の 3 行に Panama が含まれています。 Attribute 列には、行 1、4、7 に 2023 年 6 月 1 日、行 2、5、8 に 2023 年 7 月 1 日、行 3、6、9 に 2023 年 8 月 11 日が含まれています。

この変換で重要なことは、テーブル内で一連の日付がすべて 1 つの列に含まれていることです。 各日付と国のそれぞれの値は、別の列にあり、属性と値のペアが効果的に作成されています。

Power Query では、常に次の 2 つの列を使用して属性と値のペアが作成されます。

• 属性: ピボット解除された列見出しの名前。
• 値: ピボット解除された各列見出しの下にあった値。

ユーザー インターフェイスの複数の場所で、[列のピボット解除] を見つけることができます。 ピボット解除する列で右クリックするか、リボンの [変換] タブからコマンドを選択できます。

テーブルの列をピボット解除するには、次の 3 つの方法があります。

• 列のピボットを解除する
• その他の列のピボットを解除する
• 選択した列のみのピボットを解除する

列のピボットを解除する

前述のシナリオでは、最初にピボット解除する列を選択する必要があります。 必要な数の列を選択するときは、Ctrl キーを押しながら選択できます。 このシナリオでは、Country という列を除くすべての列を選択します。 列を選択したら、選択した列のいずれかを右クリックして、[列のピボット解除] を選択します。

2023 年 6 月 1 日、2023 年 7 月 1 日、2023 年 8 月 1 日 の列が選択されているテーブルで、ショートカット メニューで [列のピボット解除] コマンドが選択されているスクリーンショット。

その操作では、次の図に示されているような結果が生成されます。

Text データ型として設定された Country 列と Attribute 列、整数データ型として設定された Value 列を含むテーブルのスクリーンショット。 Country 列には、最初の 3 行に USA、次の 3 行に Canada、最後の 3 行に Panama が含まれています。 Attribute 列には、行 1、4、7 に 2023 年 6 月 1 日、行 2、5、8 に 2023 年 7 月 1 日、行 3、6、9 に 2023 年 8 月 1 日 が含まれています。 また、[クエリの設定] ペインで [列のピボット解除] エントリが強調表示され、M 言語のコードが数式バーに表示されています。

特別な注意事項

前の手順でクエリを作成した後に、初期テーブルが次のスクリーンショットのように更新されたとします。

元の Country、2023 年 6 月 1 日、2023 年 7 月 1 日、および 2023 年 8 月の日付列が同じで、2023 年 9 月 1 日の日付列が追加されているテーブルのスクリーンショット。 Country 列には、USA、Canada、Panama の値が引き続き含まれていますが、行 4 に UK が追加され、行 5 に Mexico が追加されています。

日付 2023 年 9 月 1 日 (9/1/2023) の新しい列と、英国とメキシコの国/地域の 2 つの新しい行を追加していることに注目してください。

クエリを最新の情報に更新すると、更新された列に対して操作が実行されますが、元々選択されていない列 (この例では Country) には影響しません。 これは、ソース テーブルに追加した新しい列もピボット解除されることを意味します。

次の図は、新しい更新されたソース テーブルを最新の情報に更新した後のクエリの状態を示しています。

Country、Attribute、Value 列を持つテーブルのスクリーンショット。 Country 列の最初の 4 行には USA、2 番目の 4 行には Canada、3 番目の 4 行には Panama、4 番目の 4 行には UK、5 番目の 4 行には Mexico が含まれています。 Attribute 列には、最初の 4 行に 2023 年 6 月 1 日、2023 年 7 月 1 日、2023 年 8 月が含まれており、それが各国に対して繰り返されています。

その他の列のピボット解除

ピボット解除しない列を選択して、テーブル内の残りの列をピボット解除することもできます。 この操作では、[その他の列のピボット解除] が使用されます。

その操作は、列のピボット解除の結果とまったく同じ結果となります。

Text データ型として設定された Country 列と Attribute 列、整数データ型として設定された Value 列を含むテーブルのスクリーンショット。 Country 列には、最初の 3 行に USA、次の 3 行に Canada、最後の 3 行に Panama が含まれています。 Attribute 列には、行 1、4、7 に 2023 年 6 月 1 日、行 2、5、8 に 2023 年 7 月 1 日、行 3、6、9 に 2023 年 8 月 11 日が含まれています。

Note

列の数が不明なクエリでは、この変換が重要になります。 この操作では、選択した列を除く、テーブルのすべての列がピボット解除されます。 これは、更新時にシナリオのデータ ソースに新しい日付列が追加されていた場合に、それらが選択されてピボット解除されるため、理想的なソリューションです。

特別な注意事項

列のピボット解除操作と同様に、クエリが更新されて、データ ソースからより多くのデータが選択された場合、以前に選択された列を除くすべての列がピボット解除されます。

このプロセスを説明するために、次の図のような新しいテーブルがあるとします。

Country、2023 年 6 月 1 日、2023 年 7 月 1 日、2023 年 8 月 1 日、2023 年 9 月 1 日の列を含み、すべての列がテキスト データ型に設定されているテーブルのスクリーンショット。 Country 列には、上から下に USA、Canada、Panama、UK、Mexico が含まれています。

Country 列を選択して、[その他の列のピボット解除] を選択すると、次の結果が生成されます。

Country、Attribute、Value 列を持つテーブルのスクリーンショット。 Country 列と Attribute 列は、Text データ型に設定されています。 Value 列は、整数データ型に設定されています。 Country 列の最初の 4 行には USA、2 番目の 4 行には Canada、3 番目の 4 行には Panama、4 番目の 4 行には UK、5 番目の 4 行には Mexico が含まれています。 Attribute 列には、最初の 4 行に 2023 年 6 月 1 日、2023 年 7 月 1 日、2023 年 8 月 1 日、2023 年 9 月 1 日 が含まれており、それが各国に対して繰り返されています。

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

この最後のオプションの目的は、テーブルの特定の列のみをピボット解除することです。 このオプションは、データ ソースの不明な数の列を処理し、選択した列のみをピボット解除するシナリオで重要になります。

この操作を実行するには、ピボット解除する列を選択します。この例では、Country 列を除くすべての列です。 次に、選択した列のいずれかを右クリックして、[選択した列のみをピボット解除] を選択します。

この操作では前の例と同じ出力が表示されることに注意してください。

Text データ型として設定された Country 列と Attribute 列、整数データ型として設定された Value 列を含むテーブルのスクリーンショット。 Country 列には、最初の 3 行に USA、次の 3 行に Canada、最後の 3 行に Panama が含まれています。 Attribute 列には、行 1、4、7 に 2023 年 6 月 1 日、行 2、5、8 に 2023 年 7 月 1 日、行 3、6、9 に 2023 年 8 月 1 日 が含まれています。

特別な注意事項

更新後に、新しい列 (9/1/2020) と新しい行 (英国 とメキシコ) を持つようにソース テーブルが変更された場合、クエリの出力は前の例とは異なるものになります。 更新後に、ソース テーブルが次の図のようなテーブルに変更されたとします。

クエリの出力は次の図のようになります。

このように表示されるのは、ピボット解除操作が 6/1/2020、7/1/2020、8/1/2020 列にのみ適用され、ヘッダー 9/1/2020 を持つ列は変更されなかったためです。