はじめに
Power BI Desktopの中で、2箇所ほどExcelと同じ操作で列に対しフィルターをかけることができる。1箇所目はPower Query内で、もう1箇所はデータビュー内である。
3者のそれぞれのフィルタリングした後の挙動にどういう違いがあるのか？
Power BI DesktopとExcelのフィルタリングの違い
Power Query：データが完全に削除される
フィルタリングで除外されたデータはテーブルから消え、以降の計算に含まれなくなる
Power BI Desktopのデータビュー：非表示になるだけ
フィルタリングで除外されたデータはデータビュー上で非表示になるが、レポートページはデータビュー上のフィルタリングに影響されず、結果が変わらない。
ただし、データビューの右クリックメニューで「テーブルのコピー」をする場合、フィルタリングで除外されたデータはコピーされない。
Excel：非表示になるが、集計されるかどうかは関数次第
見た目上ではフィルタリングで除外されたデータはシート上で非表示になるが、集計する際に使う関数によって非表示のデータも集計される。
例：
- SUM：非表示のデータも集計する
- SUBTOTAL：非表示のデータは集計しない
各機能の詳細動作
Power Queryのフィルタリング
フィルターのかけ方
クエリ編集画面を開くと、クエリテーブルの各列のヘッダーの横に「▼」ボタンがあり、それをクリックするとフィルターパネルが開き、いろんな種類のフィルターを設定することができる。
※システムが用意してくれているスタンダードなフィルターは列の型によって異なる。詳細は以下をご参考ください。
フィルタリング後の挙動
フィルタリング前の初期テーブルが以下である。
「Alphabet」列をA～Dに限定すると、以下のようになる。
この時点でフィルタリングされたE～Gの行のデータはテーブルから削除されている。そのため、「Number」列の合計を取ると、結果が4になる。
Power BI Desktopのデータビューのフィルタリング
フィルターのかけ方
場所がクエリ編集画面からデータビューになること以外はPower Queryと同様である。
各列のヘッダーの横に「▼」ボタンをクリックするとフィルターパネルが開く。またシステムが用意してくれているスタンダードなフィルターは列の型によって異なる。
フィルタリング後の挙動
フィルタリング前の初期テーブルが以下である。
またレポートページで「Number」列を合計したカードの値は以下である。
「Alphabet」列をA～Dに限定すると、以下のようになる。
この状態でレポートページの「Number」列を合計したカードの値を確認すると、7のままなので、データビューのフィルタリング操作はレポートに影響がないことが分かる。
ただし、以下のようにデータビューで右クリックし、「テーブルのコピー」をしてExcelに貼り付けてみると、フィルタリング後の表がコピーされていることが分かる。
Excelのフィルタリング
テーブルとして定義せず、セルに普通にデータを入力した場合、該当範囲のフィルターをONにすると、Power Queryと同じようなフィルターウィンドウが出てくる。Excelの列自体には型という概念がないが、その列のセルの型に合わせて選べるフィルターの種類が変わるのはPower Queryと同様である。
テーブルとして定義した場合、自動で各列のフィルター機能がONになる。
フィルタリング後の挙動
テーブルとして定義していない場合
フィルタリング前の初期テーブルが以下である。「Number」列（E列）の合計は7である。
「Alphabet」列をA～Dに限定すると、以下のようになる。
フィルタリングされた行は非表示になっているが、シート上には存在している。
そのためSUM関数で列の合計を計算した場合、非表示のE～Gのデータも計算に入ってしまい、結果は7のままである。
ただし、SUBTOTAL関数を使うとのE～Gのデータは集計から除外されるので、結果は4になる。
テーブルとして定義した場合
テーブルとして定義していない場合と同じ結果である。
フィルタリング前の初期テーブルが以下である。「Number」列（E列）の合計は7である。
「Alphabet」列をA～Dに限定すると、以下のようになる。
同じ様に、フィルタリングされた行は非表示になっているが、シート上には存在している。
SUM関数で列の合計を計算した場合、非表示のE～Gのデータも計算に入ってしまい、結果は7のままである。SUBTOTAL関数を使うとのE～Gのデータは集計から除外されるので、結果は4になる。
テーブルに集計行を追加した場合も、SUBTOTALが使われるので、フィルタリングされた行は集計から除外される。
参考
とある企業で主にBI（Power BI）による業務改善推進しているCoEメンバーのPublication。 Power Platformの他サービスもそこそこ活用している。
Discussion