当前位置： 首页 > java >正文

Power Query精通指南4：M语言（查询结构与值系统）、查询优化、自动刷新

java 2025/5/4 5:57:10

文章目录

    • 十五、M 语言的核心架构：值系统
      • 15.1 值类型概述
      • 15.2 表（Table）
      • 15.3 列表（List）
        • 15.3.1 直接输入值来创建列表
        • 15.3.2 使用范围表达式创建列表、筛选数据
        • 15.3.3 从表列中提取列表
        • 15.3.4 嵌套列表
        • 15.3.5 将列表转换为表
      • 15.4 记录（Record）
        • 15.4.1 创建
        • 15.4.2 将记录转换为表
        • 15.4.3 记录访问和字段访问
          • 15.4.3.1 通过索引访问记录
          • 15.4.3.2 按条件访问记录
        • 15.4.4 从表中提取记录
          • 15.4.4.1 通过添加索引列实现
          • 15.4.4.2 使用下划线（_）简化操作
      • 15.5 Value
      • 15.6 二进制文件（Binary）
      • 15.7 错误处理
      • 15.7 函数（Function）
        • 15.7.1 使用#shared查看所有M函数
        • 15.7.2 自定义函数
          • 15.7.2.1 函数定义方式
          • 15.7.2.2 指定参数类型
          • 15.7.2.3 可选参数（默认值）
      • 15.8 关键词（Keyword）
        • 15.8.1 基元值关键字
        • 15.8.2 type关键字
          • 15.8.2.1 使用Table.Schema检测数据类型
          • 15.8.2.2 设置数据类型
          • 15.8.3 #table 关键字
    • 十六、理解 M 语言
      • 16.1 查询结构
        • 16.1.1 查询结构
        • 16.1.2 标识符与命名规则
        • 16.1.3 代码注释
      • 16.2 查询计算
        • 16.2.1 延迟计算与查询折叠
        • 16.2.2 查询计划
      • 16.3 迭代器（逐行计算）
        • 16.3.1 循环函数
        • 16.3.2 关键词 each 和 _
          • 16.3.2.1 创建新列
          • 16.3.2.2 处理列表
          • 16.3.2.3 访问嵌套属性
          • 16.2.3.4 使用限制
      • 16.4 动态数据处理技巧
        • 16.4.1 表索引时使用索引数字而非字段值
        • 16.4.2 错误保护
        • 16.4.3 固定动态列表的类型
          • 16.4.3.1 未显式定义数据类型
          • 16.4.3.2 显式定义数据类型
          • 16.4.3.3 动态提取所有字段名
    • 十九、查询优化
      • 19.1 查询设置
        • 19.1.1 全局设置
        • 19.1.2 当前文件设置
      • 19.2 Power Query 中的缓存机制与应用
        • 19.2.1 缓存的作用与原理
        • 19.2.2 案例一：强制计算（生成随机数）
        • 19.2.2 案例二：多文件处理时缓存中间结果
          • 19.2.2.1 案例背景
          • 19.2.2.2 对比测试
      • 19.3 处理响应滞后
        • 19.3.1 响应滞后的成因
        • 19.3.2 优化策略：临时暂存数据法
        • 19.3.3 SQL数据处理原始方案（响应滞后）
        • 19.3.4 重构解决方案
      • 19.4 处理公式防火墙（见17.5章节）
    • 二十、Excel自动刷新技术指南
      • 20.1 Excel 刷新设置
      • 20.2 使用宏实现自动刷新
        • 20.2.1 刷新单个连接
        • 20.2.2 按特定顺序刷新
          • 20.2.2.1 录制宏。
          • 20.2.2.2 修改宏代码，显式指定每个连接的刷新顺序。
        • 20.2.3 刷新所有Power Query查询
        • 20.2.4 同步刷新问题
      • 20.3 Power BI 中的计划刷新

全文参考《精通 Power Query》， 点此下载本文所有示例文件。

十五、M 语言的核心架构：值系统

  值系统是 Power Query 公式语言（M 语言）的基础，它定义了数据如何被表示、存储和操作。在使用 Power Query 进行数据处理时，理解其底层的值系统至关重要的，下面将进行详细的介绍 。

15.1 值类型概述

Power Query 中的值可以分为两大类：基元值和结构值。

  • 基元值：这是最基本的数据类型，包括二进制、日期、时间、数字、文本、逻辑值、null等。
  • 结构值：这些是由基元值构造而成的复杂类型，如表（Table）、列表（List）、记录（Record）等，以彩色字体显示。结构值是 Power Query的独有设计，使得 Power Query 能够高效地处理基于行、列或结构化的数据转换。
    在这里插入图片描述

15.2 表（Table）

  表是 Power Query 中最常用的数据结构之一，它以行和列的形式组织数据，并包含元数据（如列名和数据类型）。打开第 15 章 示例文件"Power Query Values.xlsx”，创建空白查询，在公式栏中输入=Excel.CurrentWorkbook()，可以看到工作簿中有一个“Table”：

在这里插入图片描述

表的主要特点如下：

  • 表支持丰富的数据转换操作，如筛选、排序、分组、添加自定义列等。
  • 表中的每一行可以被视为一个记录（Record），每一列可以被视为一个列表（List）。
  • 表的创建通常通过函数实现，例如：
    1. #table：从 columns 和 rows 创建表值
      在这里插入图片描述
    2. Table.FromColumns：将多个列表作为列合并成一个表，其语法为Table.FromColumns({列表1, 列表2, ...})
      在这里插入图片描述
    3. Table.FromList和Table.FromRecords：分别用于将列表或记录转为表。
      在这里插入图片描述
  • 使用{}可以索引行数据，使用[]可以索引列数据，组合起来可以索引任意单元格数据。
    在这里插入图片描述

15.3 列表（List）

  在 Power Query 中，列表（List） 是一种一维的有序集合，它可以包含任意类型的数据，包括数字、文本、日期、甚至其他列表（嵌套列表）。列表可以看作只有一列的表格，其主要操作有：

  • 使用{}索引列表中的元素

  • 使用 List.Combine 函数将多个列表组合成一个列表。

    = List.Combine({{1, 2, 3}, {4, 5, 6}})

  • 使用 Table.FromRows 函数将嵌套列表转换为表

    = Table.FromRows({{1, "Ken"}, {2, "Miguel"}, {3, "John"}}, {"ID", "Name"})

  • 使用 List.Sort 函数对列表进行排序。

  • 使用 List.Distinct 函数去除列表中的重复项。

15.3.1 直接输入值来创建列表

  可以使用大括号 {} 来定义一个列表，元素之间用逗号分隔。如果列表中包含文本元素，必须用双引号将文本括起来，例如：

={1, 2, 3, 4, 5}
={1,465,"M","Data Monkey",{999,234}}
= {"Puls,Ken","Escobar,Miguel"}

在这里插入图片描述

15.3.2 使用范围表达式创建列表、筛选数据

除此之外，还可以使用范围表达式 创建连续的数字列表或字母列表。例如：

={1..10}  		// 创建从 1 到 10 的连续数字列表
={"A".."Z"}     // 创建从 A 到 Z 的连续字母列表

  需要注意的是，范围表达式仅支持单个字符的字母列表，不支持多字符的范围（如 {"AA".."ZZ"}）。可利用此技巧进行数据筛选：

