【PowerApps 】 GroupByとAddColumns関数でピボットテーブルみたいな集計をする

2021

08/01

Updated

2022.10.09

2021

08/01

Published

2021.08.01

/ Mike

【PowerApps 】 GroupByとAddColumns関数でピボットテーブルみたいな集計をする

PowerAppsでピボットテーブルみたいなクロス集計をする時にGroupBy関数とAddColumns関数を使います。そのクロス集計で文字列、個数、合計を求める仕方をステップバイステップで解説しています。

（動画時間：11:57）

中級編【SharePointで、実務で使える業務アプリの作り方：勤怠管理アプリ編】」
Udemyオンラインコースを作りました！
⇒半額になる「ディスカウントリンクページ」へ

1. PowerAsppsのGroupBy関数でできる事
2. PowerAppsのAddColumns関数とShowColumns関数の使い方
3. PowerAppsのGroupBy関数の役割と使い方
4. GroupBy関数でグループ化したデータの使い方
5. GroupBy関数でクロス集計の仕方

PowerAsppsのGroupBy関数でできる事

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

今日は久しぶりにPowerAppsの動画リクエストからです。

「チーム勤務表の作成方法について詳しく教えて頂きたいです。」

ちょっとお名前を言うのが照れちゃうんですが、
ねもねもにゃんにゃんさん、リクエストありがとうございました！

これは以前、PowerAppsで作った勤怠管理アプリを
ご紹介した動画でコメントを頂きました。
⇒「PowerApps で本格的な勤怠管理アプリを作る【テレワーク対策】」

そのチーム勤務表の機能はPowerAppsのGroupBy関数、
AddColumns関数そしてShowColumns関数を使って作成をしています。
今日はそれらの関数をこの実例を使ってご説明します。

まずは、元データの状況を確認します。

使用データベース＝ SharePointリスト
リスト名＝「Tbl_Labor_Records」
「使用フィールド名」：データ型、機能＝

「Date」：日付型、出勤打刻をした日、
「Employee1」：文字列型、従業員名
「Department」：文字列型、所属部署
「Work_Type」：文字列型、出勤形態が記録される
「Hours_Worked 」：数値型、その日に働いた時間

これはフラットなデータ形式です。
さっきの勤務表はエクセルだったら
ピボットテーブルとINDEX関数を組合せてできますが、
PowerAppsだとGroupByやAddColumns、
そしてShowColumns関数をネストして使います。
各関数の働きは分かり難いので
後で実例と共にご説明します。

今回は表の結果を文字列の出勤形態を出していますが、
ピボットテーブルみたいに個数や合計などを計算する
クロス集計もこの方法でできます。
それは後半でお見せします。それでは早速やってみましょう。

PowerAppsのAddColumns関数とShowColumns関数の使い方

このチーム勤務表はデータテーブルコントロールを使っていて、
その下でその作成の再現をしてみます。

メニュバーの「挿入」から、「データテーブル」を追加して、
データソースとして「Tbl_Labor_Records」を選びます。

最初は全てのデータが表示されていますので
「Items」属性で表示するデータを絞ります。

左のドロップダウンには最近の各月曜日の日にちが並んでいて、
そして右のドロップダウンでは部署名が並んでいます。
それらで表示したい週と部署を選ぶのです。

その結果を表示するにはFilter関数が使えます。
変更後の数式がこれです：

データテーブルの「Items」属性＝
Filter(Tbl_Labor_Records,
　　　Date>=DD_月曜日選択3.Selected.Value &&
　　　Date<=DD_月曜日選択3.Selected.Value +6 &&
　　　Department=DD_部署選択3.Selected.Result
)

上の式を説明しますと、
Date列の日付がドロップダウンで選択された日
（これはいつも月曜日です）から
6日後の日までの期間で絞ります。
つまり、一週間です。

そして「&&」の後にもう一つの条件入れて、
Department列を部署選択のドロップダウンで
選択された部署で絞るのです。
ドロップダウンで選択を変えると
表示データがうまく変わります。

数式バー内でカーソルを最後のカッコの外に置くと、
その下に下矢印が出てきます。

それをクリックすると選択可能な列が沢山あるのが分かります。
日付はありますが、しかし、必要な曜日がありません。
それにはAddColumns関数が使えます。
この関数で一時的に新たに列を追加でき、こうします：

データテーブルの「Items」属性＝
AddColumns(
　　　Filter(Tbl_Labor_Records,
　　　　　　Date>=DD_月曜日選択3.Selected.Value &&
　　　　　　Date<=DD_月曜日選択3.Selected.Value +6 &&
　　　　　　Department=DD_部署選択3.Selected.Result
　　　),”曜日”,Text(Date,”ddd”)
)

