创建用于分析项目的 Power BI 报表Create a Power BI report to analyze projects

注意:本文属于将 PowerApps、Microsoft Flow 和 Power BI 与 SharePoint Online 结合使用的系列教程。Note: This article is part of a tutorial series on using PowerApps, Microsoft Flow, and Power BI with SharePoint Online. 请确保已阅读系列介绍,了解总体情况以及相关下载内容。Make sure you read the series introduction to get a sense of the big picture, as well as related downloads.

在此任务中,我们将创建以两个 SharePoint 列表为依据的 Power BI 报表。In this task, we'll create a Power BI report based on the two SharePoint lists. 我们将把列表数据导入 Power BI Desktop 并稍作清理,再进行一些基本数据建模,并创建一组可提供数据信息的视觉对象。We'll bring the list data into Power BI Desktop and clean it up a little, do some basic data modeling, and create a set of visuals that tell us something about the data.

提示:此方案的下载包包括该报表的完成版 (project-analysis.pbix)。Tip: The download package for this scenario includes a finished version of this report: project-analysis.pbix.

快速回顾 Power BI DesktopQuick review of Power BI Desktop

在我们深入介绍如何创建报表之前,让我们先来回顾一下 Power BI Desktop。Before we dive into report creation, let's review Power BI Desktop. 这是一款功能非常强大的工具,具有许多功能。那么,我们将重点概述此任务将用到的工作区。This is a powerful tool, with a lot of features, so we will focus on an overview of the areas that you will use in this task. Power BI Desktop 有以下三个主要工作区或视图:“报表”视图、“数据”视图和“关系”视图。There are three main work areas or views in Power BI Desktop: Report view, Data view, and Relationships view. Power BI Desktop 还包括在单独窗口中打开的“查询编辑器”。Power BI Desktop also includes Query Editor, which opens in a separate window.

下面的屏幕展示了 Power BI Desktop 左侧的三个视图图标,从上到下分别为“报表”、“数据”和“关系”。The following screen shows the three view icons along the left of Power BI Desktop: Report, Data, and Relationships, from top to bottom. 左侧的黄色条表示当前视图;在此示例中,显示的是“报表”视图。The yellow bar along the left indicates the current view; in this case, Report view is displayed. 选择这三个图标中的任何一个即可更改视图。Change views by selecting any of those three icons.

Power BI Desktop 视图

“报表”视图有五个主要区域:The Report view has five main areas:

  1. 功能区:显示与报表和可视化效果相关联的常见任务。The ribbon, which displays common tasks associated with reports and visualizations.
  2. “报表”视图或画布:可用于创建和排列可视化效果。The Report view, or canvas, where visualizations are created and arranged.
  3. “报表页”选项卡区域:位于底部,可用于选择或添加报表页。The Pages tab area along the bottom, which lets you select or add a report page.
  4. “可视化效果”窗格:可用于更改可视化效果、自定义颜色或轴、应用筛选器、拖移字段等。The Visualizations pane, where you change visualizations, customize colors or axes, apply filters, drag fields, and more.
  5. “字段”窗格:可用于将查询元素和筛选器拖到“报表”视图上,或拖到“可视化效果”窗格的“筛选器”区域中。The Fields pane, where query elements and filters can be dragged onto the Report view, or dragged to the Filters area of the Visualizations pane.

Power BI Desktop 选项卡、视图和窗格

“数据”视图有三个主要区域:The Data view has three main areas:

  1. 功能区:下面的屏幕已选择“建模”选项卡。The ribbon, which has the Modeling tab selected below. 在此选项卡上,可以创建计算表和计算列,并对数据模型进行其他更改。On this tab, you create calculated tables and columns, and make other changes to the data model.
  2. 中心窗格:显示选定表的数据。The center pane, which shows data for the selected table.
  3. “字段”窗格:可用于控制字段在报表中的显示方式。The Fields pane, where you control how fields are displayed in your reports.

Power BI Desktop“数据”视图

我们在此任务中不会用到“关系”视图,但可以先将列表数据导入 Power BI Desktop,日后再一探究竟。We don't use the Relationships view in this task, but you can check it out later after we bring the list data into Power BI Desktop.

