扩展表列时优化 Power Query

Power BI 简单易用，用户可以快速收集数据并生成有趣而强大的报表，从而做出明智的业务决策，同时用户也可以轻松生成性能不佳的查询。 当两个表以外键方式关联 SQL 表或 SharePoint 列表时，经常会出现这种情况。 (需要说明的是，这个问题并不是 SQL 或 SharePoint 独有的问题，在许多后端数据提取场景中都会出现，尤其是在架构不固定且可自定义的情况下）。在共享通用键的单独表中存储数据本身也没有什么问题，事实上，这是数据库设计和规范化的基本原则。 但这确实意味着有更好的方法来扩展这种关系。

请考虑 SharePoint 客户列表的以下示例。

以及它引用的以下位置列表。

首次连接到列表时，位置将显示为记录。

这些顶级数据通过对 SharePoint API 的单个 HTTP 调用（忽略元数据调用）收集，你可以在任何 Web 调试器中看到这些数据。

展开记录后，就会看到从辅助表联接的字段。

将相关行从一个表扩展到其他表时，Power BI 的默认行为是生成 Table.ExpandTableColumn 调用。 可以在生成的公式字段中看到这一点。 但遗憾的是，此方法为第一个表中的每一行生成对第二个表的单独调用。

这样，主列表中每一行的 HTTP 调用次数都会增加一次。 在上述五行或六行示例中，这似乎并不多，但在 SharePoint 列表达到数十万行的生产系统中，这可能会导致体验显著下降。

当查询达到此瓶颈时，最佳缓解措施是使用经典表联接来避免每行调用行为。 这样，可确保只有一个调用来检索第二个表，其余扩展可以使用两个表之间的公共键在内存中进行。 在某些情况下，性能差异可能很大。

首先，从原始表开始，注意要扩展的列，确保有项目的 ID，以便与之匹配。 通常情况下，外键的名称与附加 ID 列的显示名称相似。 在此示例中，它是 LocationId。

其次，加载辅助表，确保包含 ID，即外键。 右键单击“查询”面板创建新查询。

最后，使用匹配的列名联接两个表。 通常可以先展开列，然后在预览中查找匹配的列，从而找到该字段。

在此示例中，可以看到主列表中的 LocationId 与辅助列表中的 ID 匹配。 UI 将此重命名为 Location.Id 以使列名唯一。 现在，让我们利用这些信息来合并表格。

右键单击查询面板并选择“新建查询>””合并>“”合并查询为新查询“，可看到一个友好的 UI 来帮助合并这两个查询。

从下拉列表中选择每个表查看查询预览。

选择这两个表后，选择逻辑上联接表的列（在本例中，是主表中的 LocationId 和辅助表中的 ID）。 该对话框将将显示使用该外键匹配的行数。 你可能希望对此类数据使用默认联接类型（左外部联接）。

选择“确定”，你将看到一个新查询，这就是联接的结果。 现在展开记录并不表示对后端的附加调用。

刷新此数据只会对 SharePoint 进行两次调用，一次用于主列表，一次用于辅助列表。 联接将在内存中执行，大大减少对 SharePoint 的调用次数。

此方法可用于 PowerQuery 中具有匹配外键的任何两个表。

注意

SharePoint 用户列表和分类也可以作为表访问，并且可以按照上述方式进行联接，前提是用户有足够的权限访问这些列表。