皆さんこんにちは。
業務ハックLabのよーよんです。
今回は2021/3/27に開催されたOffice365勉強会で登壇した内容のPower BI部分にフォーカスした内容です。
3/27の登壇では「Power Platformで簡単入社対応」と題して、情シス部門で面倒な作業の一つである入社対応を簡単にしようというアプローチで作成したアプリについてお話をしました。(その時の資料はこちらをご覧ください)
アプリの部分について書くと非常に長くなるので今回はPower BIでライセンスの見える化を行った個所について書いていきたいと思います。
- Power BI Desktopでデータセットに接続してみよう。
- Power Queryを使ってデータを成型してみよう。
- リレーションを設定しよう
- レポートを作成しよう
- レポートの発行
- データセットを自動更新しよう
- 作成後の注意
アプリで必要情報が取り込まれているという前提で案内していきますのでご注意ください。
Power BI Desktopでデータセットに接続してみよう。
アプリで利用しているリストをデータベースとして、Power BI Desktopから接続します。
- 「データを取得」から「詳細」をクリックします。
因みにPower BI Desktopを立ち上げた際に表示されるオレンジ色のウィンドウで「データを取得」をクリックしても同じように2の画面が表示されるのでお好みで実施してください。 - 検索欄で「SharePont」と入力し「SharePoint Online リスト」をクリックし、「接続」をクリックします。
- サイトURLに先ほどリストを作成したSharePointサイトのホームをクリックしURL欄に表示されたURLをコピー、Power BIで表示されているウィンドウのサイトURL欄に貼り付け、「OK」をクリックします。
因みに実装の部分で「2.0(ベータ)」を選択するとSharePointリストで「既定のビュー」に定義された列のみを取得することが可能になります。
ですのであらかじめ、SharePointリストの既定のビューで今回のPower BIで集計に使う列のみの表示にしておくことで後程、Power Query画面での
作業工数の低減につながります。ただしPower Query画面での操作も説明をする為、今回は「1.0」を使用します。
Sharepoint Online リスト v2.0 (ベータ版) に接続する(Microsoft Docs) - SharePointリストに接続する為の認証方法の選択画面が表示されます。
基本的には「Microsoftアカウント」を使用します。
「Microsoftアカウント」をクリックし、「サインイン」をクリックします。 - サインインする為のアカウントを選択します。
- パスワードを入力し「サインイン」をクリックします。
- 画面が変わるので「接続」をクリックします。
【補足事項1】
認証の種類は「匿名」「Windows」「Microsoftアカウント」の3つあります。
匿名は文字通り匿名でのアクセスとなります。
Windowsは現在ログインしているWindowsの資格情報を使用、もしくは代替の資格情報を使用してアクセスする形になります。
上記でも案内した通り、基本的にはMicrosoftアカウントを利用していただければ問題ありません。
【補足情報2】
認証を適用する対象レベルの変更も可能です。
下図のように「これらの設定の適用対象レベルの選択」をクリックするとサイトの階層URLが表示され、いずれかを選択することで、ここで設定する認証がどの階層のレベルに割り当てるか指定できます。
これは上位の階層に対して特定の認証方法を設定したくない場合に使用します。
詳細はこちらのDocsを参照していただければと思います。
SharePoint Online リストに接続する - 先ほど追加したリストにチェックを入れ「データの変換」をクリックします。
Power Queryを使ってデータを成型してみよう。
取り込んだデータから不要な列などを削除していきます。 Power BIで視覚化する場合、この作業が非常に重要になります。
ELIST
まずはELISTから成型します。 このリストはライセンス集計に使用します。
- EmployeeNo、EDisplayName、Department、Office、LicenseA~O列を選択し、「ホーム」タブ、「列の削除」で「他の列を削除」をクリックします。 尚、注意点として列を選択する際に基本的にCtrlキーを押しながら選択していきますが列をあけて連続選択する場合(A列を選択後、C~F列を選択するなど)、連続選択列に対してShiftキーを押して選択すると、最初にCtrlキーを押して選択した列が解除されてしまいます。 この場合は連続選択する起点となる列をCtrlキーを押しながら選択したのち、Ctrlキー+Shiftキーを押しながら最終選択列をクリックすると最初に単体で選択した列が解除されず選択状態になったままになります。
- LicenseA~O列を選択し、変換タブから「値の置換」をクリックします。
- 検索する値を「null」、置換後を"-"にして「OK」をクリックします。
- LicenseB列を選択し、変換タブから「値の置換」をクリックします。
- 検索する値を「○」、置換後を「LicenseB」にして「OK」をクリックします。
- 4、5をLicenseC列からO列まで実施します。置換後の値はそれぞれの列名にして下さい。
- カスタム列を追加し、LicenseA~O列をカンマ区切りで結合します。(列名をライセンス結合とする)
※2021/6/15追記
ここの列の結合ですが変換タブの「列のマージ」から簡単にできることがこのお二方のやり取りでわかりました!momo🐱情シスママ (@momo08534512) | Twitter
Akira Takao (@modernexcel7) | Twitter
お二方に感謝感謝です。
方法は
結合したい列をすべて選択して
変換タブから列のマージをクリック
区切り記号を選択しOKをクリックします。
するとこの通り!
列の値が結合されました!
こっちのほうが簡単ですね。 - LicenseA~O列は不要となりますので削除してしまいましょう。
- 先ほど作成したカスタム列「ライセンス結合」を選択し、変換タブの列の分割をクリックし「区切り記号による列の分割」をクリックします。
- 詳細オプションをクリックし分割方向を「行」にして「OK」をクリックします。
もともとEmployeeNoを一意のキーとしてライセンスを横並び(列)で表示していましたがこの処理をすることでEmployeeNoに対し、どのライセンスが割り当たっているかを行単位に変換することができました。 - LLISTより集計に必要な情報を持ってくるため、ELISTとLLISTを「ホーム」タブの「クエリをマージ」を使って結合します。
- ELIST ライセンス結合列-LLIST Licemse列をリレーション、結合の種類は「内部(一致する行のみ)」を選択。
※補足情報 結合の種類によって返ってくる結果は変わってきます。
名前 説明 左外部 マージ画面上部に表示されているテーブルのすべてを残したうえで下部のテーブルは一致したもののみのデータを返す。 右外部 マージ画面上部に表示されているテーブルから下部のテーブルと一致したもの+下部のテーブルのすべてのデータを返す。 完全外部 マージ画面上部に表示されているテーブル、下部のテーブルすべてのデータを返す。 内部 マージ画面上部に表示されているテーブルから下部のテーブルと一致したもののみのデータを返す。 左反 マージ画面上部に表示されているテーブルで下部のテーブルと一致しないもののみのデータを返す。 右反 マージ画面下部に表示されているテーブルで上部のテーブルと一致しないもののみのデータを返す。 - 追加されたLLIST列の矢印マークをクリックします。
- SystemName、Value、Price、NoofLicensesにチェックを入れ「元の列名を...」のチェックを外し、OKをクリックします。
※Value、Price、NoofLicenses列の型が文字列になっている場合は列を選択し、右クリック、メニューから「型の変更」をクリックし「整数」を選択します。 - カスタム列の追加でPrice列とValue列の差を求めます。(列名をCRとする)
- 14で作成したカスタム列の型を10進数に変更します。
LLIST
次はLLISTを成型します。
このリストはライセンス残数の計算に利用します。
- SystemName、License、Value、Price、Vendor、Uses、NoofLicenses列を選択し、「ホーム」タブ、「列の削除」で「他の列を削除」をクリックします。
※Value、Price、NoofLicenses列の型が文字列になっている場合は列を選択し、右クリック、メニューから「型の変更」をクリックし「整数」を選択します。 - カスタム列の追加でコスト合計を求めます。(列名をCostSUMとする)
- 2で作成したカスタム列を選択し、型の変更で10進数に変更します。
OMLIST
次にOMLISTを成型します。
- Title、Models、Career、User、Dep、Office、Tethering、Cflag、SpareFlag列を選択し、「ホーム」タブ、「列の削除」で「他の列を削除」をクリックします。
- OMLISTとMLISTを「クエリをマージ」を使って結合します。
- OMLIST Cflag列-MLIST Title列をリレーション、結合の種類は内部。
- 追加されたMLIST列の矢印マークをクリックします。
- Valueにチェックを入れ「元の列名を...」のチェックを外し、OKをクリックします。
※Value列の型が文字列になっている場合は列を選択し、右クリック、メニューから「型の変更」をクリックし「整数」を選択します。 ここまで処理が終わったら最後に「閉じて適用」をクリックします。
リレーションを設定しよう
取り込んだデータ間でーリレーションの設定をします。
また不要なデータについては非表示しましょう。
ELISTとLLISTのリレーション
レポート画面で複数のデータにまたがったレポートを作成する場合、データ同士でリレーションを組むことでお互いのデータに作用するように設定ができます。
今回はELIST SystemNameとLLIST SystemNameでリレーションが必要となります。
ただし基本的にデータソース間で同一の列タイトルのものはリレーション対象として前段でPower Queryの実行をした際に自動的にリレーションされます。
下記はリレーションが張られていなかった場合を想定しリレーションを組む方法について案内します。
- Power BI Desktop画面右側の一番下のアイコンをクリックします。
- ELISTのSystemNameをLLIST SystemNameにドラッグ&ドロップします。
- データ間に矢印が表示されるので右クリック、プロパティをクリックし、下図の通りになっているか確認します。
データの非表示設定
レポートを作成する際に不要なデータ列が表示されていると作成しにくいので事前に使わないとわかっているものは非表示設定にしておくことをお勧めします。
データごと非表示にする場合はデータソース名横に表示されている目のアイコンをクリックするとデータソースごと非表示にできます。
レポートを作成しよう
では下準備は完成したのでレポートを作成しましょう。
レポートの作り方はあくまで自分の好みなので必ずしもこうしなければならないというものはありません。
今回は勉強会で発表したものを題材に作成をしていきます。
拠点別、部署別のライセンスコスト、使用数(マトリクス)
このマトリクスにはELISTのデータを使用します。
行:SystemName
列:Office
:Department
値:Value
:CR
:NoofLicensesのカウント
※NoofLicensesをカウントにするには下記の手順でできます。
- フィールドの矢印をクリックします。
- 「カウント」をクリックします。
列にOfficeとDepartmentを入れることによってドリルダウンで切り替えができます。
上記の形でそれぞれのフィールドを入れると下図のような感じになります。
上記の完成形のデータと比較すると下記のようなことが異なっていると思います。
- ValueとCRが円表記になっておらず、小数点第2位まで表示されている(数値の書式が違っている)
- フィールドのタイトルが違う
- タイトルがない
- 罫線がない
これらの設定方法についてご案内します。
数値の書式変更
数値の書式を変更することでマトリクスの中に表示されている数値の見え方が変わります。
設定変更の方法は下記の通りです。
- フィールドで書式を変更したいものを選択します。
- 画面上部のメニューに「列ツール」というタブが表示されるので、その中から$アイコンの横にある矢印をクリック、通貨記号が表示されるのでその中から円マークを選択します。
書式が通貨型になり自動的に小数点以下が表示されなくなります。 - 通貨型以外の数値の小数点以下の表記を無くしたい場合は下図の「自動」の部分を「0」にすることで少数点以下無しの表記になります。
フィールドのタイトル変更
マトリクスを選択した状態でフィールドの部分でタイトル部分をダブルクリックをすると名称変更が可能です。
タイトルの追加
タイトルに関しては「書式」で変更ができます。 タイトルをオンにし、タイトルテキストを入力するとタイトルをつけることができます。
罫線の追加
罫線に関しては「書式」で変更ができます。 罫線をオンにするとマトリクスに罫線をつけることができます。
各種ライセンス月額コスト(マトリクス)
このマトリクスにはLLISTのデータを使用します。
行:SystemName
値:CostSUM
各種ライセンス残数(マトリクス)
このマトリクスにはELISTとLLISTのデータ、また残数を算出する為にメジャーを使用します。
行:SystemName(LLIST)
値:SystemNameのカウント(ELIST)
:新しいメジャー(下記参照)
新しいメジャーというのは取り込んだデータを基に新たな集計結果を算出する為の定義です。
わかりやすく言えば集計されたデータ列を追加する感じです。
今回は保有しているライセンス数(LLIST NoofLicenses列)から使用済ライセンス数(ELIST SystemNameのカウント)を引き算するのでメジャーに入れる数式は下記になります。
メジャー = SUM(LLIST[NoofLicenses])-COUNTA(ELIST[SystemName])
また上の画像では残数が「0」のものに対して背景色が赤になっているのがわかると思います。
これは条件付き書式というものを利用しています。
拠点別、部署別のシステムコスト(積上棒グラフ)
このグラフにはELISTのデータを使用します。
軸:Office
:Department
凡例:SystemName
値:Value
軸にOfficeとDepartmentを入れることによってドリルダウンで切り替えができます。
導入システムのコスト割合(円グラフ)
このグラフにはELISTのデータを使用します。
凡例:SystemName
値:Value
携帯総台数(カード)
このカードにはOMLISTのデータを使用します。
フィールド:Titleのカウント
予備機台数(カード)
このカードにはOMLISTのデータを使用します。
またSpareFlag列の○の数のみをカウントする必要がある為、ここでもメジャーを使用します。
フィールド:新しいメジャー
携帯の総台数を見るとTitleのカウントで算出しているのがわかると思います。
ですので考え方としてはSpareFlagに〇が入力されているTitle列をカウントする形になります。
つまり数式は下記のような感じになります。
予備機 = CALCULATE(COUNTA(OMLIST[Title]),OMLIST[SpareFlag] IN { "○" })
携帯月額コスト(カード)
このカードにはOMLISTのデータを使用します。
フィールド:Value
拠点別・部署別携帯コスト(折れ線グラフ+積上棒グラフ)
このカードにはOMLISTのデータを使用します。
共有の軸:Office
:Dep
縦棒:Career
各棒の値:Value
線の値:Titleのカウント
キャリア別コスト(マトリクス)
このマトリクスにはOMLISTのデータを使用します。
行:Career
列:Office
:Department
値:Value
列にOfficeとDepartmentを入れることによってドリルダウンで切り替えができます。
拠点別・部署別携帯保有数(マトリクス)
このマトリクスにはOMLISTのデータを使用します。
行:Model
列:Office
:Department
値:Value
列にOfficeとDepartmentを入れることによってドリルダウンで切り替えができます。
拠点別・部署別テザリングオプション導入数(マトリクス)
このマトリクスにはOMLISTのデータを使用します。
行:Model
列:Office
:Department
値:Tethering
列にOfficeとDepartmentを入れることによってドリルダウンで切り替えができます。
あとは体裁を整えればレポート完成です。
レポートの発行
レポートの設定が完了しましたがまだこの時点ではクラウド上のPower BIにレポートが発行されていません。
画面上部からレポートの発行をしましょう。
データセットを自動更新しよう
クラウド上にレポートをアップするところまでは完了しましたが最後にデータセットの自動更新設定が必要です。
レポートの発行でアップしたワークスペースを開き、更新のスケジュール設定アイコンをクリックします。
更新頻度や時刻などを設定し、適用をクリックすれば設定完了です。
作成後の注意
Power BIにはレポートをWeb公開する設定があります。
これは外部への一般公開のことです。
Power BIでは会社のクリティカルな情報を扱うことが多くなるかと思いますので、間違って一般公開をしてしまわないよう 十分注意をして下さい。
Power BIのWeb公開と抑止についてわかりやすくまとめた記事がありますので参考にして下さい。
如何だったでしょうか?
社内のライセンスやスマホのコストを視覚化することで削るべき部分の把握や報告にかかる工数、人が入社してくるときのコストなどの把握が容易になります。
ライセンスの見える化、皆さんも是非挑戦してみてください。
それでは皆さん良い業務ハックライフを~