在“查询编辑器”中,生成查询并转换数据,再将优化后的数据模型加载到 Power BI Desktop 中。In Query Editor, you build queries and transform data, then load that refined data model into Power BI Desktop. “查询编辑器”有四个主要区域:Query Editor has four main areas:

  1. 功能区:包含许多选项,可便于塑造和转换所导入的数据。The ribbon, which has many options for shaping and transforming the data that you bring in.
  2. 左窗格:其中列出了可供选择、查看和塑造的查询。The left pane, where queries are listed and available for selection, viewing, and shaping.
  3. 中心窗格:显示选定查询的数据,可供塑造。The center pane, where data from the selected query is displayed and available for shaping.
  4. “查询设置”窗口:其中列出了查询属性和已应用的数据转换步骤。The Query Settings window, which lists the query's properties and data transform steps that have been applied.

Power BI Desktop 查询编辑器

第 1 步:将数据导入 Power BI DesktopStep 1: Get data into Power BI Desktop

在这一步中,我们将先连接两个列表。In this step, we'll first connect to the two lists. 然后,我们将删除对数据分析无用的列,从而清理数据。Then we'll clean up the data by removing columns we don't need for our data analysis. 我们还将更改剩余一些列的数据类型,以便可以正确进行计算。We'll also change the data types on some of the remaining columns so that calculations work properly. 若要详细了解如何获取和清理 Power BI Desktop 中的数据,请参阅我们引导学习课程中的获取数据部分。For more information on getting and cleaning data in Power BI Desktop, see the Getting Data section in our Guided Learning course.

连接 SharePoint 列表Connect to SharePoint lists

  1. 在 Power BI Desktop 中的“开始”选项卡上,依次单击或点击“获取数据”和“更多...”In Power BI Desktop, on the Home tab, click or tap Get Data, then More…

    获取数据

  2. 在“获取数据”对话框中,依次单击或点击“SharePoint Online 列表”和“连接”。In the Get Data dialog box, click or tap SharePoint Online List, then Connect.

    连接 SharePoint 列表

  3. 输入 SharePoint 网站的 URL,再单击或点击“确定”。Enter the URL for your SharePoint site, then click or tap OK.

    SharePoint 列表 URL

  4. 如果看到以下对话框,请确保登录凭据正确,再单击或点击“连接”。If you get the following dialog box, make sure you're signed in with the right credentials, then click or tap Connect.

    SharePoint 列表凭据

  5. 选中“项目详细信息”和“项目申请”,再单击或点击“编辑”。Select Project Details and Project Requests, then click or tap Edit.

    选择 SharePoint 列表

    此时,列表在查询编辑器中显示为表。The lists are now displayed as tables in Query Editor.

    查询编辑器中的表

从表中删除不必要的列Remove unnecessary columns from the tables

  1. 在左侧导航窗格中,单击或点击“项目详细信息”。In the left navigation pane, click or tap Project Details.
  2. 在中间窗格中,选择“FileSystemObjectType”列,再单击或点击“删除列”。In the middle pane, select the FileSystemObjectType column, then click or tap Remove Columns.

    删除列

  3. 删除“Id”列后的两列,即“ServerRedirectedEmbedURL”和“ContentTypeId”列。Remove the two columns after the Id column: ServerRedirectedEmbedURL and ContentTypeId. 提示:使用 Shift 键选择这两列,再单击或点击“删除列”。Tip: use the Shift key to select both columns, then click or tap Remove Columns.
  4. 删除“PMAssigned”列右侧的所有列(共 22 列)。Remove all columns to the right of the PMAssigned column (a total of 22 columns). 表应与下图一致:The table should match the following image:

    查询编辑器中的“项目详细信息”表

  5. 重复对“项目详细信息”执行的过程,并删除“Approved”列右侧的所有列(共 22 列)。Repeat the process you went through for Project Details, and remove all columns to the right of the Approved column (a total of 22 columns). 表应与下图一致:The table should match the following image:

     <span data-ttu-id="8331c-168">查询编辑器中的“项目申请”表</span><span class="sxs-lookup"><span data-stu-id="8331c-168">Project Requests table in Query Editor</span></span>

