こんな人向けの記事です
- Power BIのMクエリで「クエリのマージ」を多用する人
- 大きなテーブルを連結して見せる必要がある人
Power BI デスクトップで、複数のテーブルどうしをメールアドレスなどをキーにしてjoinする場合、Power Queryでは「クエリのマージ」を使います。
とても手軽で親の顔より見た操作なのですが、何やらデータソースの種類によっては劇遅になる事態に遭遇しました。
そこで、「クエリのマージ」を使わず、高速に同じデータ加工が実現できたので紹介します。
テストデータを用意します
まず基準となるTableAはExcelで用意しました。よくあるやつです。100名くらいのテーブルです。
まだ適切なデータが用意できていないときに、テスト用のテーブルとして「新しいテーブル」を使うことがあります。
クリックするとテスト用のテーブルを手入力で簡単に作成することができます。
「TableB」は一部だけマッチするようにBさんだけ用意しました。
まずはご存じクエリのマージ
TableAを開いた状態で「クエリのマージ」をクリックします。
下側に「TableB」を選択し、双方共通のキーとなるUser列をそれぞれ選択します。結合の種類は「左外部」でOKです。
「クエリのマージ」知らない、SQLのLeftJoinとかわからないという方はこちらの記事が図付きでイメージが付きやすいです。Power BI サービス上だと図が出てくれるので親切ですね。
OKをクリックすると、TableBという列ができています。右肩のボタンをクリックすると、TableBに含まれる列を選択して開くことができます。
まあ、そうですね！という状態になります。
では何が問題なのか？
モックアップでは正常に動作したので、実際にデータが入っているSharePointリストと差し替えたときに事件が起こりました。
SPOリストにTableBにあたるuserListを作りました。
Power QueryエディターでSPOリストも取り込みました。
TabeAを詳細エディタで開き、展開のところにある "TableB"の文字を"userList"に書き換えます。
結果は同じですが、マージされる側はSharePointリストになっています。
「閉じて適用」をクリックすると、なぜかちっとも更新が終わらず。（再現させたかったのですが、すんなり更新終わってしまいました。）
たまたまの不具合かどうかわかりませんが、TableA側の1行ずつにマージさせるたびにListへ問い合わせにいってるんじゃないか？というような動作でした。
そこで、列追加の関数 Lookupvalueを使うことにする
テーブルどうしをリレーションでつないでしまうと、双方に存在する行しか現れなくなってしまいます。実現したいのはLeftOuterJoinなので、これを実現できるのはなにかというと、Lookupvalue関数になります。
TableAを選択した状態で「新しい列」をクリックします。
使い方は意外と簡単で、今回の例では下記のようになります。
dept = LOOKUPVALUE(TableB[dept],TableA[User],[User])
- 第1引数：持ってきたいテーブルと列名
- 第2引数：キーにしたい元テーブルの列名
- 第3引数：持ってきたいテーブルでキーとマッチさせたい列名
dept列が追加できたので、同じようにarea列も追加してみます。
area = LOOKUPVALUE(TableB[area],TableA[User],[User])
複数の条件でマッチできる
もういちどリファレンスの構文を見てみると、search2_columnNameというのが任意に追加できることがわかります。
LOOKUPVALUE (
<result_columnName>,
<search_columnName>,
<search_value>
[, <search2_columnName>, <search2_value>]…
[, <alternateResult>]
)
これって、つまり私がこれまでキー文字を連結させた複合キーでマッチさせていたあの苦労も、関数一つで解決できてしまうということですね。
でかいデータでPower Queryの更新待ちをする時間もぐっと短縮です。
もっと早く知っておきたかったテクニックでしたので、自分自身へのメモもかねて記事にさせていただきます。
お粗末様でした。
こんな人が書いています
こちらの記事はランゲルハンス島のDDさんが紹介しました。ブログでクラウドフローのTIPSのようなものを書いたり、Qiita記事を書いたりしていますのでご贔屓に。
フォローやいいねいただけると嬉しいです。
関西のPowerPlatform系の勉強会にときどき出没しますので、「あのアイコンの顔の人だ！」と、気軽に声をかけていただけると喜びます！