SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Excel の Power BI アドインのクエリをスライサーと連携させてみる

leave a comment

スライサー側の処理を適当に作っているので、自動連係ではなく、ボタンを押すという操作が必要ですが、Power BI アドインのクエリをスライサーと連携させるためにあれこれやってみた記録を。

Power BI アドインでデータモデルにデータを読み込めば大量データを取り込むことも可能ですが、ある程度のデータを絞るのを、Excel 側の操作で制御したかったというのがそもそもの目的です。

出来上がったのが下のようなもので、スライサーで取得件数を選択し、「LINEITEM データ取得」でデータモデルを更新するような処理となっています。

image

Power Query に不慣れなので、変なことをして取得している気がしないでもないですが、シンプルな方法があったらコメントいただけると幸いですm(_ _)m

今回、tpch の LINEITEM というテーブルに対してアクセスを行っているのですが、LINEITEM を取り込むためのクエリは以下のような記載としています。

let
    sql = "SELECT TOP " & Text.From(LINEITEM_TOP) &  " * FROM LINEITEM",
    ソース = Sql.Database("localhost", "tpch", [Query=sql])
in
    ソース

LINEITEM を取得する際には、TOP 句を指定して情報の取得を行っており、「LINEITEM_TOP」というパラメーターを使用して、クエリを制御しています。

「LINEITEM_TOP」のパラメーターの設定は以下のような内容となっています。

image

クエリでは、パラメーターを使用して、取得件数を制御していますので、クエリ自体を変更しなくても、パラメーターの値を変更して、データモデルを最新化すれば、取得される件数が制御されるような形です。

スライサーには、以下のようなクエリで設定した値を使用しています。

let
    ソース = {20, 50, 100, 200, 300},
    並べ替えられた項目 = List.Sort(ソース,Order.Ascending),
    テーブルに変換済み = Table.FromList(並べ替えられた項目, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    変更された型 = Table.TransformColumnTypes(テーブルに変換済み,{{"Column1", type number}})
in
    変更された型

 

Power Query 側の設定は以上で終わりです。

後は、Excel で、上記で取得したデータモデルを使用したピボットテーブルを作成します。

image

あとは、動的に件数を変更するための VBA を記述します。

今回はボタンのクリックイベントとして、以下のようなコードを使用しています。

    Dim TopCount As String
    For i = 1 To ThisWorkbook.SlicerCaches(1).SlicerCacheLevels(1).SlicerItems.Count
        If ThisWorkbook.SlicerCaches(1).SlicerCacheLevels(1).SlicerItems(i).Selected Then
           TopCount = ThisWorkbook.SlicerCaches(1).SlicerCacheLevels(1).SlicerItems(i).Caption
        End If
    Next
    
    ThisWorkbook.Queries("LINEITEM_TOP").Formula = TopCount & " meta [IsParameterQuery=true, Type=""Number"", IsParameterQueryRequired=true]"
    ActiveWorkbook.Model.Refresh

 

スライサーの処理は適当なので、複数選択されている場合や、スライサーの名称を使用した複数スライサーへの対応は実施していません…。

スライサーで選択された値を、パラメーターの式に設定し、設定後にデータモデルをリフレッシュすることで、スライサーで選択した件数を取得するようなコードとなっています。

パラメーターの値のみを変更したいのですが、パラメーターのプロパティに値のみのものが見当たらなくて、式自体を変更するような方法をとっています。

これで一通りの仕組みは完了です。

20 を選択した状態で、ボタンをクリックすると 20 行の読み込みが行われた状態となります。

image

100 を選択して、ボタンをクリックすると以下のように 100 件の状態となっています。

image

全件を取得してから、行のフィルターで間引くこともできますが、取得時からデータを限定し、限定した範囲を Excel 側の操作で変更する方法として、この内容を考えてみたのですが、どういう方法がシンプルなんでしょうかねぇ。

Written by masayuki.ozawa

2月 15th, 2017 at 9:30 pm

Posted in Power BI

Tagged with

Leave a Reply

*