はじめに
Excel形式で収集した残業データをPower BIでレポート作成するための手順です。
そんなに難しい機能は使わない初心者向け。
teams上のファイルを指定してPowerQueryでの加工、BIでのビジュアル化を想定。
自分用の忘備録としての側面が強いです。
使用するデータ
ある事業所Aでのメンバーの残業をまとめたExcelファイル。
残業が必要な場合に業務分類と時間を入力して管理していると仮定します。みんなが使用するのでteamsのとあるチームに保存しています。
フォーマットはまだまだよくある、人は見やすいけどデータとしてすぐには使用できない状態を想定。
PowerQueryによるデータソース作成
今回はある先ほどのファイルがteams上あるとしてこれをデータソースとします。
データソース取り込み
teams上に保存してあるExcelブックはWebページからのデータインポートです。"Excelブック" を選択しても残念ながらteamsファイルは選べないので注意。これはteams上のチームに保存してあるファイルの実態がSharePointサイト上に保存してあるためです。
そのため、リボンのホームタブ、データ項目からデータを取得→Webを選択します
ポップアップが出てくるのでteams上のファイルリンクをコピペします。
この時、張り付けたURLの.xlsx以降(フラグメント部分)は不要なので削除します。
ちなみにリンクはteams上のファイルからコピーするとよいです。
うまくいかない場合はPower BIでSharePointのデータを利用するを参考にしてください。
コネクタをSharePoint系にするとよいみたいです。先達はあらまほしきことなり。
データソースの指定がうまくいった場合はこんな感じの画面が出てきます。
データのクレンジングと変換
データをBIが使用できる形式にするクレンジング処理を行います。
工程としては
①いらない行(上位)と列を削除
②ヘッダー作成
③いらない行(下位)の削除
です。完成イメージはこんな感じです。
①いらない行(上位)と列を削除
今回残すのは
行：残業時間が入力されている行
列：入力日、年、月、日、氏名、残業が必要になった理由、分類、（ｈ）
です。
行と列の削除はリボンにあるここ
から実施します。
ヘッダーとなる行が一列目かつ一番左側の行からスタートするようにどんどん削除しましょう。
行の削除
列の削除
完了形はこんな感じ
②ヘッダー作成
"一行目をヘッダーとして使用"を選択してヘッダー名称を決定します。
一行目がヘッダーとなった状態
③いらない行(下位)の削除
下位に空白行や無駄な入力があると結合時にゴミとなるので削除します。
まずは行の削除から空白行の削除を選択して空白行を削除。
その月の最終日以下の行をすべて削除してください。
例としてはこんなやつ。時間に合計値が入ってました。
変換が完了したら"閉じて適用"
日付テーブルの作成と適用
PowerBIで日付データを活用する際に必須である日付テーブルを作成します。
日付テーブルはフィールド内のテーブルの日付データを連動させるために必要なものです。
使用することで各テーブルの日付が結びつき、日付によるインタラクティブなビジュアルを実現できます。
日付テーブルを作らずにドはまりしたことがあるので要注意。
作成手順
BIで画面左のデータビューを選択します。
次にテーブルツールタブで新しいテーブルの作成を選択します。
入力欄が選択されているのでここに以下の様にDAX式を入力します。
カレンダー = CALENDAR(DATE(2023,4,1),DATE(2024,3,31))
今回はFY2023における1年分の日付を作成しています。
今ある日付のみほしい場合は
カレンダー = CALENDAR(RIRSTDATE('シート名称'[入力日].[Date]),LASTDATE('シート名称'[入力日].[Date]))
としてください。
また、1年間のデータを表示する際に月で分けると便利なことが多いです。
そこでYYYY年MM月で表示可能な年月メジャーを作成します。
フィールドペインでカレンダーを選択した状態で新しいメジャーを作成します。
メジャーには次のように入力します。
年月 = FORMAT('カレンダー'[Date],"YYYY年MM年")
これで日付テーブルは完成です。こんな感じのテーブルができてるかと思います。
最後にカレンダーテーブルを日付テーブルとしてマークするで日付テーブルとして設定します。
リレーションシップの設定
作成したカレンダーのDateを残業一覧の残業日と結びつけます。
画面左のモデルビューを選択します。
カレンダーテーブルの「Date」をドラッグして残業一覧テーブルの「残業日」でドロップします。
すると以下のようにリレーションシップが形成されます。
以上でBIでのビジュアルを設定するための前処理は完了です。
PowerBIでのデータビジュアライゼーション
白状すると私にはデザインのセンスがありません。そこで今回はイースト株式会社さんのレポートを参考にしました。
このレポートのデザインを基にしてビジュアル化していきます。
完成品
そしてできあがったのがこれ
一つずつ解説します。
個人別残業時間合計(積み上げ縦棒グラフ)
使用する項目
X軸：氏名
Y軸：残業時間の合計
ポイントはデータラベルの表示。グラフだけではわかりにくい残業時間の詳細を数値表示によって把握しやすくしている。データラベルの表示は視覚化ペインの書式設定からデータラベルスイッチをONするだけ。
分類別残業時間合計(集合縦棒グラフ)
使用する項目
X軸：氏名
Y軸：残業時間の合計
ポイントは各項目の塗分け。通常だと規定の一色での表示だが各項目ごとに塗分けている。
塗分け方法は視覚化ペイン書式設定→列→カラーより設定の適用先を該当の項目にしてからカラーを変更するだけ。
氏名テーブル(テーブル)
使用する項目
列：氏名
残業時間
残業可能時間(月間)→DAX式で設定。内容は後述
ポイントはDAX式と条件付き書式。残業時間では当月の残業時間とデータバーを表示。DAX式で定義した残業可能時間(月間)では最大残業可能時間を45時間としてあと何時間残業可能かを表示。また、時間の過多によってアイコンが変化することで残業可能時間が少なくなる場合に注意喚起。
条件付き書式は視覚化ペインの残業時間から条件付き書式を選択することで設定できる。
アイコンの例では、
45~20:緑アイコン
20~10:黄アイコン
10~0 :赤アイコン
に設定。
DAX式について
DAX式は新しいメジャーを算出するための式です。Excelの数式とよく似ています。
今回は残業可能時間を算出するために使用します。 カレンダーテーブル作成に使用していることを忘れていました。
今回は月間の最大残業時間が45時間までとして、あと何時間残業できるのかを表示するメジャー**残業可能時間(月間)**を作成します。
まずリボンから新しいメジャーを選択します。
次に以下の式を入力します。
残業可能時間(月間) = 45 - SUM('2024年1月'[残業時間])
この式は最大の45時間からその月の残業時間の合計を引くことを意味しています。
おわりに
思っていたよりも記事の作成に時間がかかりました。
忘れたころに役に立つといいなー。