提取字符类型M函数
提取数字Text.Select([文本数据],{"0".."9"})
提取大写英文字符Text.Select([文本数据],{"A".."Z"})
提取小写英文字符Text.Select([文本数据],{"a".."z"})
提取全部英文字符Text.Select([文本数据],{"A".."z"})
提取全部中文字符Text.Select([文本数据],{"一".."龟"})

  1. 去除大写英文：下表中，如果只想要中文名，可以在添加自定义列时使用Text.Remove函数，去掉所有大写英文字母。

    姓名=Text.Remove([客户],{"A".."Z"})

      Text.Remove 的参数有两个，第一个是文本，第二个是要移除的字符，可以是文本或者是文本的列表。{"A".."Z"}就是生成了一个从A到Z的列表，只要是大写字母，就将其中移除。最终效果为：
    在这里插入图片描述

  2. 去除所有英文字符：把大写的"Z"替换成小写的"z"就行：

    姓名=Text.Remove([客户],{"A".."z"})

  3. 去除所有中文字符：

    英文名=Text.Remove([客户],{"一".."龟"})

    在这里插入图片描述

      Power query 的中文字符以 Unicode 连续储存，"一"的 Unicode最小，正常使用的汉字中，"龟"的 Unicode 最大，因此{“一”…“龟”}就包含了所有正常使用的汉字列表。

  4. 复合提取：如果有更多种类的文本数据不规则的堆放在一起，想把联系方式提取出来，第二个参数还可以这样写：

    联系方式=Text.Remove([客户],{"A".."z","一".."龟"})

    在这里插入图片描述

  5. 直接提取：如果字符种类很多，更加杂乱，用Text.Remove 就有点麻烦。此时可以使用Text.Select 函数直接提取我们需要的部分：

    联系方式=Text.Select([客户],{"0".."9"})

    在这里插入图片描述

15.3.3 从表列中提取列表

可以提取表的某一列数据，将其转换为列表。例如，有一个名为 Sales 的表：

在这里插入图片描述
如果只想提取其中“Inventory Item”列的值，可以：

  1. 从【应用的步骤】窗格中删除“Changed Type”步骤
  2. 右击“Inventory Item”列【深化】，这将通过引用列名的方式（Source[Inventory Item]），此列转为列表
    在这里插入图片描述

除了此种方式，还可以使用 Table.Column 函数将表列转为列表。例如：

// 第一个参数为表，第二个参数为要提取的表列的名称。
= Table.Column(Source, "Inventory Item")
15.3.4 嵌套列表

在公式栏中输入以下公式，创建一个嵌套列表：
在这里插入图片描述

15.3.5 将列表转换为表

将列表转换为表非常容易。只需单击【列表工具】选项卡左上角的【到表】按钮，将弹出以下对话框：

在这里插入图片描述
在这里插入图片描述
  如果是嵌套列表（比如上一节的），选择【转换】【到表】仍然返回一个只有一列的列表，直接展开还是单列的表，可见扩展嵌套列表不会改变其方向。

在这里插入图片描述
使用 List.Combine函数，结果也是一样：

在这里插入图片描述
正确的做法是使用Table.FromRows 函数：

在这里插入图片描述

15.4 记录（Record）

15.4.1 创建

  记录可以被视为一个只有一行的表，它包含多个字段（Field），每个字段都有一个唯一的名称和一个值。 例如：

=[EmployeeID=1,EmployeeName="Fred"]
  • 每个字段名和值之间用等号 = 分隔
  • 字段名之间用逗号分隔
  • 整个记录用方括号 [] 包裹，使用[Field_Name]可以索引字段内容

可以将多个记录组合成一个列表，每个记录用逗号分隔，并用大括号 {} 包裹。例如：
在这里插入图片描述

15.4.2 将记录转换为表

记录本身的功能有限，通常需要将其转换为表以便进行更复杂的数据操作。

  1. 单个记录转换为表： 使用“转换”选项卡中的“到表”功能。
    在这里插入图片描述
    通常情况下，希望在顶部显示字段名，并在第一行显示值，可以通过转置表然后将第一行提升为标题来实现：
    在这里插入图片描述

  2. 多个记录转换为表：转换方式有两种。

    • 使用【到表】功能:先使用【到表】功能将记录转换为表，再进行展开：
      在这里插入图片描述
  • 使用函数：直接使用 Table.FromRecords 函数将记录列表转换为表，该函数会自动将记录中的字段名作为列名，记录的值作为行数据。
    在这里插入图片描述
15.4.3 记录访问和字段访问

以下图所示的表格为例，使用"Changed Type"步骤将其进行数据类型转换：

在这里插入图片描述

15.4.3.1 通过索引访问记录

在这里插入图片描述

使用字段名可进一步访问记录中的特定字段，例如：

在这里插入图片描述

15.4.3.2 按条件访问记录

  在 Power Query 中，可以通过条件筛选直接导航到表中的特定记录，而不是通过索引位置。这种方法类似于使用筛选器，但需要提供一个“Key”（逻辑或标准），以确保返回的是一条记录，例如：
在这里插入图片描述
这个公式会从 “Changed Type” 步骤的表中导航到满足以下条件的特定行：

  • Inventory Item 字段值为 “Lovable Kitten”
  • EmployeeID 字段值为 1
  • Quantity 字段值为 4

  如果表中的多条记录符合定义的“Key”，那么会报错。解决这个问题的一个简单方法是确保表中有一个具有唯一值的“Key”列，比如创建一个索引列。

在这里插入图片描述
  在导航到特定记录后，同样可以通过字段名访问记录中的特定字段，两种方式结果相同。
在这里插入图片描述

  但在Power Query 中，通常使用后一种方法（ 按条件）进行访问。例如，在查询的 “Source” 步骤中，可能会看到类似以下的公式：

= Excel.CurrentWorkbook(){[Name="Sales"]}[Content]

在这里插入图片描述

  • Excel.CurrentWorkbook()：获取当前工作簿中的所有可用表（或命名范围），即每一行代表工作簿中的一个表或命名范围，通常包含以下列：

    • Name：表或命名范围的名称。
    • Category：表或命名范围的类别（如 Table 或 NamedRange）。
    • Data：表或命名范围的实际数据。
    • Hidden：是否隐藏。
    • Content：表或命名范围的实际内容（通常是另一个表）。

  • {[Name="Sales"]}：按条件访问Name 列中值为 “Sales” 的记录。

  • [Content]：导航到该记录的 "Content" 列，通常是该表的实际内容，即比如 “Sales” 表的实际数据。

15.4.4 从表中提取记录
15.4.4.1 通过添加索引列实现

  这种方法的核心是通过添加一个索引列，然后利用索引列的值动态地将每一行转换为记录。

  • 添加一个索引列（从0开始），重名为此列为索引；
  • 使用公式 = AddedIndex{[索引]} 添加自定义列：
    在这里插入图片描述
15.4.4.2 使用下划线（_）简化操作

  另一种更简单的方式是使用公式= _，可以得到相同的结果。

在这里插入图片描述
  这里的下划线是 M 语言中的一个特殊关键字，表示当前元素，在本例中是当前行；each 关键字用于对每一行应用操作。

15.5 Value

  当使用关系数据源（如 OData 或数据库）时，如果表之间设置了主键和外键关系，可能会看到包含“Value”的列。此列是数据库返回记录的一种方式，它表示与当前记录相关联的单条记录。

在这里插入图片描述

在数据库中，一般是一对多的关系，进行查询时：

  • 返回 Value：如果从关系的“多”端查询“一”端（比如从事实表查找维度表），返回的是一个“Value”列（记录）。
  • 返回Table：如果从关系的“一”端查询“多”端（比如从维度表查找事实表），返回的是一个“Table”列（多条记录）。

15.6 二进制文件（Binary）

二进制文件是 Power Query 中的一种特殊值类型，用于表示文件内容。二进制文件的主要特点如下：

  • 可以通过各种连接器（如 Excel、CSV、JSON 等）读取和解析；
  • 可以使用特定的函数（如 Binary.From 或 Binary.FromText）将二进制文件转换为其他格式。

15.7 错误处理

在 Power Query 中，错误分为行级错误和步骤级错误：

  • 行级错误：通常发生在数据转换过程中，不会中断整个数据处理过程，只会影响特定的行。有时候会故意引发行级错误来进行数据筛选（见本文14.2章节）
    在这里插入图片描述

  • 步骤级错误：通常发生在 公式或操作的语法错误，或者 逻辑错误 中，会中断整个查询处理过程。

  错误调试：Power Query 虽然实现了智能感知和着色，但其调试工具经常出错，比如一个由于缺少“ } ”字符而导致的语法错误，却提示要求使用逗号（“^”字符，指示 Power Query 认为需要将逗号放在哪里）：

在这里插入图片描述

15.7 函数（Function）

  函数是 Power Query 中的一种特殊值类型，它表示从一组输入值到单个输出值的映射。