第二引数に加えたい任意の列名で「”曜日”」にします。
この時点では文字列扱いですの
半角のダブルクォーテーションが必要です。

第三引数に表示させたい結果を求める数式が必要です。
日付を曜日に変換するのにText関数が使え、
「”ddd”」は３文字の曜日を表示する記号です。

また数式バーの下で確認すると
実際に曜日列が追加されているのが分かりますね。
ここには使わない列が沢山ありますので、
必要な列だけにすると後の作業がし易いです。
それにはShowColumns関数を使い、こうします：

データテーブルの「Items」属性＝
ShowColumns(
　　　AddColumns(
　　　　　　Filter(Tbl_Labor_Records,
　　　　　　　　　Date>=DD_月曜日選択3.Selected.Value &&
　　　　　　　　　Date<=DD_月曜日選択3.Selected.Value +6 &&
　　　　　　　　　Department=DD_部署選択3.Selected.Result
　　　　　　),”曜日”,Text(Date,”ddd”)
　　　),”Employee1″,”曜日”,”Work_Type”,”Hours_Wroked”
)

データソースの後に使う列名を書き込むだけです。
このチーム勤務表で必要なのは、従業員名、曜日、
そして勤務形態のWork_Type列だけです。
この記事の最後にクロス集計もお見せしたいので
「Hours_Worked 」も入れておきます。
数式バーの下の結果を見ると
うまく行っているのが分かりますね。

PowerAppsのGroupBy関数の役割と使い方

チーム勤務表の完成形を見ると、
各従業員名は一行ずつ表示され、
残りのデータは横方向に並べたいです。
そこで従業員名を基にグループ化をするのです。
それにはGroupBy関数を使い、こうします：

データテーブルの「Items」属性＝
GroupBy(
　　　ShowColumns(
　　　　　　AddColumns(
　　　　　　　　　Filter(Tbl_Labor_Records,
　　　　　　　　　　　　Date>=DD_月曜日選択3.Selected.Value &&
　　　　　　　　　　　　Date<=DD_月曜日選択3.Selected.Value +6 &&
　　　　　　　　　　　　Department=DD_部署選択3.Selected.Result
　　　　　　　　　),”曜日”,Text(Date,”ddd”)
　　　　　　),”Employee1″,”曜日”,”Work_Type”,”Hours_Wroked”
　　　),”Employee1″,”他”
)

第二引数に基にしたい列「“Employee1”」を選び、
残りは一まとめにし、その任意の名前を「“他”」としておきます。
数式バーで結果を見ると従業員は重複無しで表示され、
残りのデータがサブテーブルに入っています。
ここだと各サブテーブルの中身が見えません。

そこで、この数式を全てコピーして、ボタンを一つ追加し、
その「OnSelect」属性に、Collect関数で
さっきの数式の結果を入れたコレクションを作成します。
Altキーを押しながらそのボタンを押すとコレクションが作成され、
こっちは「他」列にテーブルアイコンが出て、
それをクリックするとサブテーブルの中身が見えます。

従業員毎の残りのデータが全て入っているのが確認できます。
これがGroupBy関数の働きです。

GroupBy関数でグループ化したデータの使い方

しかし、この関数はデータの準備をするだけで、
基にした従業員名しか出てきません。
そこで月曜日から日曜日までの出勤形態を
表示する列を追加したいので、
またAddColumns関数を使い、こうします。

データテーブルの「Items」属性＝
AddColumns(GroupBy(
　　　ShowColumns(
　　　　　　AddColumns(
　　　　　　　　　Filter(Tbl_Labor_Records,
　　　　　　　　　　　　Date>=DD_月曜日選択3.Selected.Value &&
　　　　　　　　　　　　Date<=DD_月曜日選択3.Selected.Value +6 &&
　　　　　　　　　　　　Department=DD_部署選択3.Selected.Result
　　　　　　　　　),”曜日”,Text(Date,”ddd”)
　　　　　　),”Employee1″,”曜日”,”Work_Type”,”Hours_Wroked”
　　　),”Employee1″,”他”
),”（月）”,LookUp(他,曜日=”月曜日”,Work_Type))

AddColumns関数を加えて、
第二引数の列名を「”（月）”」にします。
次の引数の式ですが、
もう一度コレクションの「他」サブテーブルを見ましょう。

この中の「曜日」列に「月曜日」がある勤務形態を持ってきたいので、
LookUp関数が使えます。
次がポイントですが、
LookUp関数のソースがサブテーブルの「他」になります。
そして条件として、「曜日」列に「月曜日」が入った
Work_Typeを持ってくるのです。

　　LookUp(他,曜日=”月曜日”,Work_Type)

するとちゃんと「（月）」列の結果が出てきました。