Edited at

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

DAX Studioが使えない状況では役立つかもしれません.

18:43追記:DAXクエリを書いて取り込む方法を入れました.

ただし,MS非公式なやり方のようですので,運用は要注意.


はじめに

この手法は海外の方々の昔の記事を元に,自分なりに再現したという話です.参照した記事は下記の通りです.

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の調べ方


方法1:DAX Studioで接続すれば,右下に出る.

Chris webbさんが書かれている通りです.DAX Studioが使えないような環境を問題にしているので,省きます.


方法2:TCP ViewというMS製ツールを使う.

ORAYLIS社が書かれた通りです.当該ツールは下記サイトから入手可能です.(僕は後述の方法で行けたので,試してません.)

https://docs.microsoft.com/ja-jp/sysinternals/downloads/tcpview


方法3:PowerShellで調べる.

僕がPowerShellを使いこなせていないので,泥臭い方法です.


1.「msmdsrv」のサービスIDを取る.

msmdsrvはPower BIを起動すると並行して稼動するプロセスで,ここが接続先ということになります.

入力するコードはこれだけ.srvであって,svrではないです.


プロセス取得

Get-Process -Name msmdsrv


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

image.png


2.localhostの番号を調べる

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

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

image.png

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


Power Queryからの接続方法


1.Power Queryエディタを開いて,「Analysis Service」を選択.

image.png


2.サーバ名に「localhost:番号」を入力して,OK.

下図の通り.

※複数のpbixが開いていると,プロセスも同数になるようです.どのプロセスがどのpbixかは,試してみるしかないでしょう.

image.png


3.キューブ内から任意のテーブル,列を選択して開きます.

image.png

↓実行結果

image.png

↓pbix側のデータ

image.png


注意点

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


1.そもそも,MS非公式.⇒というわけでもないらしい.

 MSさんがpbixからデータを取るコネクタを用意してないことからすれば,非公式ということになるでしょう.


2.抽出中はpbixファイルは開いていないといけない.

 msmdsrvが動いていないと,駄目なわけです.他方,開いたpbixに加えた編集内容は,pbixを上書き保存しなくてもPower Queryでの取り込み内容に反映されるようです.


3.localhostの番号はpbixファイルを開くたびに変わってしまう.

 なので,常にpbixファイルとセットで運用するような場合は,Power Queryに固め打ちせず,localhostの部分をパラメータ化しておく方が便利でしょう.


4.pbixでのデータ型やリレーションがない状態で取り込まれる.⇒避けられる.

いわば,「列がはっきり分かれたCSVファイルが,複数入っているフォルダ」と同様で,キューブとは言いがたいものだと思います.

後述のDAXクエリをかませることで,pbix内のデータモデルどおり取れるようです.


5.PQ取り込み時にDAXクエリは,たぶん使えないし,使ってはいけない.⇒むしろ使うべき.

Power Queryエディタで取り込む際に,DAXクエリを打ち込める画面が出てきますが,本件に関しては使ってはいけません.データ型等が失われていて不正確になるからです.

また,そもそもデータベース名の入力を要求されるので,操作が分からなかったです.


追記:DAXクエリを入れて,接続するには

MDXクエリは全く知らないので,ここではDAXクエリで挑戦します.

思えば,当初の接続画面にて,すでにDAXクエリ/MDXクエリを入れる画面がありましたね.

image.png

しかし,DAXクエリを入れる場合,「データベース名」は省略できなくなります.(当たり前か)

↓データベース名というのは下図の長い文字列のことです.

image.png

なので,手順は下記のようになります.


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


2.クエリの最初のステップ(ソース/Source)をクリックして,データベース名をコピペ.

↓下図のName列がそれ.

image.png


3.同じくソース(Source)ステップの歯車を押して,接続内容の再編集へ.

予めDAXクエリのコードを作っておいて,そこから貼り付けるのがよいでしょう.

↓入力した後

image.png


4.ソース(Source)より後のステップがいらなくなるので削除する.

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

image.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

これをPQで取り込んでみます.やり方はクエリの歯車マークから,ここでチェックを入れるだけ.

※メジャーを書く前のpbixと接続していた場合は,プレビューの更新をしないと出ません.

image.png

まず,メジャー1の方だけ.

image.png

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

image.png


テストした環境

Windows7 HOME Premium SP1

PowerShell 5.1.14409.1018

Excel office365バージョン1910

Power Query 2.74.5619.262

Power BI 2019年10月版

  • Power Queryからの接続方法
  • 注意点
  • 追記:DAXクエリを入れて,接続するには
  • おまけ:メジャーも取り込めるが,挙動の見極めを慎重に.⇒DAXクエリを書かない場合の話です.
  • テストした環境