LoginSignup
2
5

More than 3 years have passed since last update.

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

Last updated at Posted at 2019-11-04

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

方法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月版

2
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
5