更改“项目详细信息”表中列的数据类型Change the data type on Project Details columns

  1. 选择“ProjectedDays”列,依次单击或点击“数据类型:任意”和“整数”。Select the ProjectedDays column, click or tap Data Type: Any, then Whole Number.

    将数据类型更改为整数

  2. 对“ActualDays”列重复执行上一步。Repeat the previous step for the ActualDays column.
  3. 选择“ApprovedDate”列,依次单击或点击“数据类型:任意”和“日期”。Select the ApprovedDate column, click or tap Data Type: Any, then Date.

     <span data-ttu-id="8331c-174">将数据类型更改为日期</span><span class="sxs-lookup"><span data-stu-id="8331c-174">Change data type to date</span></span>

  4. 对“ProjectedStartDate”和“ProjectedEndDate”列重复执行上一步。Repeat the previous step for the ProjectedStartDate and ProjectedEndDate columns.

更改“项目申请”表中列的数据类型Change the data type on Project Requests columns

  1. 选择“EstimatedDays”列,依次单击或点击“数据类型:任意”和“整数”。Select the EstimatedDays column, click or tap Data Type: Any, then Whole Number.
  2. 选择“RequestDate”列,依次单击或点击“数据类型:任意”和“日期”。Select the RequestDate column, click or tap Data Type: Any, then Date.

应用并保存更改Apply and save changes

  1. 在“开始”选项卡上,单击“关闭并应用”关闭查询编辑器,再返回到 Power BI Desktop 主窗口。On the Home tab, click Close and Apply to close Query Editor and go back to the main Power BI Desktop window.

    关闭并应用更改

  2. 依次单击或点击“文件”和“保存”,保存为 project-analysis.pbix。Click or tap File, then Save, and save with the name project-analysis.pbix.

第 2 步:改进数据模型Step 2: Improve the data model

至此,我们已将 SharePoint 列表中的数据拉取到 Power BI Desktop 中。我们将继续进行数据建模。Now that we have the data from our SharePoint lists pulled into Power BI Desktop, we'll move on to data modeling. 数据建模流程可能非常耗时,但可以执行我们将简要介绍的一些有意义操作,以便在 Power BI Desktop 中更深入地挖掘列表数据:Data modeling can be a time-consuming process, but we'll briefly show you some interesting things you can do to get more out of the list data in Power BI Desktop:

  • 更改两个表相互关联的方式Change how the two tables are related to each other
  • 添加日期表,以便我们可以根据工作日进行计算Add a date table so we can make calculations based on weekdays
  • 添加计算列,以计算项目里程碑之间的时间跨度Add calculated columns to calculate timespans between project milestones
  • 添加度量值,以计算项目预计天数与实际天数的差异Add measures to calculate variance in projected versus actual days for a project

完成这些步骤后,我们可以生成可视化效果来利用我们改进的模型。After these steps are complete, we can build visualizations that take advantage of the improvements to our model. 若要详细了解如何在 Power BI Desktop 中进行数据建模,请参阅我们引导学习课程中的建模部分。For more information on modeling data in Power BI Desktop, see the Modeling section in our Guided Learning course.

更改表关系Change table relationships

导入列表后,Power BI Desktop 会根据两个表中的“Id”列创建表关系。When Power BI Desktop brought the lists in, it created a relationship between them based on the Id column in both tables. 表关系实际上应该就是“项目申请”表中的“Id”列和“项目详细信息”表中的“RequestId”列之间的关系。The relationship should actually be between the Id column in the Project Requests table, and the RequestId column in the Project Details table. 让我们来解决此问题:Let's fix that:

  1. 单击或点击“数据”视图图标。Click or tap the Data view icon.

    “数据”视图

  2. 在“建模”选项卡上,单击或点击“管理关系”。On the Modeling tab, click or tap Manage Relationships. 我们将留在“数据”视图中的这个选项卡上,继续执行所有数据建模步骤。We'll stay on this tab in the Data view for all the data modeling steps.

    管理关系

  3. 确保已选择现有关系,依次单击或点击“删除”和“删除”进行确认。Make sure the existing relationship is selected, click or tap Delete, then Delete again to confirm.

    删除关系

  4. 单击“新建”创建其他关系。Click New to create a different relationship.
  5. 在“创建关系”对话框中:In the Create Relationship dialog box:

    1. 对于第一个表,依次选择“项目申请”和“Id”列。For the first table, select Project Requests, and the Id column.
    2. 对于第二个表,依次选择“项目详细信息”和“RequestId”列。For the second table, select Project Details, and the RequestId column.
    3. 屏幕应如下图所示。The screen should look like the following image. 准备就绪后,单击或点击“确定”。When you're ready, click or tap OK.

      创建关系

