スライサー側の処理を適当に作っているので、自動連係ではなく、ボタンを押すという操作が必要ですが、Power BI アドインのクエリをスライサーと連携させるためにあれこれやってみた記録を。
Power BI アドインでデータモデルにデータを読み込めば大量データを取り込むことも可能ですが、ある程度のデータを絞るのを、Excel 側の操作で制御したかったというのがそもそもの目的です。
出来上がったのが下のようなもので、スライサーで取得件数を選択し、「LINEITEM データ取得」でデータモデルを更新するような処理となっています。
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」のパラメーターの設定は以下のような内容となっています。
クエリでは、パラメーターを使用して、取得件数を制御していますので、クエリ自体を変更しなくても、パラメーターの値を変更して、データモデルを最新化すれば、取得される件数が制御されるような形です。
スライサーには、以下のようなクエリで設定した値を使用しています。
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 で、上記で取得したデータモデルを使用したピボットテーブルを作成します。
あとは、動的に件数を変更するための 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 行の読み込みが行われた状態となります。
100 を選択して、ボタンをクリックすると以下のように 100 件の状態となっています。
全件を取得してから、行のフィルターで間引くこともできますが、取得時からデータを限定し、限定した範囲を Excel 側の操作で変更する方法として、この内容を考えてみたのですが、どういう方法がシンプルなんでしょうかねぇ。