Excel PowerQuery 特定データより上のレコードだけにフィルタリングする

Microsoft Excel

スポンサーリンク

はじめに

PowerQueryの下図のようなデータで、名前が「自分」というデータが9番目にあるが、その上の1~9番目にあるデータのみにフィルタリングしたいという場合、どうするか考えてみる。

実務での利用ケースとしては、基準となる部門より実績が低いor高い部門に絞り込むとかいう場合かなと。

この「自分」のデータは今回は9番目だが、何番目になるかは事前に分からないものとする。

意外とこれ、「数値が○○より小さい」とか具体的な条件を指定できないので、やり方が思い付かないと若干難しいかと思う。

解法

ではまず、ちょっと手間は掛かるけどマウス操作だけでやれる方法から。

リボン「列の追加」→「条件列」としてみよう。

次の条件設定画面で、「名前」列が値「自分」に等しい時に、「A」とかの値を返すようにする。

「それ以外の場合」欄は、空白にするか「null」と入れる。

そして「OK」を押す。

Excel PowerQuery 計算式や条件を入れたオリジナルの項目列を追加する

名前が「自分」の行だけ「A」となるカスタム列が追加されたが、それ以外はnullとなるのがポイント。

ここで

リボン「変換」タブ→フィル→上

とする。この「フィル」は通常、「下」を選んで下にデータを埋めるのに使うから、この「上」を選ぶのが少し思い付きにくいかと思う。

Excel PowerQuery データの空白を埋める

これで「自分」から上のレコードにだけ「A」がセットされた。

後は、「A」のデータにフィルタリングするだけだ。

これで、「自分」から上のレコードだけになった。

別解(関数を直接書き込む)

次は、処理ステップ数を増やさなくて済みすぐ終わるが、数式欄に関数を直接書き込まないといけなくて、ちょっと知識が必要な方法。

まず、その関数を書き込む1個前のステップ名を「前のステップ」としておく。

そして数式欄に

= Table.RemoveLastN(前のステップ,each [名前] <> “自分”)

と書き込めば終了だ。

なんとなく字面を見れば分かるだろうが、名前が「自分」と等しくない、Last=下部の方のレコードを削除するというものだ。

ここで出てきた「Table.RemoveLastN」というのは、通常の操作でいくと

リボン「ホーム」タブ→行の削除→下位の行の削除

とするものに該当する。

普通ここでは「上位の行の削除」を使うことばかりだと思うけどね。

PowerQuery エディターの基礎

そして、下位の10行を削除すると指定したなら、下図のように

= Table.RemoveLastN(前のステップ,10)

と表示される。

この最後の「10」を「each [名前] <> “自分”」という条件指定に変えたわけだ。

eachというのは、私も使い慣れないが、要は各レコード行に対して判定を掛けてくるというようなことだ。

スポンサーリンク