教程：在 Power BI Desktop 中调整和合并数据

借助 Power BI Desktop，可连接到多个不同类型的数据源，然后调整数据以满足你的需求，使你能够创建可与其他人共享的视觉对象报表。 调整数据意味着转换数据：重命名列或表格、将文本更改为数字、删除行、将第一行设为标题等等。 合并数据意味着连接到两个或多个数据源，根据需要调整它们，然后将其合并到单个查询中。

本教程介绍以下操作：

• 使用 Power Query 编辑器调整数据。
• 连接不同的数据源。
• 合并这些数据源，以及创建要在报表中使用的数据模型。

本教程演示如何使用 Power BI Desktop 来调整查询，其中突出显示了最常见的任务。 有关此处所用查询的更多详细信息，包括如何从头开始创建查询，请参阅 Power BI Desktop 入门。

Power BI Desktop 中的 Power Query 编辑器使用右键单击菜单和“转换”功能区。 大部分可在功能区选择的内容也可通过右键单击项目（如某列）并从所显示的菜单中进行选择。

调整数据

若要 Power Query 编辑器中调整数据，需在 Power Query 编辑器加载并呈现数据时，提供可用于调整数据的分步操作说明。 原始数据源不受影响，将仅调整或整理这一特定的数据视图。

指定的步骤（例如重命名表、转换数据类型或删除列）由 Power Query 编辑器记录。 每当此查询连接到数据源时，Power Query 编辑器都会执行这些步骤，因此数据始终以指定的方式进行调整。 每当你使用 Power Query 编辑器，或任何人使用你的共享查询（如在 Power BI 服务上）时，都会出现此过程。 这些步骤在“应用的步骤”下的“查询设置”窗格中按顺序捕获。 本文会介绍其中的每个步骤。

1. 从 Web 源导入数据。 选择“获取数据”下拉列表，然后选择“Web”。

   

2. 将此 URL 粘贴到“来自 Web”对话框中，然后选择“确定”。

   https://www.fool.com/research/best-states-to-retire
   

   

3. 在“导航器”对话框中，选择 Table 1，然后选择“转换数据”。

   

1. 此时会打开“Power Query 编辑器”窗口。 可以在“应用的步骤”下的“查询设置”窗格中查看目前已应用的默认步骤。

   • 源：连接到网站。
   • 从 HTML 中提取的表：选择表。
   • 提升的标题：将数据的首行更改为列标题。
   • 更改的类型：将作为文本导入的列类型更改为其推断类型。

   

2. 将表名从默认的 Table 1 更改为 Retirement Data，然后按 Enter。

   

3. 现有数据按加权分数排序，如源网页上的方法下所述。 让我们添加一个自定义列来计算不同的分数。 然后，我们将根据此列对表进行排序，以将自定义分数的排名与现有排名进行比较。

4. 在“添加列”功能区中，选择“自定义列”。

   

5. 在“自定义列”对话框的“新列名”中，输入“新分数”。 对于“自定义列公式”，请输入以下数据：

   ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] + [Weather] ) / 8
   

6. 确保状态消息为“未检测到任何语法错误”，并选择“确定”。

   

7. 在“查询设置”中，“应用的步骤”列表现在显示刚刚定义的新的“添加的自定义”步骤。

   

调整数据

使用此查询之前，让我们先执行一些更改以调整数据：

• 通过删除列来调整排名。

  例如，假设“天气”不是结果中的一个因素。 从查询中删除此列不会影响其他数据。

• 修复错误。

  由于我们删除了一个列，因此需要通过更改其公式来调整“新分数”列中的计算。

• 对数据进行排序。

  根据“新分数”列对数据进行排序，并与现有的“排名”列进行比较。

• 替换数据。

  我们将重点介绍如何替换特定值以及如何插入应用的步骤。

这些更改在以下步骤中进行说明。

1. 若要删除“天气”列，请选择该列，在功能区中选择“开始”选项卡，然后选择“删除列”。

   

   注意

   “新分数”的值未发生更改，其原因在于步骤的顺序。 Power Query 编辑器按顺序但彼此独立地记录步骤。 若要以不同的顺序应用操作，可以向上或向下移动每个已应用的步骤。

2. 右键单击某个步骤以查看其上下文菜单。

   

3. 将最后一步“删除列”向上移动到“已添加的自定义”步骤上方。

   

