Help us understand the problem. What is going on with this article?

Excel ピボットテーブル なレポートに概要テーブルを添えてみる

More than 1 year has passed since last update.

みんな大好きピボットテーブル?を使ったレポート。ピボットテーブルのソースになっているデータモデルから抽出したデータを同じ 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

image.png

"このデータをデータモデルに追加する"が必須。ピボットテーブルの出力には必要ないので、"接続の作成のみ"にしておくけれども、ロード先を"テーブル"にしたときのテーブルを概要テーブルとしても構わないのですよ。ただ、このままだとすべての列と行になってしまうので調整が必要というだけ。この調整をやっておこうかというのがこの後。

ここで出力された Excel のテーブルは、接続テーブル/Connection Tables (クエリ定義という感じかな)を用いてワークブックのテーブルとしてに投影しているという感じでしょう。

接続に作成のみ"にしてもデータモデルへのロードは行われ、
image.png
Excel ワークブックには "ThisWorkbookDataModel" っていうデータモデルが追加される。
image.png

リレーションシップをきちんと設定して、
image.png
メジャーをひとつ設定しておいた。
image.png
で、ひとまず作ったピボットテーブル
image.png
このピボットテーブルを好きにぐりぐりして必要な情報にすりゃーいいなじゃないのなんだけど。それができない人が思いのほか多いのとピボットテーブルでは抽出しにくいものとか別途テーブルに用意しておこうかというのが主旨。

[既存の接続] ダイアログから 接続テーブル/Connection Table を開く
image.png
この後がっつり調整していくのでどの接続でも構わないけれども、ひとまず 'Query - 担当'を開く。
image.png
で、概要っぽいテーブルが欲しいので ロード先を "テーブル" にする。
image.png
ひとまず、データモデルに存在する '担当'テーブル すべての列と行。
該当のテーブルどこでもよいのだけど、コンテキストメニューから [DAX の編集]ダイアログへ。
image.png
コマンドの種類/Command Type を "DAX" にして、DAX クエリを記述
image.png
[OK]押下で結果がテーブルに投影された。メジャーで指定した書式が引き継がれなかったのは残念だけど、ワークシート上で設定すればよいだけなので、今回のところは勘弁しておいてあげよう。
image.png
これくらいならピボットテーブルでも表現できるだろうけれども、
image.png
データモデルに定義されていないメジャーを使う、定義済みメジャーを組み合わせるということもできるのでアイディア次第なのかなと。

DAX Query をいきなり記述するのは大変なので DAX Studio 使うとよいですよ。
DAX Studio とは - Qiita
image.png

思ったこと🙄

新規顧客だけのリストにしたり、ご無沙汰な顧客リストとか。テーブルになっているので Excel ならではのチャートなど使えますしね。
DAX, Power Query は、データのみを扱うだけの言語ではあるけれども、Excel / Power BI / SSAS 共通で使えるものなので、そろそろ使えるようになっておきなさいよというお気持ち。MDXに比べるとかなり理解しやすいと思うんだよね。

その他

PowerBIxyz
#PowerBI とか #PowerQuery とか Microsoft MVP for Data Platform (2016,2017-2018,2018-2019,2019-2020,2020-2021), Access (2011-2015)
https://powerbi.connpass.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした