【Power Query性能飙升】：掌握这8个技巧，让数据加载与处理速度飞起来


参考资源链接：[Power Query教程：从入门到深度开发](https://wenku.csdn.net/doc/6412b75bbe7fbd1778d4a016?spm=1055.2635.3001.10343)
# 1. Power Query基础简介

Power Query 是一个数据连接、转换和加载工具，广泛应用于Microsoft Excel和Power BI平台，为数据分析工作提供了强大的支持。通过它的图形化界面和丰富的数据处理功能，用户可以轻松地从各种数据源提取信息，执行数据清洗，以及将数据整合成一致的格式，为后续的数据分析和可视化打下坚实的基础。

Power Query 的核心是M语言，一个专门用于数据转换和数据加载的函数式编程语言。M语言提供了一套完整的函数集，能够对数据进行复杂的操作和变换，帮助用户灵活处理各种数据源中的数据。使用M语言，可以编写高级数据处理逻辑，以满足特定的业务需求。

在本章中，我们将了解如何使用Power Query的用户界面，通过数据连接、数据转换和数据加载等步骤来处理数据。此外，还会介绍Power Query的基本概念和术语，为掌握更高级的数据处理技巧奠定基础。

# 2. 优化Power Query数据加载

### 2.1 数据连接与转换

#### 2.1.1 选择高效的数据源连接

在Power Query中选择一个高效的数据源连接对优化数据加载至关重要。选择合适的数据源连接类型能显著减少数据加载时间，提升整体工作效率。例如，在使用Excel作为数据源时，可以利用现有的数据模型来创建连接，这样可以减少从头开始加载数据的需求。

以Microsoft SQL Server为例，数据库提供了稳定且强大的数据连接。Power Query支持通过ODBC或OLE DB进行连接，但使用原生的SQL Server连接通常会提供更好的性能和更丰富的功能。使用原生连接可以：

- 利用SQL Server的查询优化器
- 仅提取查询结果，而非整个表或视图
- 直接在数据库中执行过滤和转换操作

以下是一个连接到SQL Server数据库的示例：

let
    Source = Sql.Databases("ServerName"),
    DatabaseName = "DatabaseName",
    SourceTable = Sql.Database(Source{0}[Name], DatabaseName)
in
    SourceTable

在这个示例中，首先使用`Sql.Databases`函数连接到SQL Server，然后选择一个特定的数据库，并从中加载数据。

#### 2.1.2 数据类型转换的最佳实践

数据类型转换是数据处理中不可或缺的一步。正确地处理数据类型不仅有助于确保数据的准确性，还可以减少数据处理时间。在Power Query中，数据类型转换发生在数据加载到工作表之前的查询过程中，这可以避免在Excel内部进行昂贵的数据类型转换操作。

最佳实践包括：

- 在转换前，检查和清理数据，确保不会出现错误转换。
- 避免不必要的数据类型转换，只在需要时进行更改。
- 尽可能使用原生的数据类型，比如日期、时间、数字等。
- 使用M语言提供的函数，如`DateTime.ToText`、`Number.ToText`等，来对数据进行格式化。

下面是一个数据类型转换的代码示例：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type text}})
in
    ChangedType

在此示例中，我们使用`Table.TransformColumnTypes`函数将`Table1`中的`Column1`和`Column2`转换为日期和文本类型。这个步骤应根据实际数据情况，针对需要转换的数据列进行。

### 2.2 缓存与刷新策略

#### 2.2.1 利用缓存加速数据处理

当处理大量数据或执行复杂的数据转换时，Power Query的缓存机制可以帮助加速数据处理。缓存是将数据存储在内存中的过程，这样在需要相同数据时，可以从内存中快速获取，而不必从数据源重新加载。在Power Query中，可以对整个查询或者特定的步骤启用缓存。

启用缓存后，Power Query将存储查询结果，直到满足以下条件之一：

- 数据源发生了更改
- 手动刷新查询
- Power Query检测到查询环境变化

以下是如何启用特定查询步骤的缓存：

let
    Source = ...,
    Step1 = ...,
    Step2 = ...,
    Step3 = ...,
    CachedStep3 = Table.Buffer(Step3)
in
    CachedStep3

在这个例子中，`Step3`是需要启用缓存的步骤。通过`Table.Buffer`函数，我们可以强制Power Query缓存此步骤的输出。

#### 2.2.2 理解并控制数据刷新频率

Power Query允许用户设置自动刷新频率，以满足不同的数据需求。在数据快速变化的环境中，频繁的自动刷新可以确保数据的实时性。而在数据更新较慢的情况下，减少自动刷新频率可以节省资源。

设置自动刷新频率的步骤如下：

1. 打开包含Power Query查询的工作簿。
2. 进入“数据”选项卡，然后点击“查询”组中的“高级编辑器”按钮。
3. 在弹出的窗口中，选择你想要修改的查询，然后点击“高级编辑器”。
4. 在M代码编辑器中，可以在代码中找到`Source`步骤，并添加或修改`#shared`部分中的`"WorkbookSettings.xlsx!RefreshInterval"`值来设置刷新频率。
5. 保存并关闭高级编辑器窗口。

示例代码：

let
    Source = ...,
    // 在此步骤中添加或修改共享代码部分
    #"WorkbookSettings.xlsx" = [ ... ],
    #"Shared" = [ ... ],
    #"WorkbookSettings.xlsx" = ...,
    #"Shared" = ..., #shared = [WorkbookSettings = #"WorkbookSettings.xlsx", Shared = #"Shared"],
    // 其他步骤
in
    ...

在上面的代码中，`Source`代表数据源步骤，`#"Shared"`用于存放共享参数，其中包括了`WorkbookSettings.xlsx`文件内定义的刷新间隔设置。

### 2.3 减少数据加载量

#### 2.3.1 使用高级筛选优化数据

当处理大型数据集时，只加载需要的数据变得尤为重要。高级筛选提供了一种有效的方式来限定数据加载范围。在Power Query中，可以通过M语言编写复杂的筛选条件，仅加载符合特定条件的数据行和列。

以下是一个使用高级筛选的示例：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FilteredRows = Table.SelectRows(Source, each [Column1] >= 100 and [Column1] < 200)
in
    FilteredRows

在这个例子中，`Table.SelectRows`函数用于选择`Table1`中`Column1`值在100到200之间的所有行。

#### 2.3.2 排除不需要的列和行

另一个优化数据加载的方式是排除不需要的列和行。在数据加载时，如果一个查询包含不必要的列，这会增加加载时间和内存使用。通过在Power Query中删除这些列和行，可以大大减小数据的体积。

以下是一个删除不需要的列和行的示例：