4. 选择“已添加的自定义”步骤。

   请注意，“新分数”列现在显示“错误”而不是计算值。

   

   可采用几种方法来获取每个错误的详细信息。 如果选择该单元格，而未单击“错误”这个词，Power Query 编辑器将显示错误信息。

   

   如果直接选择“错误”这个词，则 Power Query 编辑器将在“查询设置”窗格中创建“应用的步骤”，并显示错误的相关信息 。 由于我们不需要在其他任何位置显示错误信息，因此请选择“取消”。

5. 若要修复错误，需要进行两项更改：删除“天气”列名称并将除数从 8 更改为 7。 可以通过两种方式进行这些更改：

   1. 右键单击“自定义列”步骤并选择“编辑设置”。 此时会打开用于创建“新分数”列的“自定义列”对话框。 如前所述编辑公式，直到如下所示：

      

   2. 选择“新分数”列，然后通过启用“视图”选项卡中的“公式栏”复选框来显示该列的数据公式。

      

      如前所述编辑公式，直到如下所示，然后按 Enter。

      = Table.AddColumn(#"Removed Columns", "New score", each ( [Quality of life] + [Housing cost] + [Healthcare cost and quality] + [Crime rate rate] + [#"Public health/COVID-19 response"] + [Sales taxes] + [#"Non-housing costs"] ) / 7)              
      

   Power Query 编辑器会将数据替换为修改后的值，并且“已添加的自定义”步骤顺利完成，未出现任何错误。

   注意

   还可以通过使用功能区或右键单击菜单来选择“删除错误”，这将删除具有错误的任意行。 但是，在本教程中，我们希望保留表中的所有数据。

6. 基于“新分数”列对数据进行排序。 首先选择最后一个应用的步骤“添加的自定义”以显示最新数据。 然后，选择“新分数”列标题旁边的下拉列表，并选择“降序排序”。

   

   数据现在会根据“新分数”进行排序。 可在列表的任何位置选择应用的步骤，然后继续在序列中此点处调整数据。 Power Query 编辑器在当前选定的应用的步骤后直接自动插入一个新步骤。

7. 在“应用的步骤”中，选择自定义列前面的步骤，即步骤“已删除的列”。 我们将在此替换俄勒冈州“住房成本”排名值。 右键单击包含俄勒冈州“住房成本”值的相应单元格，然后选择“替换值”。 请注意当前选择了哪个已应用的步骤。

   

8. 选择“插入”。

   因为我们要插入一个步骤，所以 Power Query 编辑器提醒我们后续步骤可能会导致查询中断。

   

9. 将数据值更改为“100.0”。

   Power Query 编辑器替换俄勒冈州的数据。 创建新的应用的步骤时，Power Query 编辑器会根据操作对其命名，本例中为“替换的值”。 如果查询中具有多个名称相同的步骤，则 Power Query 编辑器会在每个后续应用的步骤的名称中附加一个递增的数字。

10. 选择最后一个已应用的步骤，然后选择“已排序的行”。

    请注意，数据已根据俄勒冈州的新排名进行了更改。 之所以发生此更改是因为我们在“已添加自定义”步骤前，在正确的位置插入了“已替换值”步骤。

    现已按所需的范围调整了数据。 接下来，让我们连接到其他数据源，然后合并数据。

合并数据

有关各州的那份数据很有趣，而且适用于生成进一步的分析工作和查询。 但是，大多数有关州的数据使用两个字母的州名代码缩写，而不是该州的完整名称。 我们需要某种方式来建立州名及其缩写的关联。

还有另一个公共数据源可提供这种关联，但还需要进行相当多的调整，才能连接到我们的退休表。 若要调整数据，请执行以下步骤：

1. 从 Power Query 编辑器的“主页”功能区中选择“新源”>“Web” 。

2. 输入州缩写的网站地址 https://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations，然后选择“连接”。

   导航器显示网站的内容。

   

3. 选择“美国各州、联邦地区、领地及其他区域的代码和缩写”。

   提示

   要将该表的数据缩减到我们所需的范围，将需要进行大量的调整。 是否有更快或更容易的方法完成以下步骤？ 是，我们可以创建两个表之间的关系并基于该关系调整数据。 以下示例步骤有助于了解如何使用表。 但是，关系有助于快速使用来自多个表的数据。

若要对数据进行调整，请执行以下步骤：

1. 删除首行。 因为它是网页表格创建方式的结果，不是所需的行。 从“主页”功能区中选择“删除行”>“删除前几行” 。

   

   此时显示“删除前几行”对话框。 指定要删除的 1 行。

2. 通过“开始”选项卡或功能区中的“转换”选项卡，使用“将第一行用作标题”将新首行提升为标题。

3. 由于“退休数据”表没有针对华盛顿特区或区域的信息，我们需要将其从列表中筛选去除。 选择“区域 1 的名称和状态”列的下拉列表，然后清除除“州”之外的所有复选框。

   

4. 删除所有不需要的列。 因为我们只需将每个州映射到其两个字母的官方缩写（“区域的名称和状态”和“ANSI”列），因此可以删除其他列。 首先选择“区域的名称和状态”列，然后按住“CTRL”键并选择“ANSI”列。 在功能区的“主页”选项卡中，选择“删除列”>“删除其他列”。

   

   注意

   Power Query 编辑器中应用的步骤的顺序非常重要，可能会影响数据的调整方式。 另外，必须考虑一个步骤对另一个后续步骤可能造成的影响。 例如，如果从应用的步骤中删除某个步骤，则后续步骤的行为可能与最初预期不一样。

   注意

   如果重新调整 Power Query 编辑器窗口的大小以缩小其宽度，部分功能区项会进行简缩，以充分利用可视空间。 在增加 Power Query 编辑器窗口的宽度时，功能区项将展开以充分利用已增加的功能区区域。

5. 重命名列和表。 有几种方式可用于重命名列：首先选择此列，然后选择功能区上“转换”选项卡中的“重命名”，或者右键单击并选择“重命名”。 下图显示了这两个选项，但你只需任选其一。

   

6. 将列重命名为“州名”和“州代码”。 若要重命名表，请在“查询设置”窗格的“名称”中输入州代码。

   

合并查询

现已按所需方式对“州代码”表进行了调整，接下来将这两个表或查询合并成一个表或查询。 由于现有的表是针对数据应用查询后的结果，因此这些表通常称为“查询”。

可通过以下两种主要方式来组合查询：“合并”和“追加”。

• 如果有一列或多列要添加到另一个查询，你可合并这些查询。
• 如果有一行或多行数据要添加到现有查询，你可追加查询。

在本例中，我们需要合并查询：

1. 在 Power Query 编辑器的左窗格中，选择要将其他查询合并到其中的查询。 在本例中为“退休数据”。

2. 从功能区的“主页”选项卡中，选择“合并查询”>“合并查询” 。

   

   系统可能会提示你设置隐私级别，以确保对数据进行合并，且不包括或不传输无需传输的数据。

   随即显示“合并”窗口。 这提示你选择想要合并到所选表中的表格，以及要用于合并的匹配列。

3. 从“退休数据”表中选择“州”，然后选择“州代码”查询。

   选择了匹配列后，会激活“确定”按钮。

   

4. 选择“确定”。

   Power Query 编辑器在查询的结尾创建新列，其中包含与现有查询合并的表（查询）的内容。 合并的查询中的所有列将压缩到该列中，但你可以扩展表，使之包含所需的任何列。

5. 若要展开已合并的表，并选择要包含的列，请选择展开图标 ()。

   展开窗口随即出现。

   

6. 在本例中，我们只需“州代码”列。 选择该列，清除“使用原始列名作为前缀”，然后选择“确定”。

   如果选中了“使用原始列名作为前缀”的复选框，合并的列将命名为“州代码.州代码”。

   注意

   如果要了解如何引入“州代码”表，你可以进行一些试验。 如果不喜欢结果，只需从“查询设置”窗格的“应用的步骤”列表中删除该步骤，你的查询便会返回到应用“展开”步骤之前的状态。 你可以不限次数地任意执行，直到展开过程看起来是你要的方式为止。

   现在，我们在单个查询（表）中组合了两个数据源，每个数据源都已根据需要进行调整。 此查询可以作为有趣数据连接的基础，例如任何州的住房成本统计、生活质量或犯罪率。

7. 若要应用更改并关闭 Power Query 编辑器，请从“主页”功能区选项卡中选择“关闭并应用”。

   转换后的语义模型将在 Power BI Desktop 中显示，可随时用于创建报表。

   

相关内容

有关 Power BI Desktop 及其功能的详细信息，请参阅以下资源：

• 什么是 Power BI Desktop？
• Power BI Desktop 中的查询概述
• Power BI Desktop 中的数据源
• 连接到 Power BI Desktop 中的数据
• Power BI Desktop 中的常见查询任务