スポンサーリンク
はじめに：今回の課題
下の図1の表では、C列以降の列には数字を入れるけど、何年目まで列が続くかは分からず流動的とする。
そして、3行目・5行目は横方向の数字が全て0だけど、そういう無駄な行は全部削除して出力する処理を、PowerQueryで組みたいと思う。
その場合、これまでにも当ブログで取り上げた手順だと、PowerQueryの「列のピボット解除」「列のピボット」機能を両方使って変換する手順(詳細は後述)が良いと思うけど、そうすると下図のように、元々0だったセルが空欄になってしまう。
今回は、図1のような数値カラムが流動的な表について、数値が全て0の行は削除して、なおかつ元々0だったセルが空欄にならず0を埋める手順を取り上げたい。
PowerQueryでExcelファイルを読み込む
ファイルの読み込み・変換
ではまず、基本の一歩として、PowerQueryで元データのExcelファイルを開く。
リボン「データ」タブ
→「データの取得」
→「ファイルから」
→「ブックから」
とし、元データのExcelファイルを開こう。
次の画面で「データの変換」ボタンを押す。
エディタの設定
PowerQueryのエディタ画面になるけど、ぜひ次の設定を済ませておこう。
エディタのリボン「ファイル」タブ→「オプションと設定」→「クエリのオプション」と進む。
「データの読み込み」→「型の検出」メニューで、「非構造化ソースの列の型とヘッダーを検出しない」にチェックを入れておく。
ヘッダ設定
1行目(見出し)が「Column1」などとなっていると思うが、
リボン「ホーム」タブ
→「1行目をヘッダーとして使用」
で、ヘッダを正しく設定する。
ピボット解除～リスト作成
ピボット解除
今回の例では、項目「資産№」「資産名」が固定で、それ以外の数値項目が可変なのだけど、固定項目「資産№」「資産名」の列を選んだ上で
リボン「変換」タブ
→その他の列のピボット解除
とする。
すると、横軸にあった数値項目が「属性」という縦項目に変換される。
グループ化
この「属性」項目を選択した状態で、
リボン「ホーム」タブ
→グループ化
とする。
次の画面で、名前はデフォルトの「カウント」のままで良いので、「操作」からは「すべての行」を選び「OK」を押す。
インデックス追加
今後、色々と処理をしていくと、この「属性」項目について並び順が狂うことがあり得る。
それを防ぐためにインデックス番号を付けるので、リボン「列の追加」→「インデックス列」としよう。
開始番号は0でも1でも良い。
インデックスが追加されたら、グループ化により色々なカラムを畳み込んだ項目「カウント」の右の□ボタンを押す。
項目「属性」は既に表の中にあるので、それのチェックは外し、それ以外の項目には全てチェックを入れて「OK」を押す。
グループ化で隠れていた項目が展開される。
ステップの名称が「展開されたカウント」などとなっていると思うが、後で再利用する箇所なので、ステップ名をクリックしてF2キーを押し「展開」と名称変更しておく。
リストに変換
「属性」列を選択した状態で、リボン「変換」タブ→「リストに変換」とする。
すると、項目「属性」が、「リスト」というものに変形される。私も「リスト」の扱いは全然わかってないですけどね。
この「リスト」に変形すると、その後の操作はかなり制限される。
ひとまず「重複の削除」をしておこう。
すると、ステップの名前が「削除された重複」となってるはず。
これも後で再利用する箇所で、もっと分かりやすくしときたいので、ステップ名をクリックしてF2キーを押し、「属性リスト」と名前を変更することにする。
リストへの変換後
前の処理に時計を戻す
名前を変更した「属性リスト」を右クリックして「後にステップの挿入」としよう。
リストってそもそも、こうでもしないと後に処理を続けられない奇妙なもので。
「カスタム1」とかいうステップが追加されたと思うが、このステップ名を「展開時に戻る」とでも変更しよう。
で、ちょっと前に「展開」というステップを作ったけど、これを数式欄に入れる。
これで、リストを作った事実はそのままに、ステップ「展開」の時点に時計を戻せたようなことになるというか。
不要データの削除
列「インデックス」は、列「属性」の並び順を制御するためのものだが、もう不要になったので削除しておく。
では、元々の目的として、数値が全て0という行は削除したいのだった。
「値」列についてオートフィルタで、0を外すフィルタリングをして「OK」を押そう。
列のピボット
大元のデータに合わせて、数値項目を横軸に置いた形式に戻すことにする。
数値項目「属性」の列を選択した状態で、リボン「変換」タブ→「列のピボット」を押そう。
先程は横軸を縦軸にするため「列のピボット解除」をしたのだけど、それと逆の操作だ。
次の画面で、値列というやつについてそのまま「値」を選んで「OK」を押す。
すると、「○年目」というのが横軸にきて、全て0の行が削除されてくれはするのだが、元々0だったところにnullというのが入ってしまっている。
縦軸だったものを強引に横軸にした結果、こういうことになったわけだ。
これを、nullではなく0を埋めたいというのが今回のそもそもの課題だ。
リストを活用して置換する
では、問題となっているnull値が入っている列ならどこでも良いので、その列を選択して
リボン「ホーム」タブ
→値の置換
としよう。
次の画面で、「検索する値」には「null」と手入力し、「置換後」には0を入力して「OK」を押す。
数式欄を見ると
= Table.ReplaceValue(ピボットされた列,null,0,Replacer.ReplaceValue, {“5年目”} )
というようになっているはずで、 {” “} で囲まれた項目を対象に置換したということだ。
ではこの項目を、先ほど数値項目についてリストを作ったとき、ステップ名称とした「属性リスト」に書き換えよう。
変更後の式は
= Table.ReplaceValue(ピボットされた列,null,0,Replacer.ReplaceValue,属性リスト)
となり、全ての数値項目についてnull値は0に置換されたはずだ。
つまり、リスト「属性リスト」に含まれている数値項目の全てが置換対象になったということだ。
「閉じて読み込む」ボタンを押して、Excelシートに結果出力しよう。
では、大元のデータの方に、元々なかった「6年目」の列を追加してみる。
ついでに、3行目には0でないデータも追加し保存する。
PowerQueryで出力した表の方を、Ctrl + Alt + F5 キーで更新したら、6年目も追加され、0も埋められた状態で更新されるはずだ。
今回厄介だったのは、数値項目がどこまで続くのか流動的で可変だということで、PowerQueryではそういう場合は大体、コードを自分で書き足さないといけない。
もう少し分かりやすくなってほしいものだが。
スポンサーリンク