15.7.1 使用#shared查看所有M函数

 &#8195通过在公式标记栏中输入=#shared，可以调取所有内置的M函数列表，通过列筛选或搜索可以查找特定的函数。右键点击任意一个函数，选择“到表中”，将结果转换为表格。

在这里插入图片描述

在这里插入图片描述

所有包含“Max”的函数

点击Function会弹出函数文档和函数调用窗口：

在这里插入图片描述
在公式栏书写M公式时，Power Query会自动提示函数的参数及其简要说明：

在这里插入图片描述

15.7.2 自定义函数
15.7.2.1 函数定义方式

  Power Query 中的函数本质上是 参数化的表达式，通过 (参数) => 表达式 的语法结构进行定义，其定义方式有两种：

  1. 通过界面生成函数：在 Power Query 编辑器中，可以通过 右击查询，选择 “创建函数” 创建带参数的函数，界面会自动生成模板。

  2. 显式定义函数：直接在代码中声明函数，并绑定到变量，其基本语法为：

    函数名 = (参数1, 参数2, ...) => 表达式

    = let// 定义加法函数AddNumbers = (x, y) => x + y,// 调用函数并传入参数Result = AddNumbers(3, 5)
inResult		// 返回8
15.7.2.2 指定参数类型

可以为参数指定数据类型，增强代码健壮性：

// 限定参数为数值类型
Multiply = (x as number, y as number) => x * yMultiply(2, 3)      // 返回 6
Multiply(2, "text") // 报错：类型不匹配
15.7.2.3 可选参数（默认值）

使用 optional 关键字定义可选参数，并指定默认值：

= let// 定义带默认值的参数Greet = (name as text, optional greeting as text) =>if greeting is null then "Hello, " & name else greeting & ", " & name,
inGreet

在这里插入图片描述

Greet("Alice")                // 返回 "Hello, Alice"
Greet("Bob", "Bonjour")       // 返回 "Bonjour, Bob"

15.8 关键词（Keyword）

在 M 语言中，关键词是具有特殊含义的保留字，用于定义特定的数据类型或操作。常见的关键词包括：

关键词用途示例代码说明
#binary创建二进制值= #binary("TWlndWVsIEVzY29iYXI=")通常通过 Base64 编码的文本值创建二进制值。
#datetime创建日期时间值= #datetime(2021, 5, 23, 13, 23, 54)2021 年 5 月 23 日下午 1 点 23 分 54 秒
#datetimezone创建带时区的日期时间值= #datetimezone(2021, 5, 23, 13, 23, 54, -5, 0)同上，但位于UTC-5时区（美国东部时间，比UTC晚5个小时）
#time创建时间值= #time(14, 36, 54)表示下午 2 点 36 分 54 秒的时间值
#duration创建持续时间值= #duration(4, 22, 10, 35)表示 4 天 22 小时 10 分钟 35 秒的持续时间值
type定义数据类型type number
type text		用于在代码中明确指定数据类型，如数字、文本等。
#table创建表值创建一个表值，需要列标题列表和数据行。
true、false逻辑值= true
= false		用于逻辑运算和条件判断。
and、or、not逻辑运算符= [Column1] and [Column2]
= [Column1] or [Column2]
= not [Column1]		用于执行逻辑与、或、非运算。
if、then、else条件语句关键字m = if [Column1] > 10 then "Greater" else "Lesser"用于条件判断和分支逻辑。
try、otherwise错误处理关键字m = try [SomeOperation] otherwise "Default Value"用于捕获错误并提供默认值。
15.8.1 基元值关键字

  1. 创建二进制值（ #binary） ：二进制值是数据的一种表示形式，通常用于表示文件内容或非文本数据。#binary 关键字可以接受任何值，并尝试将其转换为二进制值。最实用的方法解码 Base64 编码的文本值。

    • Base64 → 二进制：#binary将Base64字符串解码为原始二进制数据。例如，“TWlndWVsIEVzY29iYXI=” 解码为二进制字节 77 105 103 117…。
      在这里插入图片描述
    • 二进制 → 文本：如果单击左上角的【打开为】选择【文本文件】，则 Power Query 将用UTF-8编码将二进制转为文本（ "Miguel Escobar"）。
      在这里插入图片描述
      • Base64 是一种编码方案，用于将二进制数据转换为可打印的ASCII字符（A-Z, a-z, 0-9, +, /），以便在文本协议（如JSON、URL）中安全传输。
      • 还有一些函数可以将其它值解释为二进制值，比如“Binary.From”、“Binary.FromText”和“Binary.FromList”。

  2. 创建日期时间（#datetime）：略

  3. 创建日期时区（#datetimezone）：#datetimezone语法如下：

    #datetimezone(year, month, day, hour, minute, second, timezone_hour, timezone_minute)

      如果在公式栏输入=#datetimezone(2021,5,23,13,23,54,-5,0)，Power Query 会自动将该关键词转换为值的实际表示形式，但这只是 Power Query 团队设计的一个视觉功能。在【视图】【高级编辑器】中，代码依旧是完整的：
    在这里插入图片描述

  4. 创建时间（#time）
    在这里插入图片描述

  5. 创建持续时间（#duration）：date，datetime，datetimezone可与duration进行计算，例如：
    在这里插入图片描述

15.8.2 type关键字

  数据类型 影响 M 引擎在执行查询步骤和特定函数工作时的行为。 Power Query 中的类型包括：基元类型（基本的数据类型如二进制、日期、时间、逻辑值、数字、文本等；以及任意类型和空类型）、记录类型、列表类型、表类型、函数类型。

15.8.2.1 使用Table.Schema检测数据类型

可以通过扫描列左上角的图标来查看表的元数据，包括数据类型。

在这里插入图片描述

如果列太多，就不方便一一检查。此时可使用Table.Schema函数，列举出所有列的类型：

在这里插入图片描述

15.8.2.2 设置数据类型

  可以使用 Table.TransformColumnTypes 函数来设置列的数据类型，例如将 id 列设置为整数类型或十进制类型。

= Table.TransformColumnTypes(Source,{{"id", Int64.Type}})  	 // 严格的整数类型
= Table.TransformColumnTypes(Source,{{"id", type number}})   // 十进制类型，包括整数类型和小数类型

还可以使用type关键字，指定、转换或处理数据的类型，例如：

在这里插入图片描述

15.8.3 #table 关键字

  除了使用列表和记录来创建表，还可以使用#table关键字来创建。#table 关键字需要两个参数，分别是表列（列标题列表或表类型）和表中行的数据。

  1. 表列为列标题列表：注意字段是按位置填充的，而不是按列名填充。此方式创建的表没有定义数据类型：
    在这里插入图片描述
  2. 表列为表类型：虽然可以添加一个“Changed Type”步骤来设置数据类型，但可以通过表类型来显示定义表中每一列的数据类型。
    在这里插入图片描述
    也可以写成：
    在这里插入图片描述
      注意到这里的id列是小数类型，而非整数类型。这是因为number其实表示的是十进制数字（可以是小数或者整数），而非严格的整数类型（如 Int64.Type）。所以如果将一个整数列定义为 number 类型，然后对该列进行数学运算（如除法），结果可能会包含小数部分。如果需要严格的整数运算，这种行为可能会导致意外的结果。

十六、理解 M 语言

16.1 查询结构

16.1.1 查询结构
  1. 导入数据
    • 根据第12章的内容，连接到【AdventureWorks 数据库】
    • 在【导航器】窗口中，导航到“SalesLT.Customer”表（在搜索栏搜索）
    • 筛选“CompanyName”列，内容为“Friendly Bike Shop”
    • 删除“CustomerID”、“FirstName”、“LastName”列之外的其它列
    • 将查询重命名为“Sample Query”
  2. 设置【高级编辑器】：点击【视图】【高级编辑器】，点击界面右上角的显示选项，设置【显示行号】、【呈现空格】【启用自动换行】

  此时显示的是整个查询的所有M语言代码。M 语言是Power Query 核心，通过 M 语言可以实现复杂的数据处理逻辑。一个典型的 M 查询由以下四个核心部分组成：

