ルドルフもわたるふもいろいろあってな

Microsoft 365、Power Platform、PowerShellについて調べたことや検証したことなどを投稿します。技術の話は面白い。

【質問回答】【Power Automate】ExcelテーブルでIDが同じ行の内容欄を結合する

Power Automate TIPS 部品化 質問回答 GitHubで公開済み

Twitterのタイムラインで見かけたこんな課題に（勝手に）こたえてみました。


作ったフローは元ツイートを投稿されたふーさんに提供しました。


フローはGitHubの下記URLにアップロードしました。
github.com

概要

これはExcelテーブルでIDが同じ行の内容欄のテキストを結合して配列に変換するフローです。

フロー図

アクションの設定を閉じた状態のフロー図


開いた状態のフロー図


フローの使用イメージ

インプットとして使用するExcelファイルを用意します。この解説ではOneDrive for Business にExcelファイルを保存しておきます。

Excelファイルにテーブルを作成します。このテーブルに「ID」と「内容」という2つの列を作成します。
テーブルにあらかじめいくつか行を追加しておきます。行のいくつかには重複したIDを記載しておきます。

フローを手動実行します。

フローの最後のアクションの出力に目的の配列が出力されます。

ステップごとに解説します。

ステップごとの解説

Excelファイルからテーブルのデータを取得

まずは「Excel Online」>「表内に存在する行を一覧表示」アクションを使ってテーブルのデータを取得します。

取得結果は下図の通りです。取得したデータはテーブルの各行が要素になったJSONのデータです。

簡易版JSON Dictionary 形式のデータに変換する

「データ操作」>「選択」アクションを使って簡易版JSON Dictionary 形式のデータに変換します。

変換結果は下図の通りです。KeyがIDでありValueが空白の文字列であるJSONデータです。このJSONデータが最終的な出力の初期状態です。後続のアクションでValueに文字列を入力します。ポイントは、複数あったJSONの要素をひとつの要素にまとめたことによってKeyであるIDの重複が排除されることです。

このJSONデータを、後続のアクションでValueを更新するために変数に格納しておきます。

JSONデータのプロパティを配列化したデータを取得（用途：繰り返し処理のインデクサとして使うため）

前述の処理で作成したJSONデータのプロパティごとに繰り返し処理（= Apply to each）を行うために、「データ操作」>「選択」アクションを使ってプロパティの配列を作成します。

"from": "@split(substring(string(outputs('作成-簡易版JSON_Dictionaryを作る')), 1, sub(length(string(outputs('作成-簡易版JSON_Dictionaryを作る'))), 2)), ',')",
"select": "@substring(substring(item(), 0, indexOf(item(), ':')), 1, sub(length(substring(item(), 0, indexOf(item(), ':'))), 2))"


この「選択」アクションによって下図のJSONデータが、、

こうなります。

JSONデータのプロパティの配列を取得する方法については下記の投稿で解説しています。詳しくはこちらを参照してください。
wataruf.hatenablog.com

補足：Apply to eachのインデクサにするためにプロパティの配列を取得する理由

さて、Apply to eachのインデクサにするためになぜプロパティの配列を作る必要があるのでしょうか。それは、Apply to eachのインデクサに使うデータがアレイ形式である必要があるからです。

JSONデータの型はオブジェクト型です。これをインデクサとして使おうとすると、、

エラーになります。データの型を意識しないとここはハマりやすいので覚えておきたいポイントです。

Apply to eachによってJSONデータのValueを更新する

このApply to eachによって、IDごとに内容欄のテキストを結合した値をJSONデータのValueに入力していきます。

Apply to eachのインデクサは前述した通り、JSONデータのプロパティを配列にしたものです。

この配列のことです。つまり、重複を排除したIDの個数だけ繰り返し処理を行います。

Apply to eachで繰り返し処理をする内容は以下の通りです。

  1. インデクサのIDをキーにしてExcelから取得したテーブルの行をフィルタして取得
  2. フィルタして取得した行の内容欄を取得
  3. 内容欄に記載されているテキストを文字列結合する
  4. 文字列結合したテキストを、JSONデータのValueに設定する


繰り返し処理内容のアクションについて解説します。ここでは例としてインデクサのIDが「1」とします。

インデクサのIDをキーにしてExcelから取得したテーブルの行をフィルタして取得

まずは「データ操作」>「アレイのフィルター処理」アクションを使って、テーブルからIDが「1」である要素を取得します。

取得した結果は下図の通りです。

フィルタして取得した行の内容欄を取得

次に、「データ操作」>「選択」アクションを使用して内容欄を取得します。

取得した結果は下図の通りです。

内容欄に記載されているテキストを文字列結合する

次に、内容欄を文字列結合します。区切り文字は要らないので「*次を使用して結合」には null をいれておきます。

ここのポイントは「*次を使用して結合」を空白にしたままではフローを保存できないことです。

ちなみに、空文字を意図してダブルクォーテーション2つをいれると、、

ダブルクォーテーション2つが区切り文字として使われてしまいます。

なので、区切り文字が要らないときは null をいれましょう。

Valueを更新したJSONデータを作成する

文字列結合したテキストをJSONデータのValueに入力します。そのためには setProperty関数を使います。

setProperty(variables('フローが出力する配列'),items('Apply_to_each'),body('結合-内容を結合'))

関数の実行結果は下図の通りです。


注意すべき点は、まだこのアクションの時点では「データ操作」>「作成」アクションによってValueが更新されたJSONデータが作成されただけであり、元のJSONデータが更新されてはいないことです。元のJSONデータを更新するために、変数に格納されたJSONデータを更新する必要があります。

それがこの次のアクションです。

変数に格納しているJSONデータを更新する

変数に格納されたJSONデータを更新するのが「変数」>「変数の設定」アクションです。これで無事、JSONデータが更新されました。

フローの最終的な出力

繰り返し処理を終えて、最終的に出力されるJSONデータがこちらです。

フローをGitHubで公開しました（再掲）

github.com

ちなみに

この課題についてツイートしたところ、これをExcelで問題として取り上げてくださったかたがいました。

課題を借りたつもりが、借りられる側にもなりました。こういうのがTwitterの面白いところだと思います。(｀・ω・´)ｼｬｷｰﾝ

今回は以上です。