使用Power Query将多个数据源组合在一起

使用Power Query将多个数据源组合在一起

在本教程中，将详细介绍如何使用Power Query将多个数据源（本地Excel文件和OData源）组合在一起，以生成“每个产品和年份的总销售额”报表。

任务 1：将产品导入到Excel工作簿

在此任务中，将从“产品”工作簿导入产品信息，并进行必要的数据清洗和转换。

步骤 1：连接到Excel工作簿

1. 创建一个新的Excel工作簿。
2. 选择“数据”>“从文件”>“从工作簿”>“获取数据”。
3. 在“导入数据”对话框中，浏览并找到下载的Products.xlsx文件，然后选择“打开”。
4. 在“导航器”窗格中，双击“产品”表。此时会显示Power Query编辑器。

步骤 2：检查查询步骤

默认情况下，Power Query会自动添加几个步骤。查看“查询设置”窗格中的“已应用步骤”列表，了解详细信息。

1. 右键单击“源”步骤，然后选择“编辑设置”。此步骤是在导入工作簿时创建的。
2. 右键单击“导航”步骤，然后选择“编辑设置”。此步骤是在从“导航”对话框中选择表时创建的。
3. 右键单击“更改类型”步骤，然后选择“编辑设置”。此步骤由Power Query创建，用于推断每列的数据类型。

步骤 3：删除其他列，只显示感兴趣的列

在此步骤中，删除除“产品ID”、“产品名称”、“类别ID”和“单位数量”以外的所有列。

1. 在“数据预览”中，选择“ProductID”、“ProductName”、“CategoryID”和“QuantityPerUnit”列（使用Ctrl+单击或Shift+单击）。
2. 选择“删除列”>“删除其他列”。

步骤 4：加载产品查询

在此步骤中，将“产品”查询加载到Excel工作表中。

1. 选择“开始”>“关闭 & 加载”。查询将显示在新的Excel工作表中。

任务 2：从OData源导入订单数据

在此任务中，将从示例Northwind OData源导入订单数据，并进行必要的数据清洗和转换。

步骤 1：连接到OData源

1. 选择“数据”>“从其他源”>“从OData源”>“获取数据”。
2. 在“OData源”对话框中，输入Northwind OData源的URL。
3. 选择“确定”。
4. 在“导航器”窗格中，双击“订单”表。

步骤 2：展开订单详情表

在此步骤中，将“订单详情”表中的相关列合并到“订单”表中。

1. 在“数据预览”中，水平滚动到“Order_Details”列。
2. 在“Order_Details”列中，选择展开图标。
3. 在“展开”下拉菜单中：

• 选择“(选择所有列)”以清除所有列。
• 选择“ProductID”、“UnitPrice”和“数量”。
• 选择“确定”。

步骤 3：删除其他列，只显示感兴趣的列

在此步骤中，删除除“订单日期”、“产品ID”、“单价”和“数量”列以外的所有列。

1. 在“数据预览”中，选择以下列：

• 选择第一列“OrderID”。
• Shift+单击最后一列“发货人”。
• Ctrl+单击“订单日期”、“订单详情.产品ID”、“订单详情.单价”和“订单详情.数量”列。

2. 右键单击所选列标题，然后选择“删除其他列”。

步骤 4：计算每个“订单详情”行的行合计

在此步骤中，创建“自定义列”，计算每个“订单详情”行的行合计。

1. 在“数据预览”中，选择预览左上角的表图标。
2. 单击“添加自定义列”。
3. 在“自定义列”对话框的“自定义列公式”框中，输入[Order_Details.UnitPrice] * [Order_Details.Quantity]。
4. 在“新建列名”框中，输入“行总计”。
5. 选择“确定”。

步骤 5：转换“订单日期”年份列

在此步骤中，转换“订单日期”列，以列呈现订单日期年份。

1. 在“数据预览”中，右键单击“OrderDate”列，然后选择“转换”>“年”。
2. 将“订单日期”列重命名为“年份”：

• 双击“订单日期”列，输入“年份”或
• Right-Click“OrderDate”列上，选择“重命名”，然后输入“年份”。

步骤 6：按“产品ID”和“年份”对行进行分组

1. 在“数据预览”中，选择“年份”和“Order_Details.ProductID”。
2. Right-Click其中一个标头，然后选择“分组依据”。
3. 在“分组依据”对话框中：

• 在“新建列名称”文本框内，输入“总销售额”。
• 在“操作”下拉菜单中，选择“求和”。
• 在“列”下拉菜单中，选择“行合计”。
• 选择“确定”。

步骤 7：重命名查询

在将销售数据导入Excel之前，请重命名查询：

1. 在“查询设置”窗格中的“名称”框中，输入“总销售额”。

任务 3：合并“产品”和“总销售额”查询

在此任务中，将使用“合并”和“展开”操作组合“产品”和“总销售额”查询，然后将“每个产品的总销售额”查询加载到Excel数据模型中。

步骤 1：将“产品ID”合并到“总销售额”查询

1. 在Excel工作簿中，导航到“产品”工作表选项卡上的“产品”查询。
2. 在查询中选择一个单元格，然后选择“查询”>“合并”。
3. 在“合并”对话框中，选择“产品”作为主表，然后选择“总销售额”作为要合并的辅助查询或相关查询。“总销售额”将成为带有展开图标的新结构化列。
4. 如要按“产品ID”匹配“产品销售总额”和“产品”，从“产品”表选择“产品ID”列，从“总销售额”表选择“订单详情.产品ID”列。
5. 在“隐私级别”对话框中：

• 选择用于两个数据源的隐私隔离级别的“组织”。
• 选择“保存”。

6. 选择“确定”。

步骤 2：展开合并列

在此步骤中，展开名称为NewColumn的合并列，以在“产品”查询中创建两个新列：“年份”和“总销售额”。

1. 在“数据预览”中，选择“新建列”旁边的“展开”图标。
2. 在“展开”下拉列表中：

• 选择“(选择所有列)”以清除所有列。
• 选择“年份”和“总销售额”。
• 选择“确定”。

3. 将这两列重命名为“年份”和“总销售额”。
4. 选择“按总销售额降序排序”。
5. 将查询重命名为“每种产品销售总额”。

步骤 3：将每种产品总销售额查询加载到Excel数据模型

在此步骤中，将查询加载到Excel数据模型中，以便生成连接到查询结果的报表。将数据加载到Excel数据模型后，可以使用Power Pivot进一步进行数据分析。

1. 选择“开始”>“关闭 & 加载”。
2. 在“导入数据”对话框中，确保选择“将此数据添加到数据模型”。

总结

通过以上三个任务，我们成功地将本地Excel文件和OData源中的数据组合在一起，生成了“每个产品和年份的总销售额”报表。此查询已应用于Power Pivot模型，对查询的任何更改都将修改并刷新数据模型中的结果表。