Power BI DesktopのファイルからPower Queryでデータを取れる

DAX Studioが使えない状況では役立つかもしれません．
18:43追記：DAXクエリを書いて取り込む方法を入れました．

##はじめに
この手法は海外の方々の昔の記事を元に，自分なりに再現したという話です．参照した記事は下記の通りです．
・[How to hack yourself in Power BI (and Power Pivot?)｜Imke Feldmann さん　2016/4/9](https://www.thebiccountant.com/2016/04/09/hackpowerbi/#more-1147)
・[Analysing Power BI DMV Queries In Power BI Desktop｜Chris Webbさん　2016/2/1](https://blog.crossjoin.co.uk/2016/02/01/analysing-power-bi-dmv-queries-in-power-bi-desktop/)
・[Connect Excel Pivot to a Power BI Desktop｜ORAYLIS社 
 2016/2/2](https://www.oraylis.de/blog/connect-excel-pivot-to-a-power-bi-desktop)

##手順
1. データを取りたいpbixファイルを開く．
2. localhostの番号を調べる．
3. localhostの番号を使って，Power Queryから接続する．

##localhostの調べ方
####方法１：DAX Studioで接続すれば，右下に出る．
Chris webbさんが書かれている通りです．DAX Studioが使えないような環境を問題にしているので，省きます．

####方法２：TCP ViewというMS製ツールを使う．
ORAYLIS社が書かれた通りです．当該ツールは下記サイトから入手可能です．（僕は後述の方法で行けたので，試してません．）
https://docs.microsoft.com/ja-jp/sysinternals/downloads/tcpview

####方法３：PowerShellで調べる．
僕がPowerShellを使いこなせていないので，泥臭い方法です．
#####１．「msmdsrv」のサービスIDを取る．
msmdsrvはPower BIを起動すると並行して稼動するプロセスで，ここが接続先ということになります．
入力するコードはこれだけ.srvであって，svrではないです．

```powershell:プロセス取得
Get-Process -Name msmdsrv
```
↓実行した様子．ここで「Id」の欄の数値がプロセスIDです．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/99f9d003-c515-67a7-d802-41815a398839.png)

#####２．localhostの番号を調べる
さらに，コマンド`netstat -no`を入れて，TCP一覧を出し，先ほどのプロセスIDがあるローカルアドレスを目視で探しますー．

↓実行した様子．右端がプロセスID．この場合，ローカルアドレスの「49268」が接続先ということになります．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/cf0d6162-59b7-2e12-dee7-62b71ee30225.png)

※Windows10だと，`Get-NetTCPConnection`コマンドを使った方が便利なのでしょう．いずれにしても，現状，僕は使いこなしてないので，目視でやってます．:expressionless:


##Power Queryからの接続方法
####１．Power Queryエディタを開いて，「Analysis Service」を選択．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/26b1c197-3c11-e345-a4a9-7d3a6d76291e.png)

####２．サーバ名に「localhost:番号」を入力して，OK．
下図の通り．
※複数のpbixが開いていると，プロセスも同数になるようです．どのプロセスがどのpbixかは，試してみるしかないでしょう．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/2a7ecaf0-a4c9-cdd0-0302-d1feaf22f5f5.png)

####３．キューブ内から任意のテーブル，列を選択して開きます．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/c8d956b3-a4a5-ee14-5161-936c506174e0.png)
↓実行結果
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/7dd2a87f-900c-0ed3-ce5f-35d7f88c617a.png)

↓pbix側のデータ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/811636fa-aab8-b669-411f-bab13f47e480.png)


##注意点
@PowerBIxyz さんに指摘をいただきまして，訂正しました．むしろ，DAXクエリを経れば，ちゃんとした接続になるようですね．:sweat:

<blockquote class="twitter-tweet"><p lang="ja" dir="ltr">1. 非公式というか、そもそも 管理用ポートだったかと。<br>4. CUBE です。既定で MDX クエリをプッシュする。<br>5. DAX クエリ / MDX クエリいずれも問題なはず。列の値がtext になるのは ディメンジョン テーブル の列の CAPTION を取得しているから。<a href="https://twitter.com/hashtag/PBIJP?src=hash&amp;ref_src=twsrc%5Etfw">#PBIJP</a> <a href="https://t.co/CnXiSbrQ57">https://t.co/CnXiSbrQ57</a></p>&mdash; Takeshi Kagata (@PowerBIxyz) <a href="https://twitter.com/PowerBIxyz/status/1191232901527920641?ref_src=twsrc%5Etfw">November 4, 2019</a></blockquote> <script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>

