使用Table.View加快Power BI Desktop中的数据刷新性能

在Power BI Desktop中使用缓慢的数据源或复杂的Power Query查询有时会令人沮丧：你打开Power Query Editor，进行一些更改，单击关闭及应用，然后等待很长一段时间才能刷新数据。在这篇文章中，将向你展示一种可以将等待时间减少多达50％的技术。它涉及一些相当复杂的M代码，但是可以保证，付出努力是值得的！

让我们看一个简单的例子。假设你有一个很大的JSON文件-对于此测试， 67MB的文件，其中包含随机数据-你想将其加载到Power BI中。然后得到一个看起来像这样的查询：

以下为查询的输出：

在电脑上，此查询大约需要7-8秒才能加载。使用任务管理器查看从文件中读取了多少数据表明Power Query从该文件读取了两次数据。该图表是使用任务管理器中的数据生成的，在x轴上有时间，在y轴上有从文件读取的数据量，两个峰值表明该数据被读取了两次：

为什么？在Power BI Desktop中刷新表时，会发生两种情况：

首先，Power BI必须检查表中存在哪些列。为此，它将运行该表的查询，但要求Power Query引擎过滤该表，以便它返回零行。

其次，再次运行查询，但是这次没有过滤器，因此将返回所有行，这是将数据实际加载到Power BI中的表中的时间。

在查询的第一次运行中，可能会出现问题。如果你使用的是SQL Server这样的数据源，并且没有进行任何复杂的转换，则会进行查询折叠，因此Power BI要求返回表但没有行的请求可以得到非常有效的处理。但是，如果你正在使用无法进行查询折叠的数据源（例如Excel，CSV或JSON文件），或者你进行了复杂的转换而无法进行折叠，那么Power Query可以计算出查询返回的列的唯一方法是通过运行整个查询。上面的示例就是这种情况。

但是，这仅在Power BI Desktop中发生。在Power BI Service中刷新数据集时，查询仅运行一次。

解决方案是欺骗Power Query引擎立即运行这些零行过滤器查询，你可以使用Table.View M函数来实现。其中包括以下步骤：

OverrideZeroRowFilter这一步骤是神奇的地方。它使用Table.View通过截取查询返回的表时发生的情况来覆盖查询折叠行为。Table.View的第二个参数中的记录中有三个字段需要更改：

GetType返回一个表类型，该表类型描述查询输出中存在的列及其数据类型。这里列出的六列是你可以在上面的查询输出的屏幕快照中看到的六列。在此处写的自定义函数时，生成所需的M代码非常容易。

GetRows返回查询可以返回的所有行，即由“更改类型”步骤返回的表。

将前n个过滤器应用于查询返回的表时，将使用OnTake。在这种情况下，代码将查看请求的行数（在count参数中指定），如果该数字为零，则仅返回一个空表，该表的列与“更改类型”步骤相同；如果大于零，则使用Table.FirstN从“更改的类型”中获取请求的实际行数。这意味着，当Power BI执行该零行过滤器时，由于无需返回数据源或执行查询中的任何转换，因此现在可以立即进行。

现在，此版本的查询运行时间为4-5秒，并且Process Monitor现在显示JSON文件仅读取一次，显然，一次读取数据比两次读取要快得多：

这种方法有一个缺点：你必须对你希望查询返回的表模式进行硬编码，并且如果你更改查询以返回不同的列，则必须在最后一步中更新表类型。

我在其他慢速查询中使用了此技术，它对Power BI Desktop的开发体验产生了巨大的影响–关闭Power Query Editor时，一次查询需要五分钟的刷新时间，因此只有两分半钟。

别忘了你还可以采取其他措施来加快慢速查询的开发速度：你绝对应该关闭“允许在后台下载数据预览”选项，并且你可能需要考虑关闭，只要你完全了解其含义，就可以取消数据隐私检查。