Power Automateで他のテーブルからVLOOKUP関数みたいにデータを持ってくる方法(結合、ルックアップ機能)
Power Automateの「アレイのフィルター処理」アクションを使って、他のテーブルからエクセルのVLOOKUP関数みたいにデータを持ってくる方法を分かり易く図解説明をしています。
(動画時間:12:34)
Power AutomateでもVLOOKUPみたいな機能を持たせられる。
こんにちは、リーンシグマブラックベルトのマイク根上です。
業務改善コンサルをしています。
最近マイクロソフトのPower Automateのご質問をよく頂きます。
そこで今日はよくあるシナリオで、顧客コードなどに紐づけて
他のテーブルからデータをPower Automateで持ってくるやり方を図解説明します。
PowerAppsやエクセルだと
LOOKUP系の関数が多くあるので簡単にできますが、
Power Automateではその様な関数がなくて、
皆さん苦労をしているのではないでしょうか?
僕もその一人で、今回それがうまくいったのでその方法を共有します。

まず、簡略化したシナリオが次で、
PowerAppsのアプリの顧客リストデータベースを
SharePointリストで用意しています。(上図参照)

上図が基幹システムからCSVで出した最新の顧客リストのレポートとします。
新規のお客さんが追加されていて、既存のお客さんの担当者が変わっています。
この変更をアプリの顧客リストデータベースに反映させるのです。
一つだけ条件があって、この表をエクセルテーブルにしておく必要があります。
アプリ上の「リストの更新」ボタンを押すと、
Power Automateのフローがトリガーされて、
さっきのエクセルの最新顧客リストが
このアプリのデータベースに反映される様にするのです。
エクセル内の更新するデータを取得する:「表内に存在する行を一覧表示」アクション
これはPower Automateの開発画面です。
トリガーはPowerAppsですね。
最初のアクションで、SharePointのドキュメントライブラリに保存されている
エクセルファイル内のデータを取得します。
「新しいステップ」から、「Excel Online」内の
「表内に存在する行を一覧表示」アクションを使います(下図参照)。
サイト名、フォルダー名、そして、ファイル名を指定し、
エクセルテーブル名を指定すると、そのデータを全て取得できます。

もし、そのテーブル内に該当しないレコードがあれば、
「フィルタークエリ」で絞り込みをした方が、フローの処理が速くなります。
その下に「上から順に取得」とあり(上図参照)、
規定値は「すべて」と書いてありますが、
実はこのままだと100レコードしか持って来れません。
最大5000なので、ここはいつも5000に変えましょう。
他のデータ取得系のアクションも同じ様にして下さい。
このアクションの名前は後で参照するので、
「新規テーブル」に変えておくと分かり易いです。
SharePointリスト内の既存データを取得する:「複数の項目の取得」アクション
次にアプリのデータベースの既存のデータを取得します。
「アクションの追加」の後、「項目」で検索し、
「複数の項目の取得」アクションを追加します。
⇒「【Power Automate】期限が来たら自動で通知メールを送信する。SharePoint「複数の項目の取得」アクション、フィルタークエリ」

サイト名と、リスト名を指定します。
ここにも「上から順に取得」の項目があるので、
5000を入れておき、このアクションの名前を「既存テーブル」とします。
テーブルの更新機能のアルゴリズム
ここで一度これからやる事を整理しておきましょう。
新しい顧客リストである「新規テーブル」と
既存のリストである「既存テーブル」の取得ができました。
新規テーブルの顧客コードを一つずつ見ていき、
その顧客コードが既存テーブルにあれば、それは既存顧客で、
その新規テーブルの情報で既存テーブルを変更します。
もし顧客コードが存在しなければ、それは新規の顧客で
そのレコードをそのまま既存テーブルに追加するのです。
それを実装するのが次です。
まず、新規テーブル内で
「顧客コードを一つずつ確認する」というのはどうするか?
Power Automateでの繰返し処理は「コントロール」内の
「Apply to each」アクションを使います。
そしてその対象が「動的なコンテンツ」内で「新規テーブル」です。
「value」と「body」がありますが、
「value」を使って下さい。(下図参照)

ルックアップ機能を与えるアクション:「アレイのフィルター処理」アクション
次に「Apply to each」内で「アクションの追加」をクリックし、
「組み込み」タブ内の、「データ操作」内の、
「アレイのフィルター処理」アクションを使います。
これが「顧客コードを確認する」部分になります。
このアクションは本来テーブルや配列を必要な条件で絞り込む、
つまりフィルター処理をします。
それを「Apply to each」内で使うと、
さっきの「一つずつ確認する」処理ができるのです。
ここで新規テーブル内の「顧客コード」列と
既存テーブル内の「Customer_ID」列を比べたいので、
日本語がおかしいですが、「差出人」で「既存テーブル」の「value」を選んで、
「値の選択」で、既存テーブルの「Customer_ID」を選び、
「次の値に等しい」はそのままで、
次の「値の選択」で、新規テーブルの顧客コードを選びます。(下図参照)