添加日期表,以简化基于日期的计算Add a date table to make date-based calculations easier

  1. 单击或点击“新建表”。Click or tap New Table.

    新建表

  2. 在编辑栏中键入下面的公式:Dates = CALENDARAUTO()。Enter this formula into the formula bar: Dates = CALENDARAUTO().

    键入了 Dates = CALENDARAUTO() 的编辑栏

    此公式将创建名为“日期”的表,其中包含一个日期列。This formula creates a table called Dates with a single date column. 此表涵盖了其他表中的所有日期,并会在添加其他日期(即刷新数据)时自动更新。The table covers all dates from your other tables and updates automatically if additional dates are added (i.e. if data is refreshed).

    此公式以及本部分中的其他公式使用数据分析表达式 (DAX),这是一种适用于 Power BI 和其他技术的公式语言。This formula and the other ones in this section use Data Analysis Expressions (DAX), a formula language for Power BI and other technologies. 有关详细信息,请参阅 Power BI Desktop 中的 DAX 基本概念For more information, see DAX basics in Power BI Desktop.

  3. 按 Enter,创建“日期”表。Press Enter to create the Dates table.

    “日期”表

向“日期”表添加计算列Add a calculated column to the Dates table

  1. 继续留在“日期”表,单击或点击“新建列”。While still on the date table, click or tap New Column.

    新建列

  2. 在编辑栏中键入下面的公式:IsWeekDay = SWITCH(WEEKDAY(Dates[Date]), 1,0,7,0,1)。Enter this formula into the formula bar: IsWeekDay = SWITCH(WEEKDAY(Dates[Date]), 1,0,7,0,1).

    此公式用于确定“Date”列中的日期是否为工作日。This formula determines whether a date in the Date column is a weekday. 如果日期是工作日,“IsWeekDay”列的值为 1;否则,值为 0。If the date is a weekday, the IsWeekDay column gets a value of 1; otherwise it gets a value of 0.

  3. 按 Enter,将“IsWeekDay”列添加到“日期”表中。Press Enter to add the IsWeekDay column to the Dates table.

    添加“IsWeekDay”列

向“项目详细信息”表添加计算列Add a calculated column to the Project Details table

  1. 依次单击或点击右侧窗格中的“项目详细信息”表和“新建列”。In the right pane, click or tap the Project Details table, then New Column.

    新建列

  2. 在编辑栏中键入下面的公式:Enter this formula into the formula bar:

    ApprovedStartDiff = CALCULATE(SUM(Dates[IsWeekday]),
    
       DATESBETWEEN(Dates[Date],
    
          'Project Details'[ApprovedDate],
    
          'Project Details'[ProjectedStartDate]
    
      )
    
    )
    

    此公式用于计算项目获准时间与预计开始时间的时间跨度(以天为单位)。This formula calculates the difference in days between when a project was approved and when it is projected to start. 由于使用的是“日期”表中的“IsWeekday”列,因此只计算工作日。It uses the IsWeekday column from the Dates table, so it counts only weekdays.

  3. 按 Enter,将“ApprovedStartDiff”列添加到“项目详细信息”表中。Press Enter to add the ApprovedStartDiff column to the Project Details table.

    添加“ApprovedStartDiff”列