###１．そもそも，MS非公式．⇒というわけでもないらしい．
　MSさんがpbixからデータを取るコネクタを用意してないことからすれば，非公式ということになるでしょう．
###２．抽出中はpbixファイルは開いていないといけない．
　msmdsrvが動いていないと，駄目なわけです．他方，開いたpbixに加えた編集内容は，pbixを上書き保存しなくてもPower Queryでの取り込み内容に反映されるようです．
###３．localhostの番号はpbixファイルを開くたびに変わってしまう．
　なので，常にpbixファイルとセットで運用するような場合は，Power Queryに固め打ちせず，localhostの部分をパラメータ化しておく方が便利でしょう．
###４．~~pbixでのデータ型やリレーションがない状態で取り込まれる．~~⇒避けられる．
~~いわば，「列がはっきり分かれたCSVファイルが，複数入っているフォルダ」と同様で，キューブとは言いがたいものだと思います．~~
後述のDAXクエリをかませることで，pbix内のデータモデルどおり取れるようです．

###５．~~PQ取り込み時にDAXクエリは，たぶん使えないし，使ってはいけない．~~⇒むしろ使うべき．
~~Power Queryエディタで取り込む際に，DAXクエリを打ち込める画面が出てきますが，本件に関しては使ってはいけません．データ型等が失われていて不正確になるからです．
また，そもそもデータベース名の入力を要求されるので，操作が分からなかったです．~~
##追記：DAXクエリを入れて，接続するには
MDXクエリは全く知らないので，ここではDAXクエリで挑戦します．
思えば，当初の接続画面にて，すでにDAXクエリ/MDXクエリを入れる画面がありましたね．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/f7890f0e-4db0-e8c9-221f-a9e4f182e5b9.png)
しかし，DAXクエリを入れる場合，「データベース名」は省略できなくなります．（当たり前か）
↓データベース名というのは下図の長い文字列のことです．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/d0286cbe-458d-8c26-13b2-aadbe3ae83d7.png)

なので，手順は下記のようになります．
####１．とりあえず「localhost+番号」だけでいったん接続する．

####２．クエリの最初のステップ（ソース/Source）をクリックして，データベース名をコピペ．
↓下図のName列がそれ．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/5985d8fe-e859-9728-a9ad-c4ac9c8de27d.png)
####３．同じくソース（Source）ステップの歯車を押して，接続内容の再編集へ．
予めDAXクエリのコードを作っておいて，そこから貼り付けるのがよいでしょう．
↓入力した後
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/0a84ae58-196f-4893-87c6-21905329b8df.png)

####４．ソース（Source）より後のステップがいらなくなるので削除する．
↓完成形．pbixどおりのデータ型がきます．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/e95587c9-de0a-fa93-af6f-4112fbd36a1e.png)


##おまけ：メジャーも取り込めるが，挙動の見極めを慎重に．⇒DAXクエリを書かない場合の話です．
下記のように，リレーションもない単純な例ではちゃんと動きました．
しかし，DAXの記述を複雑化していった場合に，どこまでPower BIのレポートと同じ挙動になるかは，注意が必要です．

```
メジャー1 := CALCULATE(
　　　　　　　SUM('クエリ1'[column1]),
　　　　　　　ALL('クエリ1'),'クエリ1'[column1]<11
　　　　　 )
メジャー2 := CALCULATE(
　　　　　　　SUM('クエリ1'[column1]),
　　　　　　　ALL('クエリ1')
　　　　　 )
```
pbix側でテーブルのビジュアルに入れれば，こんな具合です．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/22efdb0d-077c-7d5c-a755-2bd5239863e7.png)

これをPQで取り込んでみます．やり方はクエリの歯車マークから，ここでチェックを入れるだけ．
※メジャーを書く前のpbixと接続していた場合は，プレビューの更新をしないと出ません．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/7d11a99b-5a41-e325-0c22-0f20e87f275b.png)


まず，メジャー1の方だけ．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/d93f9337-77ba-5557-8435-9036642fb555.png)

次にメジャー2も加えると，pbix側で見たテーブルビジュアルと同様の結果となりました．なぜか，メジャーはデータ型が設定されますね．
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/370992/2bf60203-592f-c753-c856-07c7c460e83c.png)


##テストした環境
Windows7 HOME Premium SP1
PowerShell　5.1.14409.1018
Excel　office365バージョン1910
Power Query　2.74.5619.262
Power BI　2019年10月版