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

9957 ワード
DAX PowerQuery Excel 自分用メモ PowerBI Excel テキストリンク

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

はじめに

この手法は海外の方々の昔の記事を元に，自分なりに再現したという話です．参照した記事は下記の通りです．
・How to hack yourself in Power BI (and Power Pivot?)｜Imke Feldmann さん　2016/4/9
・Analysing Power BI DMV Queries In Power BI Desktop｜Chris Webbさん　2016/2/1
・Connect Excel Pivot to a Power BI Desktop｜ORAYLIS社
2016/2/2

手順

  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ではないです．

プロセス取得
Get-Process -Name msmdsrv

↓実行した様子．ここで「Id」の欄の数値がプロセスIDです．

２．localhostの番号を調べる

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

↓実行した様子．右端がプロセスID．この場合，ローカルアドレスの「49268」が接続先ということになります．

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

Power Queryからの接続方法

１．Power Queryエディタを開いて，「Analysis Service」を選択．

２．サーバ名に「localhost:番号」を入力して，OK．

下図の通り．
※複数のpbixが開いていると，プロセスも同数になるようです．どのプロセスがどのpbixかは，試してみるしかないでしょう．

３．キューブ内から任意のテーブル，列を選択して開きます．


↓実行結果

↓pbix側のデータ

注意点

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

１．そもそも，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クエリを入れる画面がありましたね．

しかし，DAXクエリを入れる場合，「データベース名」は省略できなくなります．（当たり前か）
↓データベース名というのは下図の長い文字列のことです．

なので，手順は下記のようになります．

１．とりあえず「localhost+番号」だけでいったん接続する．

２．クエリの最初のステップ（ソース/Source）をクリックして，データベース名をコピペ．

↓下図のName列がそれ．

３．同じくソース（Source）ステップの歯車を押して，接続内容の再編集へ．

予めDAXクエリのコードを作っておいて，そこから貼り付けるのがよいでしょう．
↓入力した後

４．ソース（Source）より後のステップがいらなくなるので削除する．

↓完成形．pbixどおりのデータ型がきます．

おまけ：メジャーも取り込めるが，挙動の見極めを慎重に．⇒DAXクエリを書かない場合の話です．

下記のように，リレーションもない単純な例ではちゃんと動きました．
しかし，DAXの記述を複雑化していった場合に，どこまでPower BIのレポートと同じ挙動になるかは，注意が必要です．

メジャー1 := CALCULATE(
　　　　　　　SUM('クエリ1'[column1]),
　　　　　　　ALL('クエリ1'),'クエリ1'[column1]<11
　　　　　 )
メジャー2 := CALCULATE(
　　　　　　　SUM('クエリ1'[column1]),
　　　　　　　ALL('クエリ1')
　　　　　 )

pbix側でテーブルのビジュアルに入れれば，こんな具合です．

これをPQで取り込んでみます．やり方はクエリの歯車マークから，ここでチェックを入れるだけ．
※メジャーを書く前のpbixと接続していた場合は，プレビューの更新をしないと出ません．

まず，メジャー1の方だけ．

次にメジャー2も加えると，pbix側で見たテーブルビジュアルと同様の結果となりました．なぜか，メジャーはデータ型が設定されますね．

テストした環境

Windows7 HOME Premium SP1
PowerShell　5.1.14409.1018
Excel　office365バージョン1910
Power Query　2.74.5619.262
Power BI　2019年10月版