向“项目申请”表添加计算列Add a calculated column to the Project Requests table

  1. 依次单击或点击右侧窗格中的“项目申请”表和“新建列”。In the right pane, click or tap the Project Requests table, then New Column.

    新建列

  2. 在编辑栏中键入下面的公式:Enter this formula into the formula bar:

    RequestDateAge = CALCULATE(SUM(Dates[IsWeekday]),
    
       DATESBETWEEN(Dates[Date],
    
          'Project Requests'[RequestDate],
    
          NOW()
    
       )
    
    )
    

    此公式用于计算项目申请时间和当天日期 (NOW()) 的时间跨度(以天为单位)。This formula calculates the difference in days between when a project was requested and today's date (NOW()). 同样,此公式也只计算工作日。Again, the formula counts only weekdays. 此列用于查找待审批时间最长的项目。This column is used to look for the project that has been pending the longest.

  3. 按 Enter,将“RequestDateAge”列添加到“项目申请”表中。Press Enter to add the RequestDateAge column to the Project Requests table.

    添加“RequestDateAge”列

向“项目详细信息”表添加度量值Add a measure to the Project Details table

  1. 依次单击或点击右侧窗格中的“项目详细信息”表和“新建度量值”。In the right pane, click or tap the Project Details table, then New Measure.

    新建度量值

  2. 在编辑栏中键入下面的公式:Enter this formula into the formula bar:

    VarProjectedActual = DIVIDE(
    
        SUM('Project Details'[ActualDays]) - SUM('Project Details'[ProjectedDays]),
    
        SUM('Project Details'[ProjectedDays])
    
    )
    

    此公式用于计算项目实际天数与预计天数的差异。This formula calculates the variance between actual and projected days for a project. 我们添加的是度量值,而不是计算列。因此,无论如何筛选或聚合报表中的数据,系统都会返回正确的结果。We add this as a measure, rather than a calculated column, so it returns the correct results regardless of how the data is filtered or aggregated in a report.

  3. 按 Enter,将“VarProjectedActual”度量值添加到“项目详细信息”表中。Press Enter to add the VarProjectedActual measure to the Project Details table.

    添加“VarProjectedActual”度量值

向“项目申请”表添加度量值Add a measure to the Project Requests table

  1. 依次单击或点击右侧窗格中的“项目申请”表和“新建度量值”。In the right pane, click or tap the Project Requests table, then New Measure.

    新建度量值

  2. 在编辑栏中键入下面的公式:Enter this formula into the formula bar:

    MaxDaysPending = MAXX(
    
        FILTER('Project Requests', 'Project Requests'[Approved]="Pending"),
    
        'Project Requests'[RequestDateAge]
    
    )
    

    此公式根据我们之前定义的计算列查找待审批时间最长的项目。This formula finds the project that has been pending the longest, based on the calculated column we defined earlier.

  3. 按 Enter,将“MaxDaysPending”度量值添加到“项目申请”表中。Press Enter to add the MaxDaysPending measure to the Project Requests table.

    添加“MaxDaysPending”度量值

第 3 步:创建报表可视化效果Step 3: Create report visualizations

现在要执行的这一步,许多人在想到数据分析时都会想起它:即创建可视化效果,以便我们可以发现数据模式。Now we're at the step that many people think of when they think of data analysis: creating visualizations so we can find patterns in our data. 在这一步中,我们将创建四个可视化效果:In this step, we'll create four visualizations:

  • 显示项目预计天数与实际天数的柱形图A column chart that shows projected days versus actual days on projects
  • 显示每个项目的差异的柱形图A column chart that shows the variance for each project
  • 显示待审批时间最长的项目的数据卡A card that shows the project that has been pending the longest
  • 显示项目获准日期与预计开始日期的时间跨度的表A table that shows the time between project approval and projected start date

在 Power BI Desktop 中创建这些报表可视化效果后,我们将会把数据和报表发布到 Power BI 服务,以便可以创建和共享仪表板。After we've created these report visualizations in Power BI Desktop, we'll publish the data and reports to the Power BI service, so we can create and share dashboards. 若要详细了解如何在 Power BI Desktop 中创建报表,请参阅我们引导学习课程中的可视化效果部分。For more information on creating reports in Power BI Desktop, see the Visualizations section in our Guided Learning course.

