パワークエリでとても便利な「列のピボット解除」。前回の基礎編に続いて、今回はやっかいなケースの扱いについて投稿します。
やっかいなケース！？
そう、しれっと更新できなくなっちゃうケース
「列のピボット解除」は前後のふるまいに注意しておかないと、正しくデータ更新されないことがあります。
得意気に横持ちから縦持ちへデータ変換したまではよかったが、ある日突然更新エラーが発生！ってことね
これは、パワークエリがステップ毎に出力するコードに原因があります。
パワークエリを使えば、複雑な関数を使わずにGUI操作のみで強力な整形処理を行うことができます。しかし、各ステップで生成されるM言語のソースコードには、エクセル・セル内の文字列が直接書き込まれることがあります。
更新漏れやエラーなく、安定的に更新処理を行うには、コード記述部分にも目を向ける必要があります。
「列のピボット解除」で更新できないケースと解消方法
いくつかのエラーサンプルと解決する方法をまとめます。本記事は、前回の記事をもとにしています。
「列のピボット解除」前の処理
まずは、「列のピボット解除」前に実施した整形処理が影響するケースです。
ケース1 自動型変換によるハードコーディング
パワークエリでデータを読み込むと 自動で型変換 されていることがほとんどです。
この自動での型変換は、列のピボット解除以外にもやっかいなケースに遭遇しやすいです。
▲ サンプルではデータを読み込んだ直後に、商品名は「テキスト型」、各月は「数値型（10進数）」として変換されています。
= Table.TransformColumnTypes(ソース,{{"商品名", type text}, {"1月", type number}, {"2月", type number}, {"3月", type number}, {"4月", type number}, {"5月", type number}})▲ ご覧の通り、列名称がソースコードに直接書き込まれています（ハードコーディング）。
▲ クエリ処理を一旦完了して、その後にデータを追加して後継ステップを見てみると、追加された月ヘッダーは、「数値型（10進数）」ではない、明示的な型指定がない状態になっています。
= Table.TransformColumnTypes(ソース,{{"商品名", type text}, {"1月", type number}, {"2月", type number}, {"3月", type number}, {"4月", type number}, {"5月", type number}})▲ ステップのコードを見ても、6月に対する型変換の定義がありません。
今回のデータでは、これでも更新できています。ただ、PowerBIサービスなどで業務アプリ化しているケースでは、バグとなりえるので不適切な箇所は、前もって取り除いておいたほうがよいでしょう。
解消方法
解消方法は、いたってシンプルです。最初の自動変換ではなく、最後に自分で型変換すればよいのです。
▲ 自動で適用された「変更された型」ステップを削除します。
▲ ポップアップが出ますが、そのまま「削除」で進めます。
▲ ステップの最後に移動し、各列の左上から適切な型を変更します。
- 「商品名」「月」をテキスト型へ
- 「売上」を数値型へ
= Table.TransformColumnTypes(#"名前が変更された列 ",{{"商品名", type text}, {"売上", type number}})▲ 最後のステップで型変換を行ったことにより、データ追加でも影響が出ないコードに変わりました。（「月」が追加されて変動しても、影響しない列を型変換している）
ケース2 値置換によるハードコーディング(null値)
「列のピボット解除」前に何かしらの値を置換している場合にもハードコードされる箇所が生成されています。以下のサンプルでは、null値を明示的なゼロに変換しています。
▲ 上記は置換処理前。
▲ 置換処理は、リボンメニューの変換タブから行うことができます。
▲ 上記は置換処理後。
この置換そのものは理にかなっています。
「列のピボット解除」を行うと、null値が含まれた行は処理後のデータから取り除かれます。これを防ぐために、null値ではなく、明示的にゼロへ置換し、ゼロというデータがあることを示すことがあります。
= Table.ReplaceValue(フィルターされた行,null,0,Replacer.ReplaceValue,{"商品名", "1月", "2月", "3月", "4月", "5月", "6月"})▲ 置換によって生成されたコードを見ると一目瞭然ですが、各列名称がソースコードに直接書き込まれています。
解消方法
解消方法は、少しだけM言語のステップコードをいじります。
= Table.ReplaceValue(フィルターされた行,null,0,Replacer.ReplaceValue,{"商品名", "1月", "2月", "3月", "4月", "5月", "6月"})▲ 上記は変更前のコード。月名の箇所をダイナミックに取得します。
= Table.ReplaceValue(フィルターされた行,null,0,Replacer.ReplaceValue,Table.ColumnNames(フィルターされた行))▲ 上記は変更後のコード。
「ColumnNames」関数で、表内の列名称をダイナミックに取得しています。「ColumnNames」関数の引数にはテーブルを入れることになりますが、ここでは直前のステップ名称を入れています。
これで、月の増減があってもダイナミックにデータ更新できるようになります。
「列のピボット解除」実施時
次に、「列のピボット解除」の操作自体が影響するケースです。
ケース3 「選択した列のみピボット解除」を使ったことによるハードコーディング
前回の記事で、ピボット解除方法には次の３通りあることを述べました。
- 列のピボット解除
- ほかの列をピボット解除
- 選択した列のみピボット解除
▲ サンプルデータの「月」を選択して、「選択した列のみピボット解除」するようなケースでは、以下のようにステップコードが出力されます。
= Table.Unpivot(フィルターされた行, {"1月", "2月", "3月", "4月", "5月"}, "属性", "値")意図して使うケースであればよいのですが、意図せずやっていると新しいデータ（月）が入ってきた時に、支障が出ることがあります。
解消方法
解消方法としては、ピボット操作の起点を「ピボット解除を行いたい列以外」を選択した状態で、「ほかの列をピボット解除」を使うことで解消されます。
▲ 操作の起点を「ピボット解除を行いたい列以外」、つまり「商品名」を選択した状態で、「ほかの列をピボット解除」します。
こちらの操作では、ステップコードは以下のように出力されます。
= Table.UnpivotOtherColumns(フィルターされた行, {"商品名"}, "属性", "値")▲ 上記のコードでは、月の増減によってエラーになることはなくなります。
まとめ
パワークエリ「列のピボット解除」の注意点について投稿しました。
気づいた範囲での注意点ですので、また新しく見かけたときはこちらの記事に追記します。
紹介する書籍の中で翔泳社出版のものは、紙媒体やKindleのほかに公式サイト「SEshop」でPDF書籍が購入できます。オフィスで画面に移しながら手を動かせるので使い勝手抜群！！
全国送料無料！IT書、ビジネス書、資格書が豊富なSEshop
「SEShop」だとクーポン利用可、PDF版での入手となり扱いやすいです。現在、10%オフとなるクーポンが配布されていました。アマゾンとかで買うよりもお得です。
