1. 引入的装饰器 @ddt

Excel如何将一个工作表的数据自动填充(导入)到另一个工作

在本教程中，你将使用 Power Query 的查询编辑器从包含产品信息的本地 Excel 文件和包含产品订单信息的 OData 源导入数据。 你执行转换与聚合步骤，然后合并两个来源的数据，从而生成一份“每年每种产品年总销售额”报表。

为了执行本教程，您需要 "产品和订单" 工作簿。 在“另存为”对话框中，将文件命名为“产品和订 单.xlsx”。

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

在此任务中，将 "产品"和 "订单 .xlsx " 文件中的产品导入到 Excel 工作簿中。

在“POWER QUERY”功能区选项卡上，单击“从文件”>“从 Excel”。
在“Excel”浏览对话框中，浏览找到或键入要导入或链接到文件的产品和订单 .xlsx 路径。
在“导航器”窗格中，双击“产品”工作表或单击“产品”，然后单击“编辑查询”。 当你编辑查询或连接到新的数据源时，将显示“

注意: 有关如何显示查询编辑器的快速入门视频，请查看本文末尾。
步骤 2：将第一行升级为表列标题

在“查询预览”网格中，表的第一行不包含表列名称。 如要第一行升级为表列标题：

单击数据预览左上角的表图标 ()。
单击“将第一行用作标题”。

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

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

在“查询预览”网格中，选择“产品 ID”、“产品名称”、“类别 ID”和“单位数量”列（使用 Ctrl+Click 或 Shift+Click）。
在“查询编辑器”功能区中，单击“删除列”>“删除其他列”或右键单击一个列标题，然后单击“删除其他列”。

在“Power Query”中执行查询活动时，在“应用的步骤”列表的“查询设置”窗格中创建并列出查询步骤。 每个查询步骤有相应的 Power Query 公式，也称为“M”语言。 

在此步骤中，将“产品”查询导入到您的 Excel 工作簿中。

在“查询编辑器”功能区中，单击“应用和关闭”。 结果将显示在新的 Excel 工作表中。
任务 2：从 OData 源导入订单数据

在“POWER QUERY”功能区选项卡上，单击“从其他文件”>“从 OData 源”。
在“导航器”窗格中，双击“订单”表或单击“订单”，然后单击“编辑”。
注意: 将鼠标悬停在某个表上时，您将看到飞出的表预览。

步骤 2：展开订单详情表

在此步骤中，展开与“订单”表相关的“订单详情”表，将“订单详情”中的“产品 ID”、“单击”和“数量”合并到“订单”表。 “展开”操作将相关表中的列合并到一个主题表。 在运行查询时，将相关表（“订单详情”）中的行合并到主题表（“订单”）的行。

在 Power Query 中，包含指向相关表的链接的列拥有“条目”链接或“表”链接。 “条目”链接导航到单个相关记录，表示与主题表的一对一关系。“表”链接导航到相关的表，表示与主题表的一对多关系。 链接表示关系模型中的数据源的导航属性。 对于 OData 源，导航属性表示使用外键关联的实体。 在数据库中，如 SQL Server 中，导航属性表示数据库中的外键关系。

展开“订单详情”表链接

展开“订单详情”表后，将三个新列和其他行添加到“订单”表中，每项对应嵌套表或相关表中的每行。
在“查询预览”窗格中，滚动到“订单详情”列。
在“展开”下拉菜单中：
单击“（选择所有列）”清除所有列。
单击“产品 ID”、“单价”和“数量”。

注意: 在 Power Query 中，您可以展开与某个列链接的表，还能够在主题表中展开数据前，在链接表的列中执行聚合操作。 

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

在此步骤中，删除除“订单日期”、“产品 ID”、“单价”和“数量”列以外的所有列。 在前一个任务中，您使用了“删除其他列”。 此任务中，您删除所选的列。

在“查询预览”窗格中，选择所有列：
单击第一列（“订单 ID”）。
Shift+单击最后一列（运货商）。
Ctrl+单击“订单日期”、“订单详情.产品 ID”、“订单详情.单价”和“订单详情.数量”列。
右键单击所选列标题，然后单击“删除其他列”。
步骤 4：计算每个“订单详情”行的行合计

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

计算每个“订单详情”行的行合计
在 "查询预览" 窗格中，单击预览左上角的表图标（ ）。
单击“插入列”>“自定义”。
在“插入自定义列”对话框的“自定义列公式”文本框内，输入[订单详情.单价] * [订单详情.数量]。
在“新建列名称”文本框内，输入“行合计”。

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

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

在“预览”网格中，右键单击“订单日期”列，然后单击“转换”>“年份”。
将“订单日期”列重命名为“年份”：
双击“订单日期”列，输入“年份”或
右键单击“订单日期”列、然后单击“重命名”，输入“年份”。    

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