在这里插入图片描述

  • 查询开始：这由查询顶部的“let”关键词定义（本例中为第 1 行）。

  • 查询结束：这是由“in”关键词（第 6 行）定义的，该关键词通常在代码末尾的倒数第二行。

  • 查询定义：是在“let”和“in”关键词之间看到的所有内容，指示查询将执行的操作。在本例中，定义的范围从查询的第 2 行到第 5 行。

  • 查询输出：在“in”关键词之后，需要定义查询的输出。对于这种情况，这在第 7 行中定义，并返回查询中最后一步的值：#“Removed Other Columns”。

16.1.2 标识符与命名规则

在 M 语言中，标识符用于表示查询名称、步骤名称、字段名称，便于引用。标识符分为以下两种：

  • 常规标识符：名称中没有空格或特殊字符，直接使用名称即可。例如StepName = ...：

  • 引用标识符：名称中包含空格、特殊字符或关键字，需要使用 #" 和 " 包裹。例如#"Step Name" = ...

  这种标识符模式将贯穿整个 M 语言，【应用的步骤】中包含步骤的简洁名称，有些和M语言中是一致的，有的不是，比如Navigation步骤在 M 代码中却根本不存在（因为这个步骤是由 Power Query 自动创建的，后台是使用的额是导航到的表 SalesLT_Customer ）。

在这里插入图片描述

  当引用字段名时，即使值的名称有特殊字符或空格也无需使用带引号的标识符，这种叫做通用标识符，例如：
在这里插入图片描述
在这里插入图片描述

16.1.3 代码注释

  在 M 语言中，单行注释：以 // 开头；多行注释：以 /* 开始，以 */ 结束。有多种方法可以向查询添加注释，但到目前为止，最简单的方法是通过用户界面来完成：

在这里插入图片描述

完成此操作后，Power Query 将在步骤右侧显示一个小信息图标，表示它有一个注释。在后台，它将在 M 代码中添加注释：

在这里插入图片描述

多行注释：

在这里插入图片描述

16.2 查询计算

16.2.1 延迟计算与查询折叠

Power Query 的查询计算方式取决于其执行环境：

  • 数据预览视图：在 Power Query 的数据预览模式中，通常只会计算部分数据（例如前 1000 行），以便快速展示数据。
  • 查询输出：当将数据加载到目标位置（例如 Excel 工作表或 Power BI 报告）时，Power Query 会执行完整的查询计算，以生成最终结果。

  延迟计算的核心优势在于其能够根据需要动态计算数据。例如，当从一个包含数十亿行的数据库表中提取数据时，Power Query 不会立即下载整个表，而是仅计算和缓存前 1000 行数据，供用户在数据预览中查看。这种方法不仅节省了时间和资源，还允许用户在较小的数据子集上快速构建和测试数据转换逻辑。

  此外，延迟计算与 Power Query 的另一个关键特性——查询折叠相结合，可以进一步优化性能。

  • 查询折叠 是指 Power Query 将 M 代码转换为数据源的本机查询（例如 SQL 查询），从而将尽可能多的计算工作推送到数据源端执行，详见第12章。
  • 对于 SQL Server 数据库，可以使用 SQL Server Profiler 之类的工具来观察 Power Query 向数据库发出的请求。数据预览请求语句包含 SELECT TOP 1000，而完整数据请求则没有这个 TOP 1000 的限制。
16.2.2 查询计划

  在本节中，默认用户已经在 Power Query 在线版中打开了“查询计划”预览项，或者用户正在使用的 Power Query 产品中已经提供了该功能。

  查询计划是 Power Query 的一个重要特性，用于展示查询的执行逻辑和数据源交互方式。通过查询计划，可以了解哪些步骤可以被折叠到数据源的本机查询中，哪些步骤需要在本地执行，对于理解和优化查询非常有用。

  右击“Sample Query”查询的最后一步并选择【查看本机查询】选项，即可打开查询计划窗口：

在这里插入图片描述

  这个查询计划表示，整个查询可以被推送到数据源执行，此外，它还告诉用户将向数据源发送什么查询以及将向哪个数据源发送查询。现在来为查询添加一个新的步骤【保留最后几行】，选择2，这将打破“查询折叠”（ SQL 数据库没有用于选择表底部行的运算符）。启动Power Query 在线版的查询折叠指示器，可以看到：

在这里插入图片描述

  右击此步骤并启动查询计划视图，可以看到这里添加了一个名为 Table.LastN 的新事件或任务（在“Kept bottom rows”步骤中使用的函数）。Power Query 引擎会在本地进 Table.LastN 的计算。

在这里插入图片描述

16.3 迭代器（逐行计算）

16.3.1 循环函数

  在 M 语言中，虽然没有传统编程语言中的 for 或 while 循环，但提供了多种循环函数来实现类似的功能。这些函数可以帮助用户在数据集上进行迭代操作，主要包括：

  1. List.Generate：通常用于生成列表，尤其是在需要分页（Power BI 自定义连接器中）或逐步生成数据时；
  2. List.Accumulate：类似于 for 循环，用于对一组值进行迭代并执行聚合运算，例如计算阶乘。

  这些函数虽然强大，但在实际使用中频率较低。在大多数情况下，用户可以通过更简单的方法实现类似的功能，同时保持代码的可读性和性能，所以不具体讲解这些函数。

16.3.2 关键词 each 和 _

  在 M 语言中，可以使用 each 和 _ 关键词进行逐行计算，来大幅简化对行、列或列表的操作代码，提升 Power Query 脚本的可读性和编写效率。在自定义函数章节，介绍过函数定义的语法为：

函数名 = (参数1, 参数2, ...) => 表达式

  在Power Query中，each [操作] 等价于 (_) => [操作] ，其中 _ 是匿名函数的隐式参数占位符，代表当前处理的元素（如行、列值或列表项，仅在 each 后面的表达式中有效）。所以each本质是一个语法糖，是一个比其等价语法更容易输入的方法，用于快速定义一个接受单个参数的匿名函数。

16.3.2.1 创建新列

  在15.4.4.2章节介绍过，添加自定义列时直接输入_，可以得到当前表中每一行的记录（删除之前的保留后两行步骤）：

在这里插入图片描述
  如果在添加【自定义列】对话框中输入：=[FirstName] & " " & [LastName]，确定之后在公式栏或【高级编辑器】中可以看到此步骤的完整代码：

// 三个参数分别是需要添加列的表、新添加列的名称（文本）以及新列生成器（函数）= Table.AddColumn(删除的其他列, "FullName", each [FirstName] & " " & [LastName])

以下三个函数是等价的：

= Table.AddColumn(Source, "FullName", each [FirstName] & " " & [LastName])
= Table.AddColumn(Source, "FullName", (_) => _[FirstName] & " " & _[LastName])
= Table.AddColumn(Source, "FullName", (_) => [FirstName] & " " & [LastName])
  • 公式1：使用了 each，这是 Power Query 中使用函数的最简单方式，也是创建新列的默认方式
  • 公式2：显式地定义了一个函数，其中_ 表示当前行的记录，_[FirstName] 和 _[LastName] 分别表示当前行的 FirstName 和 LastName 字段（通过字段名访问记录）。
  • 公式3（不建议使用）：省略了 _，但仍然有效，因为 Power Query 会自动识别字段属于当前行的记录。
16.3.2.2 处理列表
// 将列表中的每个元素乘以 2
= List.Transform({1, 2, 3}, each _ * 2)  // 返回 {2, 4, 6}// 等价于显式写法：
= List.Transform({1, 2, 3}, (_) => _ * 2)
= List.Transform( {1..5}, (r)=> r * 2)
16.3.2.3 访问嵌套属性
= letdata = {[id=1, info=[name="Alice"]], [id=2, info=[name="Bob"]]},//  访问 JSON 嵌套字段names = List.Transform(data, each _[info][name])
innames

在这里插入图片描述

16.2.3.4 使用限制
  • 此方式仅适用于单参数函数。若需要多个参数，必须显式定义，例如 (x, y) => x + y）。
  • 不能将“each”嵌套在另一个“each”中，因为 Power Query 将只返回公式中最顶层或最外层的“each”（避免混淆）

16.4 动态数据处理技巧

