power-query-m

Power Query（M 语言）

概述

Power Query 是 Power BI 中的数据转换引擎，使用 M 函数式语言。它负责将数据从源提取、转换并加载（ETL）到数据模型中。要实现高性能数据刷新，理解查询折叠、步骤优化和 M 语法至关重要。

查询折叠

查询折叠会将 M 步骤转换为源数据的原生查询（SQL、OData 等），把计算推送到数据源，而不是由 mashup 引擎处理。

如何检查折叠：

1. 右键单击“应用的步骤”中的某一步骤 >“查看原生查询”——如果此选项呈灰色，说明折叠已中断
2. 使用“查询诊断”（“工具”>“开始诊断”）查看发送了哪些查询

可折叠的步骤（常见）：

操作	SQL 转换
删除列	SELECT（列列表）
筛选行	WHERE 子句
对行排序	ORDER BY
分组依据	GROUP BY
重命名列	列别名
更改类型（基本类型）	CAST
合并查询（数据库）	JOIN
前 N 行	TOP / LIMIT
删除重复项	DISTINCT

会中断折叠的步骤：

操作	原因
添加自定义列（复杂）	M 表达式无法转换为 SQL
透视/取消透视（有时）	取决于数据源的功能
与不可折叠的数据源合并	无法推送跨数据源联接
Table.Buffer	显式将数据实体化到内存中
自定义步骤后重新排序	折叠一旦中断，后续步骤都无法折叠
日期/时间转换（部分）	数据源特定的函数差异

**黄金法则：**将可折叠的步骤放在不可折叠的步骤之前。折叠一旦中断，所有后续步骤都会由 mashup 引擎执行。

M 语言基础

Let 表达式（查询结构）

每个 Power Query 查询都是一个 let...in 表达式：

let
    Source = Sql.Database("server", "database"),
    Filtered = Table.SelectRows(Source, each [Status] = "Active"),
    Renamed = Table.RenameColumns(Filtered, {{"OldName", "NewName"}}),
    Typed = Table.TransformColumnTypes(Renamed, {{"Amount", type number}})
in
    Typed

数据类型

M 类型	描述
type text	字符串/文本
type number	小数
Int64.Type	整数（64 位整数）
type date	仅日期
type datetime	日期和时间
type datetimezone	日期、时间和时区
type duration	时间间隔
type logical	布尔值（true/false）
type binary	二进制数据
type null	Null 值
Currency.Type	定点小数（4 位）
Percentage.Type	百分比

常见表函数

// Filter rows
Table.SelectRows(table, each [Column] > 100)

// Add column
Table.AddColumn(table, "NewCol", each [Col1] * [Col2], type number)

// Remove columns
Table.RemoveColumns(table, {"Col1", "Col2"})

// Select columns (keep only these)
Table.SelectColumns(table, {"Col1", "Col2", "Col3"})

// Rename columns
Table.RenameColumns(table, {{"Old1", "New1"}, {"Old2", "New2"}})

// Change types
Table.TransformColumnTypes(table, {{"Col1", type number}, {"Col2", type text}})

// Replace values
Table.ReplaceValue(table, "old", "new", Replacer.ReplaceText, {"Column"})

// Group by
Table.Group(table, {"GroupCol"}, {
    {"Sum", each List.Sum([Amount]), type number},
    {"Count", each Table.RowCount(_), Int64.Type}
})

// Merge (JOIN)
Table.NestedJoin(left, {"KeyCol"}, right, {"KeyCol"}, "Merged", JoinKind.LeftOuter)

// Expand merged columns
Table.ExpandTableColumn(merged, "Merged", {"Col1", "Col2"})

// Pivot
Table.Pivot(table, List.Distinct(table[PivotCol]), "PivotCol", "ValueCol")

// Unpivot
Table.UnpivotOtherColumns(table, {"KeepCol1", "KeepCol2"}, "Attribute", "Value")

// Sort
Table.Sort(table, {{"Col1", Order.Ascending}, {"Col2", Order.Descending}})

