优化Power BI中的Power 优化Power BI中的Power Query合并查询效率，Part 1：通过删除列来实现

陈学谦

发布于 2020-06-17 15:09:05

4.6K0

发布于 2020-06-17 15:09:05

文章被收录于专栏：学谦数据运营

本篇文章主体部分为翻译Chris Webb的一篇文章。

合并查询在Power Query中是很成熟的应用，相当于SQL中的各种JOIN（抽时间会写几篇SQL的join，算是SQL的小核心）。但同时，在Power Query中合并查询是一个常见的影响刷新效率的因素。在我的工作中，经常会遇到对一些非文件夹性质的数据源进行合并查询操作，所以我一直在想，有没有办法可以对其进行优化。最近我正好做了一些测试，希望这些结果能够帮助到大家。

以下是我的测试数据源，只有一个CSV格式的文件，100万行7列数字格式的数据A, B C, D, E, F 和G:

在本次测试当中，我使用了SQL Server 事件探查器去计算刷新的时间。以下两个事件的持续时间是我们关注的重点：

Progress Report End/25 Execute SQL
Progress Report End/17 Read Data

第一个我想搞清楚的问题是：

表中列的数量是否影响合并查询时的效率？

首先，我对这个CSV文件创建了两个连接，按照惯例，将第一行转为标题，将7列数字全都定义为整数格式。M代码也是非常简洁明了：

let
  Source = Csv.Document(
    File.Contents("C:\NumbersMoreColumns.csv"), 
    [Delimiter = ",", Columns = 7, 
     Encoding = 65001, QuoteStyle = QuoteStyle.None]
  ),
  #"Promoted Headers" = 
   Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers", 
    {
      {"A", Int64.Type}, 
      {"B", Int64.Type}, 
      {"C", Int64.Type}, 
      {"D", Int64.Type}, 
      {"E", Int64.Type}, 
      {"F", Int64.Type}, 
      {"G", Int64.Type}
    }
  )
in
  #"Changed Type"

将两个查询都取消加载，然后建立第三个查询，使用Table.NestedJoin函数将两个查询进行内联结：

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"}
  )
in
  #"Expanded Second"

当我刷新这个查询时，在SQL Server 事件探查器中可以看到两个过程的持续时间：

Progress Report End/25 Execute SQL – 40 秒
Progress Report End/17 Read Data – 56 秒

相当慢。

但是如果我们合并的表都只有一列呢，还会像合并7列那么慢吗？

为了这样测试，我在两个查询中又添加了一个步骤，删除B-G列，只剩下A列：

let
  Source = Csv.Document(
    File.Contents("C:\NumbersMoreColumns.csv"), 
    [Delimiter = ",", Columns = 7, 
     Encoding = 65001, QuoteStyle = QuoteStyle.None]
  ),
  #"Promoted Headers" = 
   Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Promoted Headers", 
    {
      {"A", Int64.Type}, 
      {"B", Int64.Type}, 
      {"C", Int64.Type}, 
      {"D", Int64.Type}, 
      {"E", Int64.Type}, 
      {"F", Int64.Type}, 
      {"G", Int64.Type}
    }
  ),
  #"Removed Other Columns" = 
   Table.SelectColumns(#"Changed Type", {"A"})
in
  #"Removed Other Columns"

接着，我又重新建了一个查询，只合并两个表的一列：

let
  Source = Table.NestedJoin(
   First, {"A"}, Second, {"A"}, 
   "Second", JoinKind.Inner),
  #"Expanded Second" = 
   Table.ExpandTableColumn(Source, "Second", {"A"}, {"Second.A"})
in
  #"Expanded Second"

查询结束后，SQL Server 事件探查器显示：

Progress Report End/25 Execute SQL – 9 秒
Progress Report End/17 Read Data – 1 秒

很明显，快多了。

不过我转念一想：如果是直接查询的表的大小影响了性能，而不是由于合并查询呢？所以，我在合并查询的最后一步又添加了一步：

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"