在“查询预览”网格中，选择“年份”和“订单详情.产品 ID”。
右键单击其中一个标题，然后单击“分组依据”。
在“分组依据”对话框中：
在“新建列名称”文本框内，输入“总销售额”。
在“操作”下拉菜单中，选择“求和”。
在“列”下拉菜单中，选择“行合计”。

在将销售数据导入 Excel 之前，将查询命名为“总销售额”：

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

执行每个步骤后，您将拥有通过 Northwind OData 源进行的“总销售额”查询。

在“Power Query”中执行查询活动时，在“应用的步骤”列表的“查询设置”窗格中创建并列出查询步骤。 每个查询步骤有相应的 Power Query 公式，也称为“M”语言。 有关 Power Query 公式语言的详细信息，请参阅了解 Power Query 公式。

步骤 8：禁用将查询下载到 Excel 工作簿

由于“总销售额”查询不代表最终“每年每种产品总销售额”报表，您禁用将查询下载到 Excel 工作簿。 当“查询设置”窗格中的“加载到工作表”选项为“关闭”时，则没有下载此查询的数据结果，但查询仍可以与其他查询结合使用以构建所需的结果。 您了解如何将此查询与下一个任务中的“产品”查询合并。

在“查询设置”窗格中，取消选中“加载到工作表”。
在“查询编辑器”功能区中，单击“应用和关闭”。 在“工作簿查询”窗格中，“总销售额”查询显示“禁用加载”。

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

你可以通过合并或追加查询，使用 Power Query 合并多个查询。 可以在任何表格形状的 Power Query 查询中执行“合并”操作，独立于数据来源的数据源。 有关合并数据源的详细信息，请参阅合并多个查询。

本任务中，使用“合并”和“展开”查询步骤，合并“产品”和“总销售额”查询。

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

在 Excel 工作簿中，导航到“Sheet2”中的“产品”查询。
在“查询”功能区选项卡中，单击“合并”。
在“合并”对话框中，选择“产品”作为主表，选择“总销售额”作为要合并的第二查询或相关查询。 “总销售额”将成为新的可展开列。
如要按“产品 ID”匹配“产品销售总额”和“产品”，从“产品”表选择“产品 ID”列，从“总销售额”表选择“订单详情.产品 ID”列。
在“隐私级别”对话框中：
选择用于两个数据源的隐私隔离级别的“组织”。

安全说明: “隐私级别”防止用户意外合并多个数据源中的数据，可能是专用或组织数据源。 根据查询，用户可能意外将专用数据源中的数据发送到另一个可能恶意的数据源。 Power Query 分析每个数据源，并将其归类到已定义的隐私级别：公共、组织和私有。 

单击“确定”后，“合并”操作将创建一个查询。 查询结果包含主表（“产品”）的所有列，以及包含指向相关表（总销售额）的导航链接的单个列。 “展开”操作将新列从相关表添加到主要或主题表。

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

在 "查询预览" 网格中，单击 "新列" 展开图标（ ）。
在“展开”下拉菜单中：
单击“（选择所有列）”清除所有列。
单击“年份”和“总销售额”。
将这两列重命名为“年份”和“总销售额”。
按“总销售额”降序排序，以了解哪些产品以及在哪些年产品获得最高销售额。
将查询“重命名”为“每种产品销售总额”。

在 Power Query 中执行“合并”查询活动时，在“查询设置”窗格的“应用的步骤”列表创建并列出查询步骤。 每个查询步骤有相应的 Power Query 公式，也称为“M”语言。 有关 Power Query 公式语言的详细信息，请参阅了解 Power Query 公式。

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

在此步骤中，禁用“加载到工作表”选项，将查询加载到 Excel 数据模型，以构建连接到查询结果的报表。 除了将查询结果加载到 Excel 工作表以外，还可以通过 Power Query 将查询结果加载到 Excel 数据模型。 将数据加载到 Excel 数据模型后，您可以使用 Power Pivot 和 Power View

将“每种产品总销售额”查询加载到 Excel 数据模型
在“查询设置”窗格中，取消选中“加载到工作表”，选中“加载到数据模型”。
如要将查询加载到 Excel 数据模型，单击“应用和关闭”。

“每种产品总销售额”最终查询

执行每个步骤之后，您将获得将产品和订单.xlsx 文件与 Northwind OData 源的数据结合起来的“每种产品总销售额”查询。 可以将该查询应用于 Power Pivot 模型。 此外，对 Power Query 中的查询进行更改将修改并刷新 Power Pivot 模型中的结果表。

注意: 仅当您使用 Power Query 加载、编辑或创建新查询时，“查询编辑器”才会显示。  若要在不加载或编辑现有工作簿查询的情况下查看查询编辑器，请从 " Power Query功能区" 选项卡的 "获取外部数据" 部分中，选择 "从其他来源 > 空白查询"。