列名をダイナミックにしたピボット解除を行う

ピボット解除はパワークエリの協力な武器です。縦横/↓→並びを縦縦/↓↓並びに変えてくれます

但し、新たに列を加えた場合には、並び替えの対象列が狂ってしまいます

　上のGIF画像では「評価」という列を加えると、当然、「評価」列は軸として固定されずに並べ替えの対象となってしまいます

本来であれば、下の画像のようにしたいところでした

他の言い方に変えると、列の追加を行ったとしても「日付」の列のみ、並び替えを行って欲しいのです

これにはいい方法があります

ポイント

ピボット解除のM関数

下の画像は「その他の列のピボット解除」を行ったステップの数式バーです

Table.UnpivotOtherColumnsというM関数が使用されており、カテゴリー列*以外の列が並び替えられているのがよく分かります　

逆に言えば「並び替えない列」を上のM関数内でリストにて指定してしまえば、それ以外の列が並び替えの対象となります

エラーを敢えて発生させる

今回は、日付のみを並び替えの対象とします

エラーと絡めて言うと、日付形式にした場合にエラーになるものは並び替えの対象としないようにすればよいのです

更に前述のM関数との絡みで言うと、M関数内の「ピボット解除を行わない列名」を「日付形式にした場合にエラーになる列名」のみにしてしまえばいいのです

並び替えない列名リストの作成

まずは、ピボット解除を行ったクエリを複製します

そして、ソース以外のステップを削除した後にM関数/Table.ColumnNamesを使用して「列名」をリスト化します

そして、テーブル化した後に、以下の画像のようにカスタム列の作成画面で列を複製します

ここから、ポイントで前述した敢えてエラーを発生させる話になります

カスタム列を日付形式に変えます

すると、日付以外はエラーになります

次に、ホームタブで「エラーの保持」を行います

すると、日付以外の列名だけが残ります

ここから２つ作業を行います①カスタム列の削除、②リスト化

リスト化は変換タブの「リストに変換」で行います

リストに変換したら、リスト名を覚えやすいようにつけておきます

今回は列名リストとしました

M関数にリストを組み込み

並び替えない列名のリストができたので、M関数/Table.UnpivotOtherColumnsの中に組み入れます

こうしておけば、列を追加しても日付のみが並び替えされます

＜まとめ＞

今回は、ピボット解除において列を追加した場合でも日付のみが並び替えられるようにしました

ポイントは、並び替えを行わない列名のリストを作成して、ピボット解除を行うM関数の中に組み入れることです

リストを作成する際には、敢えてエラーを発生させるのもポイントです

でも、日付以外の月などの場合にはどうするのか？

その場合には、条件列を使います

上の画像の場合には、月で終わらないものはnullになります

そして、nullだけフィルタで残す、という風にすれば対応できます

にほんブログ村