みんな大好きピボットテーブル？を使ったレポート。ピボットテーブルのソースになっているデータモデルから抽出したデータを同じ Excel ワークブックにテーブルとして添えてみる。ピボットテーブル操作すればいいじゃんと思う場面は多いのだけど、みんなが使い方わかっているわけではないしね。このデータ別に欲しいんだけどみたいなこともあるので、じゃぁ別に用意しておけばいいかなというお話。
ピボットテーブルのデータを参照する方法にはいくつかあって、
CUBE 関数
参照先 : データモデル
データモデルに対し MDX で問い合わせるけど、表形式モードのデータモデルに対しても可能なので、Power BI データセット、SSAS、Excel データモデル どれでもよい。ポイントは Excel は集計をせず集計された結果を受け取るだけ。
GetPivotData 関数
参照先 : ワークシート上のピボットテーブル
いまさら使うことはないかな。個人的には時代遅れって感じすらある。
で、もうひとつ Excel データモデル(PowerPivot データモデル) に問い合わせるスタイル。これを採用。
手順など
Power Query エディターでソースになるデータを抽出変換など行いデータモデルにロードする。
別途データモデルの更新を考えるならば Power Query 使わなくても構わない。OneDrive for Business と Power BI Service を使って更新したいので Power Query を使っているのである。
Excel からPower BI にレポートを発行するというテーマは以前にポストした。
Excel で Power BI にレポートを発行する - Qiita
"このデータをデータモデルに追加する"が必須。ピボットテーブルの出力には必要ないので、"接続の作成のみ"にしておくけれども、ロード先を"テーブル"にしたときのテーブルを概要テーブルとしても構わないのですよ。ただ、このままだとすべての列と行になってしまうので調整が必要というだけ。この調整をやっておこうかというのがこの後。
ここで出力された Excel のテーブルは、接続テーブル/Connection Tables (クエリ定義という感じかな)を用いてワークブックのテーブルとしてに投影しているという感じでしょう。
接続に作成のみ"にしてもデータモデルへのロードは行われ、
Excel ワークブックには "ThisWorkbookDataModel" っていうデータモデルが追加される。
リレーションシップをきちんと設定して、
メジャーをひとつ設定しておいた。
で、ひとまず作ったピボットテーブル
このピボットテーブルを好きにぐりぐりして必要な情報にすりゃーいいなじゃないのなんだけど。それができない人が思いのほか多いのとピボットテーブルでは抽出しにくいものとか別途テーブルに用意しておこうかというのが主旨。
[既存の接続] ダイアログから 接続テーブル/Connection Table を開く
この後がっつり調整していくのでどの接続でも構わないけれども、ひとまず 'Query - 担当'を開く。
で、概要っぽいテーブルが欲しいので ロード先を "テーブル" にする。
ひとまず、データモデルに存在する '担当'テーブル すべての列と行。
該当のテーブルどこでもよいのだけど、コンテキストメニューから [DAX の編集]ダイアログへ。
コマンドの種類/Command Type を "DAX" にして、DAX クエリを記述
[OK]押下で結果がテーブルに投影された。メジャーで指定した書式が引き継がれなかったのは残念だけど、ワークシート上で設定すればよいだけなので、今回のところは勘弁しておいてあげよう。
これくらいならピボットテーブルでも表現できるだろうけれども、
データモデルに定義されていないメジャーを使う、定義済みメジャーを組み合わせるということもできるのでアイディア次第なのかなと。
DAX Query をいきなり記述するのは大変なので DAX Studio 使うとよいですよ。
DAX Studio とは - Qiita
思ったこと🙄
新規顧客だけのリストにしたり、ご無沙汰な顧客リストとか。テーブルになっているので Excel ならではのチャートなど使えますしね。
DAX, Power Query は、データのみを扱うだけの言語ではあるけれども、Excel / Power BI / SSAS 共通で使えるものなので、そろそろ使えるようになっておきなさいよというお気持ち。MDXに比べるとかなり理解しやすいと思うんだよね。