Power Query 提供了多种方法来创建和加载 Power 查询到工作簿中。 还可以在 查询选项 窗口中设置默认查询加载设置。
提示 若要判断工作表中的数据是否由 Power Query 形成,请选择一个数据单元格,如果出现 查询 上下文功能区选项卡,则从 Power Query 加载数据。
了解你所处的环境 Power Query已很好地集成到 Excel 用户界面中,尤其是在导入数据、使用连接以及编辑“数据透视表”、Excel 表格和命名范围时。 为了避免混淆,请务必随时了解当前处于哪个环境(Excel 或Power Query)。
熟悉的 Excel 工作表、功能区和网格 |
Power Query 编辑器功能区和数据预览 |
|
|
例如,在 Excel 工作表中操作数据与 Power Query 有本质上的不同。 此外,你在 Excel 工作表中看到的已连接数据可能,也可能没有 Power Query 在后台进行工作处理数据。 仅当从 Power Query 将数据加载到工作表或 数据模型 时,才会发生这种情况。
重命名工作表选项卡 最好以有意义的方式重命名工作表选项卡,尤其是有很多工作表选项卡时。 阐明数据工作表与从Power Query 编辑器加载的工作表之间的差异尤其重要。 即使只有两个工作表,一个包含名为 Sheet1 的 Excel 表,另一个是导入该 Excel 表(称为 Table1)创建的查询,也很容易混淆。 最好将工作表选项卡的默认名称更改为对你更有意义的名称。 例如,将 Sheet1 重命名为 DataTable ,并将 Table1 重命名为 QueryTable。 现在,可以清楚地看到哪个选项卡包含数据,哪个选项卡包含查询。
可以从导入的数据创建查询,也可以创建空白查询。
从导入的数据创建查询
这是创建查询的最常见方法。
-
导入一些数据。 有关详细信息,请参阅 从外部数据源导入数据。
-
在数据中选择一个单元格,然后选择 查询 > 编辑。
创建空白查询
你可能想直接从头开始。 可通过两种方法执行此操作。
-
选择 数据> 获取数据 > 从其他源 > 空白查询。
-
选择 数据 > 获取数据 > 启动Power Query 编辑器。
此时,如果你对 Power Query M 的公式语言 非常了解,则可以手动添加步骤和公式。
或者,可以选择 主页 然后在 新建查询 组中选择命令。 执行下列操作之一。
-
选择 新源 以添加数据源。 此命令就像 Excel 功能区中 数据 > 获取数据 命令一样。
-
选择 最近源 从你一直在使用的数据源中进行选择。 此命令就像 Excel 功能区中 数据 > 最近源 命令一样。
-
选择 输入数据 以手动输入数据。 可以选择此命令来试用独立于外部数据源的Power Query 编辑器。
假设查询有效且没有错误,则可以将其加载回工作表或数据模型中。
从 Power Query 编辑器加载查询
在 Power Query 编辑器中,执行下列操作之一:
-
若要加载到工作表,请选择 主页 > 关闭和加载 > 关闭和加载。
-
若要加载到数据模型,请选择 主页 > 关闭和加载 > 关闭和加载到。
在 导入数据 对话框中,选择 将此数据添加到数据模型。
提示 有时,加载到 命令会变暗或禁用。 首次在工作簿中创建查询时,可能会发生这种情况。 如果发生这种情况,请在新工作表中选择 关闭和加载,选择 数据 > 查询和连接 > 查询 选项卡,右键单击查询,然后选择 加载到。 或者,在 Power Query 编辑器功能区上选择 查询> 加载到。
从“查询和连接”窗格中加载查询
在 Excel 中,可能需要将查询加载到另一个工作表或数据模型中。
-
在 Excel 中,选择 数据> 查询和连接,然后选择 查询 选项卡。
-
在查询列表中,找到查询,右键单击查询,然后选择 加载到。 将显示 导入数据 对话框。
-
确定想要如何导入数据,然后选择 确定。 有关使用此对话框的更多信息,请选择问号 (?)。
有几种方法可以编辑加载到工作表中的查询。
在 Excel 工作表中编辑来自数据的查询
-
若要编辑查询,请找到之前从 Power Query 编辑器加载的查询,在数据中选择一个单元格,然后选择 查询 > 编辑。
编辑来自“查询和连接”窗格的查询
如果在一个工作簿中有多个查询并且想要快速找到查询,则可能会发现 查询和连接 窗格更方便使用。
-
在 Excel 中,选择 数据> 查询和连接,然后选择 查询 选项卡。
-
在查询列表中,找到查询,右键单击查询,然后选择 编辑。
从“查询属性”对话框编辑查询
-
在 Excel 中,选择 数据 > 数据和连接 > 查询 选项卡,右键单击查询,然后选择 属性,选择 属性 对话框中的 定义 选项卡,然后选择 编辑查询。
提示 如果在包含查询的工作表中,请选择 数据 > 属性,在 属性 对话框中选择 定义 选项卡,然后选择 编辑查询。
“数据模型”通常包含几个以某种关系排列的表。 通过使用 加载到 命令显示 导入数据 对话框,然后选中 将此数据添加到数据模式l 复选框,将查询加载到数据模型。 有关数据模型的更多信息,请参阅 了解在工作簿数据模型中使用哪些数据源,在 Excel 中创建数据模型,以及 使用多个表创建数据透视表。
-
若要打开“数据模型”,请选择 Power Pivot > 管理。
-
在 Power Pivot 窗口的底部,选择所需表的工作表选项卡。
确认显示正确的表格。 “数据模型”可以包含多个表。 -
请记下表的名称。
-
若要关闭 Power Pivot 窗口,请选择 文件 > 关闭。 它可能需要几秒钟来恢复内存。
-
请选择 数据 > 连接和属性 > 查询 选项卡,右键单击查询然后选择 编辑。
-
在 Power Query 编辑器中完成更改后,选择 文件 > 关闭和加载。
结果
已更新工作表中的查询和“数据模型”中的表。
如果发现将查询加载到“数据模型”所花费的时间比加载到工作表要长得多,请检查 Power Query 步骤,以查看是否使用 包含 运算符筛选文本列或列表结构化列。 此操作会导致 Excel 通过每行的整个数据集再次枚举。 此外,Excel 无法有效地使用多线程执行。 作为解决方法,请尝试使用其他运算符,例如 等于 或 以开头。
Microsoft 已意识到此问题,并正在进行调查。
可以加载 Power Query:
-
添加工作表。 在 Power Query 编辑器中,选择 主页 > 关闭和加载 > 关闭和加载。
-
到数据模型。 在Power Query 编辑器中,选择“开始 > 关闭 & 加载 > 关闭 & 加载到”。
默认情况下,Power Query 在加载单个查询时将查询加载到新工作表,同时将多个查询加载到 DataModel 中。 可以更改所有工作簿或仅当前工作簿的默认行为。 设置这些选项时,Power Query不会更改工作表中的查询结果或“数据模型”的数据和注释。 还可以使用“导入”对话框动态替代查询的默认设置,该对话框在选择“关闭 & 加载到”后显示。
适用于所有工作簿的全局设置
-
在 Power 查询编辑器中,选择 文件 > 选项和设置 > 查询选项。
-
在“ 查询选项 ”对话框左侧的“ 全局”部分下,选择“ 数据加载”。
-
在 默认查询加载设置 部分下,执行以下操作:
-
选择 使用标准加载设置。
-
选择“ 指定自定义默认加载设置”,然后选择或清除“ 加载到工作表 ”或“ 加载到数据模型”。
-
提示 在对话框底部,可以选择 还原默认值 以方便地返回到默认设置。
仅适用于当前工作簿的工作簿设置
-
在 查询选项 对话框中,在左侧的 当前工作薄 部分下,选择 数据加载。
-
执行下列一项或多项操作:
-
在 类型检测 下,选择或清除 检测非结构化源的列类型和标头。
默认行为是检测它们。 如果想要自行调整数据,请清除此选项。 -
在 关系下,选择或清除 首次添加到“数据模型”时创建表之间的关系。
在加载到数据模型之前,默认行为是查找表之间的现有关系,例如关系数据库中的外键,并将其与数据一起导入。 如果希望自行执行此操作,请清除此选项。
-
在 关系 下,在刷新加载到数据模型的查询时,选择或清除更新关系。
默认行为是不更新关系。 刷新已加载到“数据模型”的查询时,Power Query 会发现表之间现有的关系,如关系型数据库中的外键,并更新它们。 这可能会删除在数据导入后手动创建的关系或引入新的关系。 但是,如果要执行此操作,请选择该选项。 -
在 后台数据 下,选择或清除 允许在后台下载数据预览。
默认行为是在后台下载数据预览。 如果想要立即查看所有数据,请清除此选项。
-