创建显示预计天数与实际天数的条形图Create a bar chart to show projected versus actual

  1. 单击或点击“报表”视图图标。Click or tap the Report view icon. 在 Power BI Desktop 中执行操作的剩余时间内,我们将继续留在此视图中。We'll stay in this view for the rest of our time in Power BI Desktop.

    “报表”视图

  2. 在右侧的“可视化效果”窗格中,单击或点击“簇状柱形图”。In the Visualizations pane on the right, click or tap Clustered column chart.

    可视化效果 -> 簇状柱形图

  3. 将“字段”窗格内“项目详细信息”中的“PMAssigned”和“Title”拖到“可视化效果”窗格内的“轴”中。Drag PMAssigned and Title from Project Details in the Fields pane to Axis in the Visualizations pane.

    “可视化效果”窗格中的“轴”

  4. 将“字段”窗格内“项目详细信息”中的“ActualDays”和“ProjectedDays”拖到“可视化效果”窗格内的“值”中。Drag ActualDays and ProjectedDays from Project Details in the Fields pane to Value in the Visualizations pane.

    “可视化效果”窗格中的“值”

  5. 此时,可视化效果应如下图所示。The visualization should now look like the following image.

    按 PMAssigned 比较 ProjectedDays 与 ActualDays

  6. 将“字段”窗格内“项目详细信息”中的“Status”拖到“可视化效果”窗格内的“筛选器”区域中,再选中“已完成”复选框。Drag Status from Project Details in the Fields pane to the Filters area of the Visualizations pane, then select the Completed check box.

    按“Status”列筛选

    此时,已在图表中筛选出已完成的项目,这样做是有意义的,因为我们在比较预计天数与实际天数。The chart is now filtered to show only completed projects, which makes sense because we are comparing projected days to actual days.

  7. 单击图表左上角的箭头可以在项目管理器和项目的层次结构中上下移动。Click the arrows in the upper left corner of the chart to move up and down the hierarchy of project managers and projects. 下图展示了如何向下钻取项目。In the following image, you see what the drill down into projects looks like.

    钻取柱形图

创建显示实际天数与预计天数差异的条形图Create a bar chart to show variance from projected

  1. 在刚创建的可视化效果外,单击或点击画布。Click or tap on the canvas outside the visualization you just created.
  2. 在右侧的“可视化效果”窗格中,单击或点击“簇状柱形图”。In the Visualizations pane on the right, click or tap Clustered column chart.

    可视化效果 -> 簇状柱形图

  3. 将“字段”窗格内“项目详细信息”中的“PMAssigned”和“Title”拖到“可视化效果”窗格内的“轴”中。Drag PMAssigned and Title from Project Details in the Fields pane to Axis in the Visualizations pane.

    “可视化效果”窗格中的“轴”

  4. 将“字段”窗格内“项目详细信息”中的“VarProjectedActual”拖到“可视化效果”窗格内的“值”中。Drag VarProjectedActual from Project Details in the Fields pane to Value in the Visualizations pane.

    “可视化效果”窗格中的“值”

  5. 将“字段”窗格内“项目详细信息”中的“Status”拖到“可视化效果”窗格内的“筛选器”区域中,再选中“已完成”复选框。Drag Status from Project Details in the Fields pane to the Filters area of the Visualizations pane, then select the Completed check box.

    按“Status”列筛选

    此时,可视化效果应如下图所示。The visualization should now look like the following image.

    按 PMAssigned 显示 VarProjectedActual

    从此图表中可以看出,Irvin Sayers 所负责项目的实际天数与预计天数差异比 Joni Sherman 多得多。You can see from this chart how much more variability there is for projects that were run by Irvin Sayers versus Joni Sherman. 向下钻取可以按项目显示差异,并确定预计天数是多于还是少于实际天数。Drill in to see the variability by project, and whether the days projected was more or less than the actual days.

    按 Title 显示 VarProjectedActual

  6. 创建其他可视化效果前,先移动并重设已创建可视化效果的大小,让它们适应并排显示。Before we create more visualizations, move and resize the ones you already created, so they fit side-by-side.

    让图表适应并排显示

