業務改善と標準化を同時に実現:MS 365業務システム開発

Power Automateで他のテーブルからVLOOKUP関数みたいにデータを持ってくる方法(結合、ルックアップ機能)

  
Power Automateで他のテーブルからVLOOKUP関数みたいにデータを持ってくる方法(結合、ルックアップ機能)
\ この記事を共有 /
Power Automateで他のテーブルからVLOOKUP関数みたいに...

Power Automateの「アレイのフィルター処理」アクションを使って、他のテーブルからエクセルのVLOOKUP関数みたいにデータを持ってくる方法を分かり易く図解説明をしています。

(動画時間:12:34)

Power AutomateでもVLOOKUPみたいな機能を持たせられる。

こんにちは、リーンシグマブラックベルトのマイク根上です。
業務改善コンサルをしています。

最近マイクロソフトのPower Automateのご質問をよく頂きます。
そこで今日はよくあるシナリオで、顧客コードなどに紐づけて
他のテーブルからデータをPower Automateで持ってくるやり方を図解説明します。

PowerAppsやエクセルだと
LOOKUP系の関数が多くあるので簡単にできますが、
Power Automateではその様な関数がなくて、
皆さん苦労をしているのではないでしょうか?

僕もその一人で、今回それがうまくいったのでその方法を共有します。

SharePointリストで作ったアプリのデータベース

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

簡略化した基幹システムの顧客リストレポート

上図が基幹システムからCSVで出した最新の顧客リストのレポートとします。
新規のお客さんが追加されていて、既存のお客さんの担当者が変わっています。
この変更をアプリの顧客リストデータベースに反映させるのです。
一つだけ条件があって、この表をエクセルテーブルにしておく必要があります。

アプリ上の「リストの更新」ボタンを押すと、
Power Automateのフローがトリガーされて、
さっきのエクセルの最新顧客リストが
このアプリのデータベースに反映される様にするのです。

エクセル内の更新するデータを取得する:「表内に存在する行を一覧表示」アクション

これはPower Automateの開発画面です。
トリガーはPowerAppsですね。
最初のアクションで、SharePointのドキュメントライブラリに保存されている
エクセルファイル内のデータを取得します。

「新しいステップ」から、「Excel Online」内の
「表内に存在する行を一覧表示」アクションを使います(下図参照)。

サイト名、フォルダー名、そして、ファイル名を指定し、
エクセルテーブル名を指定すると、そのデータを全て取得できます。

Power Automate 「表内に存在する行を一覧表示」アクション

もし、そのテーブル内に該当しないレコードがあれば、
「フィルタークエリ」で絞り込みをした方が、フローの処理が速くなります。

その下に「上から順に取得」とあり(上図参照)、
規定値は「すべて」と書いてありますが、
実はこのままだと100レコードしか持って来れません。
最大5000なので、ここはいつも5000に変えましょう。
他のデータ取得系のアクションも同じ様にして下さい。

このアクションの名前は後で参照するので、
「新規テーブル」に変えておくと分かり易いです。

SharePointリスト内の既存データを取得する:「複数の項目の取得」アクション

次にアプリのデータベースの既存のデータを取得します。
「アクションの追加」の後、「項目」で検索し、
「複数の項目の取得」アクションを追加します。
⇒「【Power Automate】期限が来たら自動で通知メールを送信する。SharePoint「複数の項目の取得」アクション、フィルタークエリ」

Power Automate アクションで「項目」で検索


サイト名と、リスト名を指定します。
ここにも「上から順に取得」の項目があるので、
5000を入れておき、このアクションの名前を「既存テーブル」とします。

テーブルの更新機能のアルゴリズム

ここで一度これからやる事を整理しておきましょう。
新しい顧客リストである「新規テーブル」と
既存のリストである「既存テーブル」の取得ができました。

新規テーブルの顧客コードを一つずつ見ていき、
その顧客コードが既存テーブルにあれば、それは既存顧客で、
その新規テーブルの情報で既存テーブルを変更します。

もし顧客コードが存在しなければ、それは新規の顧客
そのレコードをそのまま既存テーブルに追加するのです。
それを実装するのが次です。

まず、新規テーブル内で
「顧客コードを一つずつ確認する」というのはどうするか?

Power Automateでの繰返し処理は「コントロール」内の
「Apply to each」アクションを使います。

そしてその対象が「動的なコンテンツ」内で「新規テーブル」です。
「value」と「body」がありますが、
「value」を使って下さい。(下図参照)

Power Automate 「Apply to each」アクションの対象選択

ルックアップ機能を与えるアクション:「アレイのフィルター処理」アクション

次に「Apply to each」内で「アクションの追加」をクリックし、
「組み込み」タブ内の、「データ操作」内の、
「アレイのフィルター処理」アクションを使います。
これが「顧客コードを確認する」部分になります。

このアクションは本来テーブルや配列を必要な条件で絞り込む、
つまりフィルター処理をします。

それを「Apply to each」内で使うと、
さっきの「一つずつ確認する」処理ができるのです。
ここで新規テーブル内の「顧客コード」列と
既存テーブル内の「Customer_ID」列を比べたいので、

日本語がおかしいですが、「差出人」で「既存テーブル」の「value」を選んで、
「値の選択」で、既存テーブルの「Customer_ID」を選び、
「次の値に等しい」はそのままで、
次の「値の選択」で、新規テーブルの顧客コードを選びます。(下図参照)

Power Automate 「アレイのフィルター処理」アクション

ここで一度テストをしましょう。
画面右上の「テスト」から、「手動」でテストをします。
(数十秒後)エラーが無くうまくいきました。

しかし、必ず、「出力」で期待通りのデータを取得したかを確認して下さい。
すると最後の出力で何のデータも出ていません。
共通の顧客コードがあるので、いくつかは出力されるはずでした。

実は既存テーブルの顧客コードに
「”(ダブルクォーテーション)」が無いのでデータ型は数値型です。
しかし、新規テーブルの顧客コードは「”」で挟まれていてテキスト型なのです。
この二つのデータ型が違うので、同じ番号でも
違う顧客コードと判断されてしまっていたのです。

新規テーブルの顧客コードを数値型に変えましょう。編集画面に戻ります。
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の時は顧客コードが既存テーブルに無かった時なので
新規顧客という事なのです。

Power Automate 「条件」アクション

ですので「はいの場合」アクション内で、
「アクションの追加」で、また「項目」で検索して、
「SharePoint」内の「項目の作成」アクションで新規のレコードの追加をします。
こっちではID列の入力は必要ありません。

各列の値を入れるのにさっきと全く同じ様に
最初に「現在のアイテム」を挿入してそれを編集して列名を入れます。
これを全ての列でやります。

そして条件が「いいえの場合」は既存顧客で、
さっき作った「項目の更新」アクションをドラッグして、
「いいえの場合」アクション内でマウスが「+」に変わった所でドロップして移動させます。

これでフローの完成です。

テーブルの更新のフローの全体図

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

「こちらの記事も読まれてます。」