16.4.1 表索引时使用索引数字而非字段值

  使用数字索引，如Source{0}，来固定访问表的某一行数据，可以避免因字段值变化而导致的错误。比如本来中导航到SalesLT.Customer表，可以单击第7行的table关键字来完成，此时导航公式为：= 源{[Schema="SalesLT",Item="Customer"]}[Data]，使用的是固定字段值。

在这里插入图片描述
在这里插入图片描述
也可以使用索引数进行导航：

在这里插入图片描述

16.4.2 错误保护

  本节讨论如何在访问字段时添加错误保护机制，以避免因字段不存在或数据缺失而导致的错误，提高代码的健壮性和可读性。

  1. 通过try...otherwise null完成，例如：

    = try 源{[Schema="SalesLT",Item="Customer"]}[Data] otherwise null

  2. 字段后面添加一个“?”。这种方法类似try...otherwise null的语法糖，可以将错误捕获并返回null，例如：

    = 源{[Item="Custome"]}[Data]?  // 我的excel365还是抛出错误
= 源{[Item="Custome"]}[Dat]?   // [Data]字段输出错误，确实抛出了null。

    在这里插入图片描述

    在这里插入图片描述

    继续测试：

    = SalesLT_Customer{0}[CustomerI]?   // 对记录索引时，字段名错误返回null
= SalesLT_Customer{-1}[CustomerI]?  // 在索引表时，字段名错误，还是抛出错误

  可以添加一个检查导航步骤的结果是否为空的验证步骤，然后根据验证结果采取相应的行动，应对在数据导航阶段因任何原因而产生错误的情况。

16.4.3 固定动态列表的类型

  在Power Query中，当表中的列（尤其是包含record、list或table类型的复杂列）在展开操作时（执行Table.ExpandRecordColumn , Table.ExpandTableColumn函数），会有两种行为模式：

  • 未显式定义数据类型：Power Query不知道表中所有列的元数据信息，需要在数据预览中对数据进行分析；
  • 显式定义数据类型：Power Query已经知道表中所有列的元数据信息

下面进行详细的介绍。

16.4.3.1 未显式定义数据类型

  未被显式定义数据类型时，Power Query 会启动动态探测机制，进行 延迟计算（Lazy Evaluation）：

  • 仅扫描部分数据（如前几行）来推断字段结构。
  • 若后续数据中存在未扫描到的字段，首次展开时会显示 【列表可能不完整】 的警告，需手动点击 【加载更多】 触发全量扫描。
  • 潜在问题：
    • 性能依赖数据顺序（如果关键字段在未扫描的行中，会漏字段）。
    • 用户体验较差（需手动干预才能看到完整字段）。

  下面创建一个空白查询，输入以下代码，并将该查询命名为“List of Headers”。由于它包含记录，可以使用右上角的按钮展开“Data”列：

在这里插入图片描述

  可以看到，当尝试展开“Data”列中的记录时触发了一条警告，其内容为【列表可能不完整】。这是因为最后一行记录中多了一个 #"Tall?" 字段，当扫描到不存在的字段时会发出此警告。单击旁边的【加载更多】会触发【全量扫描】，扫描出此字段：

在这里插入图片描述

16.4.3.2 显式定义数据类型

   通过 type 关键字预先定义列的数据类型，Power Query 会 跳过动态探测，直接按定义的类型展开所有字段（即使某些字段在部分行中不存在）， 确保所有字段的数据类型一致性。

  1. 新建一个查询 "MyRecordType"，明确定义所有可能的字段及其类型：
    = type [First= text, Last=text, Country=text, #"Tall?"=logical]
  2. 应用自定义类型到原表：复制原查询并命名为“Dynamic Headers - Typed”，将 Data 列的类型从 record 替换为 MyRecordType。
    #table(type table [Data = MyRecordType, Title = text],{{[First = "Bill", Last = "Jelen"], "First"},{[First = "Miguel", Last = "Escobar", Country = "Panama"], "Second"},{[First = "Ken", Last = "Puls", Country = "Canada", #"Tall?" = true], "Third"}}
)
    此时展开 Data 列时，会直接显示所有字段（First、Last、Country、Tall?），无需手动加载。
    在这里插入图片描述
16.4.3.3 动态提取所有字段名

此方法通过编程方式动态获取所有记录的字段名，确保展开时自动检测并展开所有字段。

  1. 数据准备：复制原始查询（List of Headers），命名为 Dynamic Headers - Untyped，此时Data 列是未定义类型的记录（record）。
  2. 深化Data 列：右键 Data 列 → 选择 【深化】，将列转换为 记录的列表（List[Record]）
  3. 动态提取所有字段名： 使用List.Transform和Record.FieldNames获取每个记录的字段名。
    = List.Transform(Data, each Record.FieldNames(_))  
// 结果：{{"First", "Last"}, {"First", "Last", "Country"}, {"First", "Last", "Country", "Tall?"}}
  4. 合并列表：使用List.Combine将所有字段名合并为一个列表
  5. 去重： 单击工具栏【删除重复项】，生成唯一的字段名列表，将此步骤重命名为“Removed Duplicates” (如果动态字段名与表中已有列名重复，会报错)。
    // 合并所有子列表并去重
= List.Combine(Custom1)                           
= List.Distinct(Custom2)      //
    在这里插入图片描述
  6. 动态展开：将唯一的字段名列表传递到 Table.ExpandRecordColumn 函数中，进行动态展开。
    在这里插入图片描述

  如果是对表进行展开，可将Record.FieldNames→Table.ColumnNames;Table.ExpandRecordColumn → Table.ExpandTableColumn。

类型Power Query 的行为适用场景
未定义类型（动态记录）Power Query 按需探测字段，可能不完整（需点击“加载更多”）当字段频繁变化或不可预知时（如用户生成内容、第三方API）
动态传递字段名遍历所有记录提取唯一字段名，确保完整展开
性能最差（需全量扫描记录提取字段名）		同上
显式定义类型预先定义所有字段，展开时立即显示完整列表，性能最优当数据结构完全已知且稳定时（如数据库表、标准接口）

十九、查询优化

19.1 查询设置

  本节介绍了如何通过调整 Power Query 的设置来优化查询性能，包括全局设置和当前文件设置。【查询选项】对话框可通过以下方式打开：

  • Excel：【数据】【获取数据】【查询选项】；
  • Power BI：【文件】【选项和设置】【选项】。
19.1.1 全局设置
设置分类设置项推荐设置说明
全局数据加载类型检测保持默认（中间）在需要时对特定文件进行自定义调整，平衡功能和控制
后台数据保持默认（中间）同上
默认查询加载设置（仅Excel）指定自定义默认加载设置
取消"加载到工作表"和"加载到数据模型"		指定默认加载方式为"仅限连接"
如果需要再加载到表或模型，这样速度更快
快速加载数据选中提高数据加载速度，副作用是数据刷新时锁定 Excel 窗口
导致用户无法进行其他操作
全局编辑器设置所有选项全部选中优化编辑体验
全局安全性设置新本机数据库查询需要用户批准启用如果需要自定义 SQL 语句，启用此选项可防止恶意操作
（如将 SQL 查询从 SELECT 更改为 DROP TABLE）。
全局隐私设置隐私级别根据每个文件的隐私级别设置合并数据（默认）禁用全局隐私设置会导致数据失去保护

在这里插入图片描述

  12章介绍过Power Query 的隐私设置主要用于控制数据的访问和合并行为，尤其是在涉及多个数据源时。在某些情况下，调整隐私设置可以提高查询刷新速度或解决合并数据的问题，但是直接全局禁用会导致数据失去保护。使用默认设置【根据每个文件的隐私级别设置合并数据】就OK。

19.1.2 当前文件设置
设置分类设置项推荐设置说明
后台数据允许在后台下载数据预览默认启用，以自动加载数据预览在处理大型文件时遇到性能问题，可以关闭此设置
但会导致查询编辑体验变慢，因为预览需要手动刷新。
区域设置区域设置按需调整覆盖默认语言设置（详见本书5.2.5章节）
隐私隐私 级别按需调整可在文件级别调整隐私设置

  在禁用【后台数据】还可能会导致一些视觉副作用。下图取自一个非常复杂的解决方案，其中【允许在后台下载数据预览】被禁用。虽然它使工作簿有更好的响应性，但许多查询的图标上会显示一个问号（？）（因为没有生成预览），需要手动强制刷新预览数据。