创建显示待审批时间最长的项目的数据卡Create a card that shows the longest pending project

  1. 在刚创建的可视化效果外,单击或点击画布。Click or tap on the canvas outside the visualization you just created.
  2. 在右侧的“可视化效果”窗格中,单击或点击“数据卡”。In the Visualizations pane on the right, click or tap Card.

    可视化效果 -> 数据卡

  3. 将“字段”窗格内“项目申请”中的“MaxDaysPending”拖到“可视化效果”窗格内的“字段”中。Drag MaxDaysPending from Project Requests in the Fields pane to Fields in the Visualizations pane.

    “可视化效果”窗格中的“字段”

  4. 单击或点击“格式”(滚动油漆刷),再将“边框”设置为“开”。Click or tap Format (paint roller), then set Border to On.

    格式刷 -> 边框

  5. 将“标题”设置为“开”,再添加标题“最长待审批天数”。Set Title to On, then add the title "Max days pending approval".

    添加标题

    此时,可视化效果应如下图所示。The visualization should now look like the following image.

     <span data-ttu-id="8331c-316">最长待审批天数</span><span class="sxs-lookup"><span data-stu-id="8331c-316">Max days pending approval</span></span>

    发布此报表后,如果待审批项目的最长待审批天数达到特定阈值,我们将使用此磁贴触发警报。After we publish this report, we'll use this tile to trigger an alert if the maximum value for a pending project reaches a certain threshold.

创建显示项目获准日期与预计开始日期的时间跨度的表Create a table that shows the time between project approval and projected start date

  1. 在刚创建的可视化效果外,单击或点击画布。Click or tap on the canvas outside the visualization you just created.
  2. 在右侧的“可视化效果”窗格中,单击或点击“表”。In the Visualizations pane on the right, click or tap Table.

    可视化效果 -> 表

  3. 将“字段”窗格内“项目详细信息”中的“PMAssigned”、“Title”和“ApprovedStartDiff”拖到“可视化效果”窗格内的“值”中。Drag PMAssigned, Title, and ApprovedStartDiff from Project Details in the Fields pane to Values in the Visualizations pane.

    “可视化效果”窗格中的“值”

  4. 将“字段”窗格内“项目详细信息”中的“ProjectedStartDate”拖到“可视化效果”窗格内的“筛选器”区域中,再选中除“(空白)”之外的所有日期。Drag ProjectedStartDate from Project Details in the Fields pane to the Filters area of the Visualizations pane, then select all dates except for (Blank).

    按 ProjectedStartDate 筛选

  5. 重设表列的大小,以便可以看到所有数据。Resize the columns of the table so you can see all the data. 此时,可视化效果应如下图所示。The visualization should now look like the following image.

    显示 ApprovedStartDiff 值的表

  6. 依次单击或点击“ApprovedStartDiff”的向下箭头和“平均值”,这样我们就可以看到项目获准日期与预计开始日期的平均时间跨度。Click or tap the down arrow for ApprovedStartDiff, then click or tap Average, so we can see the average duration between project approval and projected start date.

    计算平均值

  7. 依次单击或点击“ApprovedStartDiff”的向下箭头和“条件格式”。Click or tap the down arrow for ApprovedStartDiff again, then click or tap Conditional formatting.

    条件格式

  8. 使用默认设置,再单击“确定”。Use the default settings and click OK.

    “条件格式”选项

    此时,可视化效果应如下图所示。The visualization should now look like the following image.

    已完成的条件格式

    可以看出,Irvin Sayers 负责的项目在获准后很久才启动。As you can see, projects that Irvin Sayers runs tend to start a lot later after approval. 虽然除了分配的经理这一因素之外,可能还有其他因素,但这是值得研究的。There could be factors other than the assigned manager, but this would be worth looking into.

报表部分到此结束,现在应生成以下列数据为依据的完整报表:从 SharePoint 导入并在 Power BI Desktop 中进行清理和建模的数据。That brings us to the end of the report section, and you should now have a complete report based on data imported from SharePoint and cleaned up and modeled in Power BI Desktop. 如果一切按计划进行,报表应如下图所示。If everything went according to plan, your report should look like the following image.

已完成的报表

后续步骤Next steps

本系列教程的下一步是发布 Power BI 项目报表并创建仪表板The next step in this tutorial series is to publish the Power BI project report and create a dashboard.