优化Power BI中的Power Query合并查询效率，Part 2：合并查询前or后删除多余的列有区别吗？

中讲解了在Power BI中对两个表进行合并查询，数据集大小影响了效率。尤其是在进行合并查询之前删除了不需要的列，可以较大地提升合并查询的效率。但是我们不禁要问：

合并查询之前还是之后删除多余的列，对查询的效率有影响吗？

上一篇文章中提到过，测试中遇到了一个问题，哪怕我将7列数据删掉6列只剩下1列，去合并查询这两个百万行的表，也会超过256MB的内存大小限制，从而使用了页面文件。这就意味着每次刷新时从SQL Server 事件探查器查看时间，会有1-2秒的差异。当你去比较两个合并查询，一个用时50秒，一个用时10秒左右，1-2秒的误差对你判断这两个时间的长短基本造不成影响。然而，当两个查询本身的时间差不多的时候，1-2秒的差异就会引起比较大的麻烦。

为了解决这个问题，我将两个用来合并查询的表的行数删减为30万行，这样，刷新数据时占用的内存就用不了256MB，也就无需使用页面文件，每次相同查询的误差就会降到最低。

我将数据缩减为30万行，并且删减到1列去进行合并查询时，多次测试求了个平均值，所需的时间：

• Progress Report End/25 Execute SQL – 2.4 秒
• Progress Report End/17 Read Data – 0 秒

接下来，我改变了一下查询步骤，首先将所有的7列数据都进行合并查询，然后再将除了A列以外的列删除，M代码如下：

let

Source = Table.NestedJoin(First, {"A"}, Second, {"A"}, "Second", JoinKind.Inner),

#"Expanded Second" = Table.ExpandTableColumn(

Source,

"Second",

{"A", "B", "C", "D", "E", "F", "G"},{"Second.A", "Second.B", "Second.C", "Second.D", "Second.E", "Second.F", "Second.G"}

),

#"Removed Other Columns" = Table.SelectColumns(#"Expanded Second", {"A", "Second.A"}),

#"Counted Rows" = Table.RowCount(#"Removed Other Columns")

in

#"Counted Rows"

多次刷新，求平均值，发现，时间和上面的相同：

• Progress Report End/25 Execute SQL – 2.4 秒
• Progress Report End/17 Read Data – 0 秒

最后，我又测试了另外一种情况：第一个查询，将除了A列以外的列删除，第二个查询全部保留不删除列，然后合并查询，再展开只保留A列。

let

Source = Table.NestedJoin(First, {"A"}, Second, {"A"}, "Second", JoinKind.Inner),

#"Expanded Second" = Table.ExpandTableColumn(Source, "Second", {"A"}, {"Second.A"}),

#"Counted Rows" = Table.RowCount(#"Expanded Second")

in

#"Counted Rows"

结果如下：

• Progress Report End/25 Execute SQL – 2.6 秒
• Progress Report End/17 Read Data – 0 秒

这次查询时间稍微慢一点，但是200毫秒的差异完全可以忽略不计。

因此，我们可以得出结论：

在合并查询后紧接着删除不必要的列，和在合并查询的上一步进行删除不必要的列，没有任何区别。

what？

why？为什么两个7列的表合并查询完再删除多余的列会表现得这么好？

不应该啊。

7列的表合并查询和1列的表合并查询，完全不可同日而语啊。

答案是：

因为我们是在合并查询之后马上进行了删除多余列的操作，所以Power Query编辑器并不会真的将多余的列也进行合并查询，然后再删除这些多余的列。它会“很聪明地”意识到既然这些列在合并查询之后将要被删除，也就是多余的列，那么干脆就别合并查询了，先删除再合并查询，从而节省算力。

就像在以下两篇文章中：

从局部刷新到节省算力，微软在省钱上从不叨叨

双“局部切换”与特朗普的割韭菜

我们总结过的：

节省算力1：在局部标签切换中，提前知晓将要切换的部分，直接进行特定部分切换而不是对整个页面切换。

节省算力2：CALCULATE计算时，提前知晓同一字段的所有筛选器，先进行合并，再进行计算，避免对同一字段重复计算。

节省算力3：计划刷新时，提前知晓将要刷新的数据集与数据量，规划排队，最优调用服务器。

今天我们再加上一条：

节省算力4：合并查询时，提前知晓某些列将要被删除，那么它会将先删除这些列，再进行合并查询，避免无效计算。

关于以上这个Power Query的引擎计算原理在Ben Gribaudo的一系列文章中说的很清楚，可以参考文末的文档。

需要指出的是，有些时候会出现以上测试失灵的情况，或者并没有那么有效，所以还是建议对于多余的列，能早一个步骤删除就早一点删除。

参考文档：

https://bengribaudo.com/blog/2018/02/28/4391/power-query-m-primer-part5-paradigm

https://blog.crossjoin.co.uk/2020/06/03/optimising-the-performance-of-power-query-merges-in-power-bi-part-2/