在这里插入图片描述

19.2 Power Query 中的缓存机制与应用

19.2.1 缓存的作用与原理
  • 覆盖惰性求值：Power Query 默认采用惰性求值（Lazy Evaluation），即仅在需要结果时才执行计算，并尽可能将操作推送到数据源（查询折叠）。
  • 缓存机制：缓存函数（如 Table.Buffer）则可以强制立即执行某一步骤的计算，并将结果存储在内存中。后续步骤直接读取该缓存数据，避免重复计算和多次访问外部数据源，提升性能。

缓存函数类型：

  • Table.Buffer：缓存整个表。
  • List.Buffer：缓存列表数据。
  • Binary.Buffer：缓存二进制数据（如文件内容）。
19.2.2 案例一：强制计算（生成随机数）

  在以前版本的 Power Query 中，在自定义列中使用 Number.RandomBetween 生成随机数时，惰性求值特性会导致所有行使用同一个随机数（合并了计算步骤，仅执行一次随机数生成）。

创建一个【空白查询】，在【高级编辑器】中输入以下代码：

letSource = Table.FromList( {"A" .. "E"}, null, {"Column1"} ),#"Added Custom" = Table.AddColumn( Source, "Random Value", each Number.RandomBetween(1,10) ) 
in#"Added Custom"

解决方案是 通过Table.Buffer 函数包裹随机数计算步骤，强制立即计算并缓存结果，迫使每行生成独立列表。

= Table.Buffer( Table.AddColumn(Source, "Random Value", each { Number.RandomBetween(1, 10) } ) )

在这里插入图片描述

19.2.2 案例二：多文件处理时缓存中间结果
  • 查询折叠的局限性：在某些场景下（如使用“从文件夹”连接器读取 CSV、TXT 或 Excel 文件），查询无法折叠回数据源。这意味着查询会被多次读取，导致性能下降。
  • 缓存的作用：Power Query 会尝试缓存共享节点（被多个查询或步骤引用的查询或步骤），以减少重复计算。但如果共享节点不够，可以通过缓存整个查询或查询中的值来优化性能。
  • 查询折叠（Query Folding） 是 Power Query连接数据库时的一项重要优化机制。其核心思想是：将用户在 Power Query 中定义的数据转换步骤，自动转换为数据库原生的查询语句（如 SQL），让数据库服务器直接执行这些操作（数据库上执行更快），而非将原始数据下载到本地后再处理。这一机制显著提升了查询性能，在处理大规模数据时效果尤为明显。
  • 查询折叠是一种将数据处理工作推送到数据源的技术，因此需要数据源具备计算引擎。这通常适用于具有存储和计算功能的数据库（如SQL）。TXT、CSV 和 Excel 文件等不包含计算引擎，不支持查询折叠。
19.2.2.1 案例背景

  以第19章示例文件Buffer Functions.xlsx为例，需要从Buffer Data文件夹的所有Excel表中汇总数据。每个Excel文件有两个表：Forecast表（统计所有产品所需的零件数据）和Parts表（每个产品需要的零件数）：

在这里插入图片描述
在这里插入图片描述
如果缓存中间结果，可以避免重复读取文件，提高性能。

在这里插入图片描述

19.2.2.2 对比测试

为了进行对比测试，构建以下查询结构：

  • Source Files：数据源文件列表，包含所有 Excel 文件。
    在这里插入图片描述
  • Buffered Files和Unbuffered Files：都是引用Source Files，删除 Content 之外的所有列，然后 提取Content列的内容，区别在于提取时是否启用缓存机制（each Excel. Workbook ( Binary.Buffer( _ ) ) ）。
    在这里插入图片描述
    在这里插入图片描述
  • Use Buffering参数：用于切换“Buffered”或“Unbuffered”文件列表。
  • Forecast和Parts：分别提取“Forecast”和“Parts”数据。
    在这里插入图片描述

使用以下环境进行10次测试：

  • 软件：微软 365 beta 频道的 2107 版本的 64 位 Excel。
  • 硬件：双核 i7-7600U CPU@2.8GHz，内存 16GB。

测试结果如下：
在这里插入图片描述

  • “Buffered”查询的刷新时间平均为 9.6 秒（± 0.16 秒）；
  • “Unbuffered”查询的刷新时间平均为 16.6 秒（± 0.33 秒）；
  • “Buffered”版本的刷新速度快 30%，时间波动减少了一半。

  修改查询以添加或删除“Buffered”函数，然后测试刷新时间的这种手工方式结果不准。Power BI 桌面版的查询诊断信息（记录步骤级细节的刷新时间）效果更好，但需要专业计数。Monkey Tools 插件可在 Excel 中对刷新操作进行基准测试并自动绘制图表，效果更好。

缓存机制可以显著提升 Power Query 的性能，尤其是在无法利用查询折叠的场景中，但也有一些注意事项：

  • 适用场景：只有在明确知道无法利用查询折叠或不适合惰性计算时，才建议使用缓存计算。
  • 避免过度缓存：不要嵌套使用缓存函数去缓存已经缓存过的值，否则会对性能产生负面影响。
  • 正确选择缓存函数：需要根据业务场景和查询依赖链条选择合适的缓存函数。

19.3 处理响应滞后

19.3.1 响应滞后的成因

  Power Query 在处理大数据集时有一个常见痛点——界面响应延迟。在处理大型数据集时，Power Query 的预览刷新机制会导致响应延迟。

  比如连接到一个包含7000万行数据的数据库时，Power Query先加载前1000行预览。当用户筛选掉"dept 105"的记录（假设筛选后剩500行），此时Power Query不会直接对本地预览的1000行做筛选，而是会将筛选操作推送到数据源进行处理，再从数据源中重新获取更新后的前 1000 行数据（而非预期的500行）。

  这就导致用户在Power Query编辑器中每添加/修改一个步骤（如筛选、删列），Power Query的预览机制都会实时重新计算每一步操作，并尝试将操作"折叠"到数据源查询中，再从源头重新获取前1000行的预览数据。对于大数据集，这种反复的远程请求和计算会导致显著延迟、卡顿，需要等待很长时间才能看到预览更新。

   Power Query 的设计是惰性计算的，即它会在每次修改查询步骤时重新计算整个查询，而不是仅对当前预览的数据进行操作。这种设计虽然在运行时可以优化性能（通过查询折叠），但在开发过程中会导致响应缓慢，尤其是对于大型数据集。

  用户可能会想到使用 Table.Buffer 函数来缓存数据，以加快响应速度。然而，Table.Buffer 在查询开发阶段（查询编辑预览）中是无效的。它会被忽略或重新执行，这意味着即使使用了 Table.Buffer，Power Query 仍然会重新从数据源加载数据。这不仅没有解决问题，反而可能因为额外的缓存操作而进一步增加响应时间。

  一种期望改进方式是缓存预览步骤，比如“锁定”一个步骤，将其结果缓存起来，后续步骤可以直接从缓存中读取数据，而不是每次都重新从数据源加载。然而，目前Power Query 并不支持这种缓存机制。用户只能通过其它方法（如优化查询结构、减少预览数据量等）来缓解响应延迟的问题，或者是使用下文的 “暂存数据法” 来优化。

19.3.2 优化策略：临时暂存数据法

处理响应滞后的一种方法是为数据设置一个临时阶段，减少预览刷新时需要处理的数据量。

  1. 连接到数据源：首先连接到完整的数据源（例如包含 7000 万行数据的数据库），创建“原始数据”查询；
  2. 创建“暂存数据”查询：【引用】“原始数据”查询，从中提取一个较小的样本数据集（例如前 1000 行数据），并将其加载到 Excel 表中，作为临时的数据源；
  3. 创建“临时数据”查询：创建一个新的 Power Query 查询，仅引用这个较小的数据集，将其设置为【仅限连接】
  4. 引用“临时数据”查询进行开发：在“临时数据”查询的基础上开发，得到输出查询；
  5. 重新指向“原始数据”查询：开发完成后，将"输出"查询重新指向"原始数据"；
  6. 删除临时组件：删除不必要的“暂存数据”和“临时数据”查询及其对应的 Excel 表。

