PowerPivot および Power Query を使用してプロジェクト用バーンダウン チャートを作成する
(この記事は 2014 年 3 月 24 日に Office Blog に投稿された記事 Creating burndown charts for Project using Power Pivot and Power Query の翻訳です。最新情報については、翻訳元の記事をご参照ください。)
プロジェクト管理では、これまでに終了した作業と今後予定されている作業を比較したり、残りの期間を追跡したりする際に、一般的にバーンダウン チャートが使用されます。この記事では、Excel 2013 と oData ストリームを使用して、90 日前までのすべてのタスクに関するレポートを作成する方法について説明します。また、この例では、Power Query および PowerPivot の主な関数についても説明します。
必要条件
-
- Excel 2013 (スタンドアロン、あるいは Office 2013 Professional Plus や Office 365 ProPlus に含まれているもの)
-
- PowerPivot および Power View が Excel で有効化されていること
-
- プロジェクト データを含む Project Online テナント環境
注 : この例ではバーンダウン チャートの作成に TaskTimePhaseDataSet テーブルを使用していますが、他の任意のテーブルでも作成できます。
Power Query でデータを収集する
Excel で新しいワークブックを作成し、リボンの [Power Query] タブを選択します。[Get External Data] セクションで [From Other Sources]、[From OData Feed] の順に選択します。
[URL] ボックスに OData フィードのアドレスを入力し、[OK] をクリックします。
PWA サイトのアドレスが https://<テナント名>.sharepoint.com/sites/pwa である場合、[OData Feed] 画面で入力するアドレスは https://<テナント名>.sharepoint.com/sites/pwa/_api/Projectdata となります。
たとえば、https://contoso.sharepoint.com/sites/pwa/default.aspx というアドレスを使用している場合は次のようになります。
Excel に、Office 365 アカウントの認証を要求するメッセージが表示されます。[Organizational account] を選択し、認証情報を入力します。
必要なレベルよりも高いレベルの OData ストリームを選択したため、使用可能なすべてのテーブルのリストが Power Query で表示されました。この中から、レポートを作成するテーブルを選択します。今回の例では TaskTimePhaseDataSet を選択し、[Edit] をクリックします。
必要なプロパティのみを選択する
Power Query は、サンプルのデータ セットから約 100 行をダウンロードします。ここからは、これに基づいてクエリを作成します。OData 要求には、必要なプロパティのみが含まれるようにすることが推奨されます。この例ではいくつかのプロパティのみを保持することにして、その他の不要な列を選択して削除します。
このバーンダウン チャートでは、ProjectID、TimeByDay、ProjectName、TaskActualWork、TaskWork の各プロパティのみを使用します。他の列はすべて削除します。
下に示したように、必要なフィールドのみが含まれた新しいデータ ソースができました。
データ モデルに読み込む
データを二重に保存してしまうことを避けるために、必ず、データをデータ モデルでのみ読み込むように設定します。この設定は、右列下部の [Load Settings] で選択できます。
90 日前までの期間に予定されていたタスクを取得する
次に、バーンダウン チャートの対象期間内のタスクのみが含まれるようにフィルタリングします。任意の値を選択できますが、ここでは 90 日間に設定します。
[TimeByDay] ヘッダーの下矢印をクリックし、[Date/Time Filters]、[After] の順に選択します。
次に、ボックスで日付を指定します。設定する日付が現在に近いほど、次の手順での所要時間が短くなります。
注: タスクの数によっては、各手順の間の処理に数分程度かかる場合があります。これは、Power Query がデータの取得と分析に時間を要するためです。
現時点では、TimeByDay の日付が 2014 年 3 月 2 日以降のタスクが Power Query で表示されています。これを、現在の日付に変更します。
このクエリは、クエリ テーブルで変更します。
= Table.SelectRows(RemovedColumns, each [TimeByDay] > #datetime(2014, 3, 2, 0, 0, 0))
このクエリを次のように変更します。
= Table.SelectRows(RemovedColumns, each [TimeByDay] > (DateTime.FixedLocalNow() – #duration(90, 0, 0, 0)))
これで、OData クエリにより、現在の日付から 91 日未満のタスクのみに結果が絞られます。実際の OData クエリには、次のようなフィルターが含まれます。
…ProjectData/Tasks?$filter=( TaskTimePhaseDataSet > datetime’11/10/2013′)’.
期限が今日またはそれ以前のタスクを取得する
これは、前のセクションの手順とほぼ同じですが、今回は [Date/Time Filters] で [Before] を選択します。
次に、下記のクエリを編集します。
= Table.SelectRows(FilteredRows, each [TimeByDay] < #datetime(2014, 6, 15, 0, 0, 0))
このクエリを次のように変更します。
= Table.SelectRows(FilteredRows, each [TimeByDay] < (DateTime.FixedLocalNow() ))
これで、TimeByDay の日付が今日かそれ以前のタスクのみが取得されます。
手順を確認する
この時点で、[APPLIED STEPS] 領域には、適用された手順が 4 つ表示されています。その 1 つはソース、1 つは不要な列のフィルタリング、2 つは各日付のフィルターです。
これで、Power Query での操作は終了しました。[Apply]、[Close] の順にクリックして、フィルタリングされたデータセットをデータ モデルにダウンロードします。
注: 詳細については、Power Query の用語集を参照してください。
PowerPivot で値を計算する
次の手順では、PowerPivot を起動して、バーンダウン レポートで使用する集計フィールドを作成します。
最初に、リボンの [PowerPivot] タブで [Manage] ボタンをクリックします。
PowerPivot が開いたら、[View] セクションの [Calculation Area] ボタンをクリックします。
期限内の合計予定作業
最初に、期限内に予定されていたすべての作業の合計を計算した値を追加します。[Calculation] 領域の左上に次の式を入力します。
Total Planned:=SUMX(CALCULATETABLE(TaskTimephasedDataSet, ALL(TaskTimephasedDataSet[TimeByDay])),TaskTimephasedDataSet[TaskWork])
これまでに実際に終了した作業
次に、これまでに実際に終了した作業の合計値を計算します。Total Planned 式の下のセルに、次の式を入力します。
ActualToDate:=SUMX(FILTER(CALCULATETABLE(TaskTimephasedDataSet, ALL(TaskTimephasedDataSet[TimeByDay])), TaskTimephasedDataSet[TimeByDay]<=MAX(TaskTimephasedDataSet[TimeByDay])),TaskTimephasedDataSet[TaskActualWork])
これまでに予定されていた作業
次に、これまでに予定されていた作業の実際の値を計算します。ActualToDate 式の下のセルに、次の式を入力します。
PlannedToDate:=SUMX(FILTER(CALCULATETABLE(TaskTimephasedDataSet, ALL(TaskTimephasedDataSet[TimeByDay])), TaskTimephasedDataSet[TimeByDay]<=MAX(TaskTimephasedDataSet[TimeByDay])),TaskTimephasedDataSet[TaskWork])
今後予定されている作業
次に、今後予定されている作業の値を計算します。ActualToDate 式の下のセルに、次の式を入力します。
Planned Work:=[Total Planned]-[PlannedToDate]
実際に残っている作業
次に、Total Planned と ActualToDate の式を使用して、実際に残っている作業の量を定義します。Planned Work 式の下のセルに、次の式を入力します。
Actual Remaining Work:=[Total Planned]-[ActualToDate]
これで計算式の定義が終了しました。最後の手順では、Power View を使用してチャートを作成します。
Power View のバーンダウン チャート
PowerPivot のウィンドウを閉じて、[Insert] タブで [Power View] をクリックします。
[Power View Fields] リストで [Actual Remaining Work]、[Planned Work]、[TimeByDay] を選択し、Power View シートでチャート テーブルを作成します。
このテーブルを選択し、[Switch Visualization]、[Other Chart]、[Line] の順にクリックします。
最後に、[Filters] リストを展開して、ここに [ProjectName] プロパティをドラッグします。
これでバーンダウン チャートは完成です。
Project 2013 のバーンダウン チャート
Project 2013 を使用しても、特定のプロジェクトで同様のチャート (およびその他のチャート) を作成できます。バーンダウン チャートでレポートを作成するプロジェクトが 1 つのみの場合は、この方法が簡単です。
Project 2013 で [Report] タブをクリックします。
[Dashboards]、[Burndown] の順にクリックすると、バーンダウン チャートでデータを表示できます。
クライアント レポートの詳細については、過去のブログ記事 (英語) を参照してください。