Power BIは非常にパワフルな分析ツールです。ETL*1からモデリング*2が完了し、適切なメジャー*3を記述した後、Power BI Desktopにて自由にチャートやテーブルを配置し、分析業務を開始することが可能となります（下図）。
上記ビジュアルと同じように、好きな切り口で好きなようにExcelでも同じように分析を実現できるのが「Excelで分析」という機能になります（詳細は下記ブログより）。
その際の留意事項に
Power BIを閉じた場合、Excel Pivotを更新しようとすると下記のメッセージが出現し、Port番号を更新する必要がある
と記述しました。localhostの右にある数字がポートナンバーですが、
Pivot数だけエラーメッセージが何度も出現するため、根気よく全て変更が必要
とも注意書きを残しました。この現象は
- Power BI Desktopで「Excelで分析」を使った場合
- 発生条件はPower BI Desktopを閉じてもう一度開いた場合にExcelのPivotを更新しようとした場合（Power BI Serviceでアクセス権限がある限り、このような現象は発生しない）
という条件があるのですが、上述の通り、Excelワークブック内に多くのPivotを作成していた場合の更新は非常に面倒です。
最新の更新法（2023年12月）
2023年12月時点のPower BI Desktopは従来の更新法のように、DAX Studioを開いてPort番号を取得する必要はありません。以下の手順で簡単に取得が可能です。
- Power BI Desktop > モデルビュー
- 「データ」>「モデル」>「プロパティ」>「サーバー」◽をクリック
従来の更新法
上記記事に更新を行う場合には
- Power BI Desktopから外部ツールのDAX Studioを開く
- 右下のlocalhost:○○をコピー
- 上図のエラーメッセージ（Analysis Service Connection 14.0）にコピーした番号を貼り付け
- Nextをクリックし、finish
という作業を（場合によっては何度も）行う必要がありますが、最新の更新法で一回の更新で済むようになります。
具体的に更新手順を示す前に、まずは以下のようなBIレポートからExcelで分析をPower BI Desktopから立ち上げてみます。
Power BI Desktopを立ち上げ、外部ツール > Analyze in Excelの順でクリック。
※ Analyze in Excelがインストールされていない場合は、上記ブログを参考するか、下記SQLBIサイトよりメールを登録してダウンロード > インストールしてください
Analyze in Excelをクリックすると、自動的に以下のようにPivotテーブルのフレームだけが作成されます（※警告アイコンが2つほど出現しますが、全て有効にしてください）
ここでいくつか異なる切り口でPivotテーブルを作っていきます。
この状態でPower BI Desktopを閉じて、もう一度Power BIを立ち上げてExcel Pivotを更新しようとすると、下記の通り、localhost（サーバーネーム）を入れるよう、促されます。
ここで再び開いたPower BIからDAX Studioを立ち上げ、以下のようにポートナンバーをコピーします。
Pivotテーブルが1つだけの場合、1回の更新で全て済みます。しかしながら、Pivotを10も20も作っていた場合、この作業を延々と繰り返さないといけません。非常に単純な作業であるが故に、非常に苦痛を伴う作業となります。
そこで、ポート番号をコピペするやり方ではなく、接続文字列を変更することで一回で全て更新が完了できるようにしていきます。以下その手順です。
簡単更新法
更新手順は非常に似ています。
- Power BIを閉じる（=その時点の作業が完了したという前提）
- 「Excelで分析」で作ったPivotテーブルを含むExcelとそのソースとなるPower BI Desktopファイルを再度立ち上げる
※この状態で更新しようとすると、ポートナンバーが変更されているため、更新ができないことからDAX Studioを立ち上げてlocalhostをコピーするのだが、下記3のように違うやり方で更新していきます - Power BIでもう一度、外部ツールタブにある「Analyze in Excel」をクリック
- データが入っていないPivotテーブルを自動生成した新しいExcelが立ち上がる
- 下記①～③の順番で「接続文字列」を全てコピーする
- 更新を行う必要のあるExcelに戻り、同じ手順で文字列を貼り付け
- （メッセージボックスが出ることなく）更新が一括で完了
このように、接続文字列を置き換えることで複数のPivotが含まれていても、一回の更新で全てが完了します。ピボットテーブルが複数ある場合、同じファイルを更新するのに非常に時間が掛かっていた作業もこのやり方であれば一発で完了しますので、実用性が一気に高まったと言えます。
おまけ
上記で一応更新法の説明は終わりですが、1つおまけを紹介しておきます。通常、Pivotテーブル内の数値セルをダブルクリックすると1000行まで詳細データが別シートに展開（＝ドリルスルー機能）されますが、この行数制限を変更することができます。
- Pivotテーブルを選択
- 同じ手順で下記のように好きな数字に変更
- セル内をダブルクリックして行数を確認
なお、このやり方はデータモデル内の全てのデータを取得できるものではなく、上図の通り、今回の例ではInventoryテーブルのUnitCost、DaysInStock、OnHandQuantityという3つの列についてのみ、情報を取得しています。
自分で好きな詳細を取得するためには、DAXクエリをある程度分かっている必要があり、テーブルクエリを記述できるのであれば例えば上記テーブルを以下のように変更することができます。
ちなみに、Power BI Serviceにてレポートがチームと共有されている場合、共有された側がファイルをダウンロードした場合、上述の設定ではテーブルを更新できません。
「Excelで分析」を使用する場合、通常は以下の①～②のステップを踏みます。レポートを作成した人がPower BI Serviceにレポート（データセットも含む）を発行すると、共有された側は「Excelで分析」（英語ではAnalyze in Excel）をクリックすることでExcelをダウンロードすることができます。
チームメンバーやマネジャーは通常、上述したこと（DAXクエリを記述）を実践するのに向いていないことが想定されるため、上図のやり方を改め、下図が現実的になるかと思います。
この図では、レポート発行を行った作成者が自身でExcelをダウンロードし、前述したやり方で必要なDAXクエリを記述し、詳細データを抽出しておきます。その後、③の通り、レポート作成者が「Excelで分析」からファイルダウンロードし、詳細データを抽出した状態でファイルをチームメンバーやマネジャーと共有します。
この後、少し手を加える必要がありますが、④の通り、ユーザーであるメンバーやマネジャーも「Excelで分析」からファイルをダウンロードし、そのファイルを一回開きます。その後、下図の通り、
a) データ > プロパティ
b) 定義 > 接続文字列 > コピー
c) レポート作成者が共有したExcelを開き、データ > プロパティ
※ ファイルを開くと、クエリが自動更新してエラーになる可能性があるが、ボタンをクリックして全て無視
d) 接続の右側にある小さなアイコンをクリック
e) 定義 > 接続文字列 > ステップbでコピーしたコンテンツをペースト
これで自動的にユーザーがダウンロードしたExcel内に入っていた「接続文字列」を使って読み込みが始まり、詳細データを含むテーブルの更新が始まります。無事更新が終われば、ファイルを右クリック > テーブル > クエリの編集をクリック。
すると、下図のようにDAXクエリが出現し、この例では20211010が日付であるため、ここを例えば20211011に変更すれば、翌日のデータが抽出されるようになります。
このやり方は、レポート作成者に代わって、現場担当者やマネジャーが詳細データを必要とした場合に自分たちでデータを取得できることにメリットがあり、加工前の詳細データとは違い、既に構築済モデルから出力されたデータであるところに特徴があります。これは、通常のデータにはない計算結果や切り口をDAX式で変更できるところに魅力があり、例えば不要な列があれば上図の「コマンドの文字列」からその列名を削除すれば良いわけです。
やや応用的な使い方ですが、データ抽出にいつも時間を掛けている人には重宝するやり方だと思います。ちなみに、データのプロと言われる方たちからは十中八九、このようなやり方は”邪道”であり、「Excelで分析」でPivotテーブルで分析を完結せよと言われます。このような考え方は、基データを抽出してしまうと、レポートがいたずらに増えてしまい、データの信ぴょう性を担保できなくなるだけでなく、1つの真実を基に構成されたデータセット*4を使うことがガバナンス等の観点から見てベストプラクティスであるためです。
あるべき姿で非常に理解はできますが、そこまでたどり着いている会社はそう多くないというのが現実です。
まとめ
- Power BI DesktopでAnalyze in Excel（Excelで分析）を使用すると、Power BI Desktopを閉じた場合、ポートナンバーが変わり、再びPower BI Desktopを開いてもそのままでは更新できず、DAX Studio等を使ってポートナンバーを取得し、Excelの接続先を変更する必要がある
- Pivotテーブルが少ない場合はそれほど負担にはならないが、テーブルが多い場合、接続文字列を全て入れ替えて更新したほうが早い
- Analyze in Excelで接続されたPivotをダブルクリックするとドリルスルー機能が働き、クリックしたセル内の詳細が別シートに出力される
- 出力されたシートは初期状態では1,000行までしか出力されていない
- これを変更する場合、Pivotをクリックした状態で、データ > プロパティ > OLAPドリルスルーの「取得するレコードの最大数」を変更
- ドリルスルーで出力されたテーブルをDAXクエリを使って変更することができる。ただし、DAXの知識がないと難しい