ここで一度テストをしましょう。
画面右上の「テスト」から、「手動」でテストをします。
(数十秒後)エラーが無くうまくいきました。
しかし、必ず、「出力」で期待通りのデータを取得したかを確認して下さい。
すると最後の出力で何のデータも出ていません。
共通の顧客コードがあるので、いくつかは出力されるはずでした。
実は既存テーブルの顧客コードに
「”(ダブルクォーテーション)」が無いのでデータ型は数値型です。
しかし、新規テーブルの顧客コードは「”」で挟まれていてテキスト型なのです。
この二つのデータ型が違うので、同じ番号でも
違う顧客コードと判断されてしまっていたのです。
新規テーブルの顧客コードを数値型に変えましょう。編集画面に戻ります。
Power Automateにもint関数があり、値を整数に変換できます。
「アレイのフィルター処理」アクション内の
「詳細設定モードで編集」をクリックします。
実はこのボックスはすごく使い難いのでこの式をコピーして、
他のソフトで編集すると楽です。
「詳細設定モードで編集」内
@equals(item()?[‘Customer_ID’], int(items(‘Apply_to_each’)?[‘顧客コード’]))
二つ目の「items」の前に「int(」と入れて、
「顧客コード」の後のカギ括弧の後に括弧を入れて、
コピペで戻します。これでまたテストをします。
結果の出力を見ると、今度はうまくいきました。
SharePointの既存データの編集には「項目の更新」アクション
これで必要なデータが全て揃ったので最後の段階で
アプリのデータベースのSharePointの更新をします。
「アクションの追加」から、「項目」で検索して、
「SharePoint」内の「項目の更新」アクションを使います。
⇒「【Power Automate】承認ワークフローの結果をSharePointリストに追加/編集する方法(「項目の更新」アクション)」
この中の「ID」列は必須で、既存テーブルから持って来れます。
そこで「アレイのフィルター処理」アクションの結果を使うのです。
「動的なコンテンツ」の編集画面内の「式」タブにして、
その式は「body(‘アレイのフィルター処理’)」となります。
しかし、これはテーブルですので、
レコードと列を指定する必要があります。
そこで最初のレコードという意味の「[0]」、そして列名の「[‘ID’]」と入れます。
「項目の更新」アクション内のID列
body(‘アレイのフィルター処理’)[0][‘ID’]
他の列は全て新規テーブルから持ってきます。
その一つの列を選択して、
「動的なコンテンツ」の「現在のアイテム」を選びます。

しかし、これはレコードですので列を指定する必要があり、
挿入したものをコピーし、「式」タブにしてペーストします。
「@」と最初と最後の括弧を消して、
「?」、「[‘(新規テーブルからの列名)’]」を入れます。
例:Custoer_ID列内
@{items(‘Apply_to_each’)}
↓
items(‘Apply_to_each’)?[‘顧客コード’]
この中の「?」は大事で、これが無いと、
この列に値が無い時にフロー時にエラーとなります。
この「?」でエラー回避ができるのです。
他の列で似た様な式を書くのでこの式をコピーし「OK」をクリックします。
これと同じことを他の列でもやっていきます。
SharePointに新規レコードを追加するには「項目の作成」アクション
これで完成かと思いきや、一つ問題があります。
顧客コードが既存テーブルになかったら、
その「ID」列が見つかりません、というよりも無いので、
「項目の更新」アクションは使えないのです。
それは新規顧客で、違うアクションが必要です。
そこで、このループ内で毎回、既存顧客か新規顧客かを判断する
「条件」アクションが必要です。
「アレイのフィルター処理」アクションの後で、
「アクションの追加」から、「コントロール」内の「条件」アクションを選びます。
条件の「値の選択」で下記の数式を入れます。
最初の「値の選択」内
length(body(‘アレイのフィルター処理’))
body関数で前のアクションの結果のテーブルを取得し、
length関数でそのテーブル内の行の数を数えるのです。
これが0の時は顧客コードが既存テーブルに無かった時なので
新規顧客という事なのです。

ですので「はいの場合」アクション内で、
「アクションの追加」で、また「項目」で検索して、
「SharePoint」内の「項目の作成」アクションで新規のレコードの追加をします。
こっちではID列の入力は必要ありません。
各列の値を入れるのにさっきと全く同じ様に
最初に「現在のアイテム」を挿入してそれを編集して列名を入れます。
これを全ての列でやります。
そして条件が「いいえの場合」は既存顧客で、
さっき作った「項目の更新」アクションをドラッグして、
「いいえの場合」アクション内でマウスが「+」に変わった所でドロップして移動させます。
これでフローの完成です。

今回はテーブル全てを入れかえる事例でやりましたが、
VLOOKUP関数の様に任意のキーで一つの値だけ持ってくる時も
「アレイのフィルター処理」アクションでできます。
いろいろ応用ができると思うのでぜひ使ってみて下さい。
「こちらの記事も読まれてます。」