// Remove duplicates
Table.Distinct(table, {"KeyCol1", "KeyCol2"})

// Combine/Append tables
Table.Combine({table1, table2, table3})

// Buffer (force materialization)
Table.Buffer(table)

列表函数

// Generate a sequence
{1..100}
List.Numbers(1, 100)
List.Dates(#date(2024,1,1), 365, #duration(1,0,0,0))

// Transform
List.Transform({1,2,3}, each _ * 2)

// Filter
List.Select({1,2,3,4,5}, each _ > 3)

// Aggregate
List.Sum(list), List.Average(list), List.Min(list), List.Max(list)

// Generate with custom logic (pagination pattern)
List.Generate(
    () => [Page = 0, Data = GetPage(0)],
    each [Data] <> null,
    each [Page = [Page] + 1, Data = GetPage([Page] + 1)],
    each [Data]
)

参数和动态数据源

为特定环境的连接创建参数：

// Define parameter in Power Query UI or M:
// Name: ServerName, Type: Text, Current Value: "prod-server.database.windows.net"

// Use in query:
let
    Source = Sql.Database(ServerName, DatabaseName),
    ...

动态数据源模式：

let
    BaseUrl = "https://api.example.com/v2/",
    Endpoint = BaseUrl & "data?page=",
    GetPage = (pageNum as number) =>
        let
            url = Endpoint & Number.ToText(pageNum),
            response = Json.Document(Web.Contents(url))
        in
            response[results],
    AllPages = List.Generate(
        () => [i = 1, res = GetPage(1)],
        each List.Count([res]) > 0,
        each [i = [i] + 1, res = GetPage([i] + 1)],
        each [res]
    ),
    Combined = List.Combine(AllPages),
    AsTable = Table.FromList(Combined, Record.FieldValues,
        type table [id = Int64.Type, name = text, value = number])
in
    AsTable

错误处理

// Try/otherwise pattern
let
    result = try SomeRiskyOperation() otherwise "default"
in
    result

// Try with error record inspection
let
    attempt = try Number.FromText("abc"),
    output = if attempt[HasError]
        then "Error: " & attempt[Error][Message]
        else attempt[Value]
in
    output

// Replace errors in a column
Table.ReplaceErrorValues(table, {{"Column1", null}, {"Column2", 0}})

// Remove error rows
Table.RemoveRowsWithErrors(table, {"Column1", "Column2"})

自定义连接器

使用 Power Query SDK 构建自定义 Power Query 连接器：

1. 安装 Power Query SDK（VS Code 扩展）
2. 创建包含 DataConnector.pq 文件的 .mproj 项目
3. 实现带身份验证的数据源函数
4. 打包为 .mez 文件
5. 部署到 Documents\Power BI Desktop\Custom Connectors 或网关

基本连接器结构：

section MyConnector;

[DataSource.Kind="MyConnector", Publish="MyConnector.Publish"]
shared MyConnector.Contents = (url as text) =>
    let
        source = Web.Contents(url),
        json = Json.Document(source)
    in
        json;

MyConnector = [
    Authentication = [
        Key = [],
        OAuth = [...]
    ],
    Label = "My Custom Connector"
];

MyConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = {"My Connector", "Connect to My Service"}
];

性能优化

技巧	影响
将可折叠的步骤放在前面	高——将处理推送到数据源
尽早删除未使用的列	高——减少数据量
在联接前尽早筛选	高——减少行数
除非必要，否则避免使用 Table.Buffer	中——防止不必要的数据实体化
折叠失败时使用原生查询	高——绕过 mashup 引擎
对暂存查询禁用“包含在报表刷新中”	中——跳过不必要的刷新
使用 Table.Partition 实现并行加载	中——并行加载大型表
正确设置隐私级别	中——设置不当会阻止折叠

更多资源

参考文件

• references/m-patterns-cookbook.md —— 常见 M 模式：Web API 分页、增量加载、JSON 扁平化、CSV 处理、SharePoint 文件夹合并