在这里插入图片描述

19.3.3 SQL数据处理原始方案（响应滞后）

  1. 连接到【AdventureWorks 数据库】（参考第 12 章的连接方式）

  2. 导航到“Sales Order Detail”表

  3. 删除“ModifideDate”、“OrderQty”、“ProductID”、“UnitPrice”、“SalesLT.Product”列以外的所有列

  4. 为了模拟更大的数据量，使用以下公式添加一个名为“Custom1”的额外步骤，人为将数据重复1000次至542000 行；

    = Table.Repeat(#"Removed Other Columns",1000)

  5. 更新数据类型 在这里插入图片描述

    • Table.Repeat 会将数据集追加 x 次，如果需要性能测试，这是一个非常有用的函数
    • 如果报错“Expression.Error”，表示运行时内存不足，请减少数据重复次数

  6. 从“SalesLT.Product”展开“Name”列

  7. 再次更新所有列的数据类型

最后两步可以感受到明细的延迟，每次步骤至少需要5秒的处理时间：

在这里插入图片描述

以上操作在“第 19 章 示例文件\Development Lag-Begin.xlsx”文件中可以找到。

19.3.4 重构解决方案

  1. 构建原始数据查询：提取“Changed Type”步骤之前的步骤，并将其重命名为“Raw Data”查询。；
    在这里插入图片描述

  2. 将所有查询加载为【仅限连接】；

  3. 【引用】“Raw Data”查询来创建“Staging Data”查询（暂存查询），仅保留前 1000 行（【主页】【保留行】【保留最前面几行】输入“1000”），将其加载到 Excel 表中（不能加载到数据模型，否则三倍耗时）；
    在这里插入图片描述

  4. 创建“Temp Data”查询（临时查询），从该 Excel 表读取数据（选择表中的任意单元格，创建新查询，【来自表格/区域】）。

  5. 修改“Output”查询的源步骤，指向“Temp Data”（=#"Temp Data"）；

  6. 开发查询：应该注意到，此时预览【刷新】变得更快了

    • 选择“OrderQty”列和“UnitPrice”列【添加列】【标准】【乘】；
    • 选择“ModifiedDate”列【转换】【日期】【年】【年】；
    • 将“ModifiedDate”列重命名为“Year”；
    • 选择“Year”列和“Name”列【转换】【分组依据】，将【新列名】配置为“Revenue”，【操作】选择“求和”，【柱】选择“乘法”
      在这里插入图片描述

  7. 完成开发后，将“Output”查询重新指向“Raw Data”（=#"Raw Data"）；

  8. 【关闭并上载】“Output”查询，由于“Output”查询现在指向完整的数据库，刷新需要一定时间

  9. 删除临时查询和 Excel 表（可选）。

在这里插入图片描述

注意事项：在开发过程中，必须等待“Output”查询完全加载完成，必须

  • 在加载查询时，必须等待“Output”查询完全加载，它的加载状态直接影响后续操作的正确性；
  • 在开发过程中，可以通过修改“Staging Data”查询来扩展或更改源数据；
  • 以上所有步骤保存在Development Lag-Complete.xlsx中。

19.4 处理公式防火墙（见17.5章节）

二十、Excel自动刷新技术指南

Excel 提供了多种自动刷新 Power Query 解决方案的方法，用户可以根据需求选择适合的方式。

  1. 打开工作簿时刷新：此功能适用于需要确保数据在使用前是最新的场景。
  2. 每 x 分钟刷新一次：用户可以设置数据每 x 分钟自动刷新一次，此功能需要工作簿处于打开状态，并且必须启用“允许后台刷新”设置。
  3. 通过宏控制不同的刷新逻辑：比如刷新所有数据、按指定顺序刷新数据、只刷新Power Query连接等；
  4. 通过第三方插件安排刷新：对于更复杂的需求，用户可以使用第三方插件来安排刷新计划。

20.1 Excel 刷新设置

打开【查询和连接】窗格，右击查询，选择【属性】，即可进入查询设置对话框：

在这里插入图片描述

  1. 允许后台刷新：启用后，Excel 会在后台执行数据刷新操作，用户可以继续进行其它编辑操作。如果禁用此选项，那么刷新时Excel会锁定用户界面，用户无法操作，直到数据加载完成，但可以减少因用户操作导致的数据不一致或错误的风险。此选项共有三个级别：

    1. 查询级别：设置单个查询是否可以后台刷新；
    2. 工作簿级别：通过【数据】【获取数据】【查询选项】对话框中的“当前工作簿”选项，设置工作簿是否可以后台刷新；
    3. 应用程序级别：通过“查询选项”对话框中的“全局”选项，设置Excel是否可以后台刷新。

  2. 刷新频率：用户可以设置每 x 分钟刷新一次的频率。此功能的有效值范围为 1 到 32767，即每分钟刷新一次到每 22.75 天刷新一次。

  3. 打开文件时刷新数据。此设置包含两个选项：

    • 打开文件时刷新数据

    • 保存工作簿前，删除来自外部数据区域中的数据：简单说就是通过 Power Query、OLE DB 连接、ODBC 连接等方式从外部数据源（如数据库、Web API、文件等）导入到 Excel 工作簿中的数据，只在Excel打开时才可看到，且是最新的。关闭并保存Excel时，会将外部数据全部删除，而只保留数据的结构和连接信息。

      此方式的优点是：

      • 防止数据泄露：如果工作簿包含从外部数据源获取的敏感数据（如客户信息、财务数据等），启用此选项可以确保在保存工作簿时不会将敏感数据存储在文件中。这样，即使工作簿被意外共享或泄露，也不会包含实际的数据内容。
      • 保护数据源的访问权限：某些数据源需要特定的访问权限，启用此选项可以确保只有在打开工作簿时，数据才会从外部数据源重新加载，从而避免未经授权的用户直接访问工作簿中的数据。
      • 减小文件大小：只保留数据的结构和连接信息，可以显著减小保存的工作簿文件大小。坏处是 无法可能无法离线查看数据。

  4. 启用快速数据加载：启用此选项后，Excel 会在后台快速加载数据，而不是逐行逐列地逐步加载，这可以显著减少数据刷新所需的时间，尤其是对于大型数据集。但是启用之后，刷新数据时会锁定用户界面，直到刷新完成。

20.2 使用宏实现自动刷新

20.2.1 刷新单个连接

用户可以通过录制宏来刷新单个 Power Query 连接。具体步骤如下：

  1. 打开“第 20 章 示例文件\Automating Refresh.xlsx”，转到“Transactions”工作表；

  2. 打开“开发工具”选项卡，点击【录制宏】；
    在这里插入图片描述

  3. 将宏命名为“Refresh”，在【保存在】下面选择【当前工作簿】【确定】；

  4. 转到“数据”选项卡，点击“全部刷新”；

  5. 转到【开发工具】选项卡【停止录制】；

  6. 测试宏是否正常工作。

    • 转到【开发工具】选项卡【宏】【查看宏】，这将启动一个对话框，允许用户查看文件中的所有宏并运行其中任何一个宏
    • 选择"Refresh”宏并单击执行

为了方便用户操作，可以为宏创建一个按钮：

  1. 在“开发工具”选项卡中单击【插入】下拉菜单中左上角的图标（当鼠标悬停时显示【按钮】（窗体控件）。
    在这里插入图片描述

  2. 在工作表空白处绘制按钮（按住鼠标左键，向下向右拖动，松开鼠标），在弹出【指定宏】对话框中选择“Refresh”宏。

  3. 右击该按钮，选择【编辑文字】，修改按钮文本为“Refresh”。

在这里插入图片描述

  • 启用宏之后，文件必须保存为.xlsm（启用宏的工作簿），用户打开文件时需允许宏运行。
  • 宏只能影响已加载到工作簿的数据，如果查询是仅限连接，宏无法控制。
20.2.2 按特定顺序刷新

Excel 会按连接名称的字母顺序刷新查询，例如：Feb2008 → Jan2008 → Mar2008 → Transactions。某些时候，需要显式指定刷新顺序时，可以使用宏来控制。

20.2.2.1 录制宏。
  • 转到【开发工具】选项卡，【录制宏】，将宏命名为“Refresh_Explicit”，并将其存储在【当前工作簿】中
  • 转到【查询和连接】窗格，右击“Transactions”【刷新】
  • 右击数据透视表中的单元格（比如 G6 单元格）【刷新】
  • 转到【开发工具】选项卡【停止录制】
20.2.2.2 修改宏代码，显式指定每个连接的刷新顺序。

  1. 进入代码编辑界面：转到【开发工具】选项卡【宏】，选择“Refresh_Explicit”【编辑】，此时，将进入 Visual Basic 编辑器，代码如下：

    Sub Refresh_Explicit()
'
' Refresh_Explicit 宏
'
'ActiveWorkbook.Connections("Query - Transactions").RefreshRange("G5").SelectActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub
    • 刷新名为 “Query - Transactions” 的 Power Query 连接
    • 选择了工作表中的单元格 G5，以定位到数据透视表所在的区域
    • 刷新了当前工作表中名为 “PivotTable1” 的数据透视表

  2. 编辑代码，显示指定刷新顺序。

    Sub Refresh()ActiveWorkbook.Connections("Query - Jan2008").RefreshActiveWorkbook.Connections("Query - Feb2008").RefreshActiveWorkbook.Connections("Query - Mar2008").RefreshActiveWorkbook.Connections("Query - Transactions").RefreshWorksheets("Transactions").PivotTables("PivotTable1") _.PivotCache.Refresh
End Sub
    • 直接通过连接名称调用刷新，避免依赖活动工作表。
    • 指定数据透视表所在工作表，提高代码稳定性。

  当你在 Excel 中刷新一个 父查询时，Power Query 引擎会 自动 先刷新它依赖的所有 子查询，所以上述示例其实无需手动指定刷新顺序，仅仅只是为了进行演示。反而如果显式刷新子查询后，再刷新父查询，会导致子查询被刷新两次

  • 第一次更新：手动刷新的子查询（Jan2008 等）；
  • 第二次更新：Transactions 刷新时，其依赖的子查询被隐式重新刷新（Power Query的默认逻辑）。

此方式应该适用于：

  • 子查询数据需加载到工作表（如中间结果需人工检查）
  • 父查询依赖工作表中的数据（而非直接引用子查询）

如果所有查询仅在 Power Query 内部关联（不输出到工作表），则应避免此方法。

20.2.3 刷新所有Power Query查询

当需要仅刷新Excel工作簿中所有由Power Query创建的连接，而忽略其他类型的连接时（如SQL Server、Access等），可以使用以下代码：

' 声明一个公共子过程，其他模块或工作表按钮均可调用
' 名称RefreshPowerQueriesOnly明确表示功能是“仅刷新Power Query连接”。
Public Sub RefreshPowerQueriesOnly()' 定义一个变量cn，类型为WorkbookConnection，用于遍历工作簿中的每个连接Dim lTest As Long, cn As WorkbookConnectionOn Error Resume Next' 遍历当前工作簿（ThisWorkbook）中的所有连接（Connections集合）For Each cn In ThisWorkbook.Connections' cn.OLEDBConnection.Connection用于获取连接的OLE DB连接字符串' InStr函数用于在连接字符串中查找"Microsoft.Mashup.OleDb.1"（Power Query的标识符）' lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1")If Err.Number <> 0 ThenErr.ClearExit ForEnd If' 如果找到（返回值>0），说明是Power Query连接,执行刷新操作If lTest > 0 Then cn.RefreshNext cn
End Sub
20.2.4 同步刷新问题

  当 VBA 代码执行 Connection.Refresh 时，Power Query 会在后台启动刷新任务，但 VBA 不会等待数据刷新完成再执行后续步骤，而是立即执行下一行代码。如果后续代码依赖刷新后的数据（如操作工作表、计算指标、导出结果等），可能会使用 旧数据，导致逻辑错误。

Sub RefreshAsyncExample()' 触发刷新（异步）ActiveWorkbook.Connections("Query1").Refresh  ' ← 刷新开始，但宏不会等待' 以下代码可能在刷新完成前执行！MsgBox "刷新已触发！"  ' ← 此时数据可能尚未更新
End Sub

解决方案（技术复杂，需求小众，可跳过）：

  1. 强制同步等待（轮询检查）：通过循环检查连接是否仍在刷新，强制宏等待，适用于简单依赖，少量查询的场景。

    Sub RefreshSyncExample()Dim cn As WorkbookConnectionSet cn = ActiveWorkbook.Connections("Query1")' 触发刷新cn.Refresh' 等待刷新完成Do While cn.OLEDBConnection.RefreshingDoEvents  ' 允许Excel处理其他事件，避免卡死Application.Wait Now + TimeValue("00:00:01")  ' 每秒检查一次Loop' 以下代码会在刷新完成后执行MsgBox "刷新完成！最新数据已就绪。"
End Sub

  2. 依赖数据模型的事件（高级）：如果查询加载到 Power Pivot 数据模型，可通过 Model.Refresh 方法结合 AfterRefresh 事件实现同步。

    ' 在ThisWorkbook模块中注册事件
Private Sub Workbook_AfterRefresh(ByVal Success As Boolean)If Success ThenMsgBox "数据模型刷新完成！"End If
End Sub

  3. 间接同步（通过工作表事件）：将查询结果输出到工作表，通过监听工作表变化（如 Worksheet_Calculate 事件）判断刷新完成。

20.3 Power BI 中的计划刷新

Power BI 虽然没有宏来控制刷新操作，但提供了在Power BI服务中管理刷新的功能。用户可以按照以下步骤设置计划刷新：

  1. 将解决方案发布到 Power BI；

  2. 登录到 Power BI 服务，为数据集选择适当的工作区；

  3. 转到“数据集 + 数据流”，查找【数据集】，单击【计划刷新】按钮；
    在这里插入图片描述

  4. 在弹出的对话框中，配置数据源凭据（用于 Web 数据源）或安装数据网关（用于本地数据源）；
    在这里插入图片描述

  5. 配置完成之后，可以设置刷新计划。

http://www.xdnf.cn/news/3660.html

相关文章：

  • rails 8 CSS不起效问题解决
  • 异步数据库事务锁：电商库存扣减的防超卖秘籍
  • 【Linux系统篇】：Linux线程控制基础---线程的创建，等待与终止
  • Tesla的战略变化策略（2010～2024）以及给中国汽车厂家的启发
  • Deformable DETR模型解读（附源码+论文）
  • 【算法基础】快速排序算法 - JAVA
  • Cycleresearcher：通过自动化评审改进自动化研究
  • Python 数据智能实战 (10)：智能商品推荐 - LLM “猜你喜欢”
  • v0.6.7/OllamaSetup.exe下载链接
  • SpringSecurity配置（权限认证）
  • 论数据分片技术及其应用
  • 市面上所有大模型apikey获取指南(持续更新中)
  • 进程间通信（IPC）
  • 安卓基础（悬浮窗和摄像）
  • 基于大模型的肾结石诊疗全流程风险预测与方案制定研究报告
  • Oracle无法正常OPEN（四）
  • Spring AI 实战：第一章、Spring AI入门之DeepSeek调用
  • 天翼云ftp服务器搭建详细步骤,ftp服务器路径怎么写?
  • Centos9 安装 RocketMQ5
  • WebSocket分布式实现方案
  • MySQL中的窗口函数
  • Modbus 通讯协议（超详细，简单易懂）
  • Qt 中实现观察者模式（Observer Pattern）
  • Milvus（12）：分析器
  • 虚拟机软件详解
  • AI日报 · 2025年5月03日｜Perplexity 集成 WhatsApp，苹果传与 Anthropic 合作开发 Xcode
  • 青少年编程与数学 02-018 C++数据结构与算法 24课题、密码学算法
  • 【C#】一个类中的接口方法使用static和不使用static的区别
  • aidermacs开源程序使用 Aider 在 Emacs 中进行 AI 配对编程
  • 使用xlwings将excel表中将无规律的文本型数字批量转化成真正的数字