Kai Site

简体中文

English

简体中文

English

外观

Power Query M 函数速查

14316 字约 48 分钟

2026-01-28

本页定位

这篇笔记作为 M 函数参考，用于查找文本、数字、日期、表、列表和记录等常用函数。

学习重点

  • 快速查找常见 M 函数的语法和使用场景。
  • 理解表达式、函数、let、if、error 和元数据等基础概念。
  • 把零散函数归入实际清洗任务，而不是孤立记忆。

文章目录

三、PQ高阶技能：M函数

参考：

Power Query 是一种数据处理工具，广泛应用于 Excel、Power BI、Analysis Services 和 Dataverse 等产品中，其核心功能是筛选和合并，即从支持的多种数据源中“混合”数据。Power Query M 是一种强大的公式语言，用于在 Microsoft Power Query 工具中进行数据的提取、清洗和转换操作。打开主页->高级编辑器，可以看到所有这些步骤的M语言。

在这里插入图片描述
  如果我们不进行鼠标操作，直接在编辑器中编写这些语言，也是可以得到最终的结果的，有了M函数，PQ的数据处理具有很强的可读性和可移植性。

3.1 M函数基本概念

参考《Power Query M 语言规范/介绍》

3.1.1 表达式和值

在 M 语言中，表达式 是可以计算的代码片段，而 值 是表达式计算的结果。例如：

  • 1 是一个表达式，它的值是数字 1。
  • 1 + 1 是一个表达式，它的值是数字 2。

M 语言支持多种类型的值，包括：

  • 基元值：如数字（123）、逻辑值（true 或 false）、文本（"abc"）和 null。

  • 列表值：有序的值序列，用 {} 表示，可以使用位置索引运算符{}进行索引，访问内部的值。 例如：
    在这里插入图片描述

  • 记录值：一组字段，每个字段是一个名称（唯一的文本值）/值对。其语法允许将名称写成不带引号的形式，这种形式也称为“标识符” 。例如：[A = 1, B = 2]，有A,B两个字段，其值分别是1,2。
    记录可以包含在其他记录中，也可以嵌套在其他记录或列表中。 你可以使用查找运算符 []按名称访问记录的字段。
    在这里插入图片描述

// ：指示注释的开头，注释延续至行尾

[ 
    Sales =  
        {  
            [  
                Year = 2007,  
                FirstHalf = 1000,  
                SecondHalf = 1100, 
                Total = FirstHalf + SecondHalf 			 // 2100 
            ], 
            [  
                Year = 2008,  
                FirstHalf = 1200,  
                SecondHalf = 1300, 
                Total = FirstHalf + SecondHalf			 // 2500 
            ]  
        }, 
    TotalSales = Sales{0}[Total] + Sales{1}[Total] 		 // 4600 
]

结果为：

在这里插入图片描述

  • 表值：由列（按名称标识）和行组成的值，每行的内容是一个Record，每列的内容是一个List。表（Table）的创建通常通过函数实现，例如：

    1. #table：从 columns 和 rows 创建表值
      在这里插入图片描述
    2. Table.FromColumns：将多个列表作为列合并成一个表，其语法为Table.FromColumns({列表1, 列表2, ...})
      在这里插入图片描述
    3. Table.FromList和Table.FromRecords：分别用于将列表或记录转为表。
      在这里插入图片描述
      在表中，行标用大括号{ }（无列名，只是列表），比如取第一行的内容：=Table{0}；列标用中括号[ ]（有列名，相当于记录），比如取Name列的内容：=Table[Name]。取第一行Name列的内容：=Table[Name]{0}，或者=Table{0}[Name]
      在这里插入图片描述

  • 函数值：可以接受输入值并返回输出值的代码块。函数编写的方法是在()中列出函数的参数，后跟“转到”符号 => 和定义函数的表达式，例如：(x, y) => x + y。

3.1.2 计算

在 Power Query M 语言中，表达式可以像电子表格（excel）一样，通过名称引用其他表达式的值，而计算顺序会根据这些引用关系自动确定。换句话说，M 语言的计算过程会根据依赖关系来决定先计算哪些部分，从而确保最终结果的正确性。

ABC
1=A1 + 2=B1 * 2

在上面的例子中，Excel 会自动按照依赖关系计算：

  • 先计算 A1 的值（1）。
  • 然后计算 B1 的值（它依赖于 A1 的值）。
  • 最后计算 C1 的值（它依赖于 B1 的值）。

Power Query M 语言也有类似的机制。你可以定义一个记录（类似于电子表格中的单元格区域），其中每个字段可以引用其他字段的值。M 语言会根据这些引用关系自动确定计算顺序。

在这里插入图片描述

记录和列表的成员表达式使用 延迟计算（有在需要时才会计算），即只，而其他表达式使用 迫切计算（立即计算）。

3.1.3 运算符

M 语言支持多种运算符，用于在表达式中执行操作。例如，在表达式 1 + 2 中，数字 1 和 2 是操作数，而+是加法运算符 。

运算符的含义可能因操作数的类型而异。例如，+可用于数字相加或者时间相加：

1 + 2                  						 // numeric addition: 3 
#time(12,23,0) + #duration(0,0,2,0) 		 // time arithmetic: #time(12,25,0)

另一个含义依赖于操作数的运算符示例是组合运算符 &：

"A" & "BC"              // 文本拼接: "ABC" 
{1} & {2, 3}            // 列表合并: {1, 2, 3} 
[ a = 1 ] & [ b = 2 ]   // 记录合并: [ a = 1, b = 2 ]

某些运算符不支持部分值组合。 例如数字不能与文本拼接或相加：

1 + "2"  // error: adding number and text isn't supported
3.1.4 函数

函数是 M 语言中的重要组成部分，用于将输入值映射到输出值。函数的定义包括输入参数和一个表达式主体。例如：

(x) => x + 1                    // function that adds one to a value 
(x, y) =>  x + y                // function that adds two values

函数也是一个值，可以作为值存储在记录或列表中，并可以被调用。例如：

[
    Add = (x, y) => x + y,
    OnePlusOne = Add(1, 1),     // 2
    OnePlusTwo = Add(1, 2)      // 3
]

M 语言提供了一个标准库，包含许多预定义的函数和值，可以直接在表达式中使用。例如：

Number.E                        // Euler's number e (2.7182...) 
Text.PositionOf("Hello", "ll")  // 2

新建一个空查询，在公式标记栏中输入#shared，就可以调取所有内置的M函数列表。右键点击任意一个函数，选择“到表中”，将结果转换为表格。此时，可以通过列筛选或搜索来查找特定的函数。点击Function即可查看该函数的定义、使用方法和示例。

在这里插入图片描述

3.1.5 元数据

元数据是与值相关联的附加信息，对其进行操作不会更改该值或其行为，存储在一个记录中，称为元数据记录。每个值都有一个元数据记录，未指定时则为空。可以通过 meta 关键字添加或修改元数据。例如：

  1. 添加元数据：使用语法 x meta y 将元数据记录值 y 与现有的值 x 相关联
"Mozart" meta [ Rating = 5, Tags = {"Classical"} ]
  1. 使用 Value.Metadata 函数访问元数据
[ 
    Composer = "Mozart" meta [ Rating = 5, Tags = {"Classical"} ], 
    ComposerRating = Value.Metadata(Composer)[Rating] 					// 访问Composer 的元数据记录中的Rating字段，结果为5
]

  1. 新增元数据
    当你对一个已经包含元数据的值再次使用 meta 添加新的元数据时，M 语言会自动将现有的元数据和新的元数据合并。这种合并的结果与直接将两个元数据记录合并后再赋予值的效果是一样的。例如，下面两个表达式虽然写法不同，但最终的结果是一样的。

    ("Mozart" meta [ Rating = 5 ]) meta [ Tags = {"Classical"} ] 
"Mozart" meta ([ Rating = 5 ] & [ Tags = {"Classical"} ])
    • 表达式1：文本值 “Mozart” 被赋予了一个元数据记录 [ Rating = 5 ]，然后再次使用 meta 关键字，为这个值添加新的元数据记录 [ Tags = {“Classical”} ]。
    • 表达式2：使用 & 运算符将两个记录合并为一个记录，然后将合并后的记录作为元数据赋予文本值 “Mozart”。
3.1.6 Let 表达式

Let表达式用于定义一组中间变量，然后在 in 子句中引用这些变量。Let 表达式类似于编程中的局部变量定义，但更灵活，因为它允许在表达式中直接嵌入变量定义。Let表达式的语法如下：

let
    variable1 = expression1,
    variable2 = expression2,
    ...
in
    final_expression
  • variable-list：定义了一系列变量及其对应的表达式。
  • in：表示在这些变量定义的基础上，计算最终的表达式。
let
    x = 1 + 1,
    y = 2 + 2,
    z = y + 1
in
    x + y + z

Let 表达式的计算规则：

  • 作用域：variable-list 中的表达式可以相互引用（如 z = y + 1），在 variable-list 中定义的变量在 in 后的表达式中可用。
  • 计算顺序：variable-list 中的表达式必须先计算完成，才能计算 in 后的表达式。如果某个变量未被引用，则不会进行计算（即按需计算）。

let 表达式允许你将复杂的计算分解为多个步骤，使代码更清晰、更易于维护。例如：

let 
    Sales2007 =  
        [  
            Year = 2007,  
            FirstHalf = 1000,  
            SecondHalf = 1100, 
            Total = FirstHalf + SecondHalf 		// 2100 
        ], 
    Sales2008 =  
        [  
            Year = 2008,  
            FirstHalf = 1200,  
            SecondHalf = 1300, 
            Total = FirstHalf + SecondHalf 		// 2500 
        ] 
  in Sales2007[Total] + Sales2008[Total]   		// 4600

所以，Let 表达式可以看作是隐式记录表达式的语法糖。这意味着Let表达式本质上是一个更简洁、更易读的语法，用来实现与显式记录表达式相同的功能。例如， 下面的表达式与上面的表达式等效：

[     x = 1 + 1,
      y = 2 + 2,
      z = y + 1,
      result = x + y + z 
][result]

语法糖（Syntactic Sugar）是指那些在编程语言中用来简化语法、提高代码可读性和易用性的语法特性。语法糖的核心在于：它只是对现有功能的更友好表达，而不是引入新的功能。在 Python 中，a += 1 是 a = a + 1 的语法糖。

3.1.6 If 表达式

if 表达式用于根据条件选择两个表达式中的一个。例如：

if 2 > 1 then
    2 + 2
else
    1 + 1

对于简单的条件判断，可以通过添加条件列来实现。比如有以下成绩表，通过添加条件列，可以实现评级结果：

在这里插入图片描述
在这里插入图片描述
其M公式为嵌套的if 语句：

if [科目一] >= 90 
then "优秀" 
  else if [科目一] >= 75 
  then "良好" 
   else if [科目一] >= 60 
     then "及格" 
      else "不及格"

如果有两列数据，需要多个判断条件来判定，就无法直接通过添加条件列来实现，而要用IF语句来完成（用 and/or 来连接多个条件）。另外，对于非空数据的判断，还可以用??来简化多重条件的判断（见本文1.5.2）。

在这里插入图片描述

如果是嵌套较多的判断，使用DAX中的SWITCH函数会更简洁，详见《SWITCH函数的秘诀》

在这里插入图片描述

3.1.7 Error

Error表示表达式无法计算出值，一般由运算符和函数遇到错误条件，或使用了错误表达式导致的。Error可以通过 try 表达式捕获和处理。例如：

let Sales = [
    Revenue = 2000,
    Units = 0,
    UnitPrice = if Units = 0 then error "No Units" else Revenue / Units
]
in try Sales[UnitPrice]

如果 Units = 0，则返回一个错误 error "No Units"，否则，计算 Revenue / Units。try 表达式捕获到 Sales[UnitPrice] 的错误，并将其转换为一个包含错误信息的记录。

在这里插入图片描述
  常见的情况是使用默认值替换错误。 otherwise 子句允许你在捕获错误时直接提供一个默认值，而不是返回一个包含错误信息的记录。

try error "negative unit count" otherwise 42
  • error 是一个关键字，用于显式地抛出错误，并提供错误消息 “negative unit count”。
  • 如果计算过程中引发错误（如本例中必然发生的情况），则返回 otherwise 子句中指定的默认值 42

使用场景：

# 处理文本转换错误
try Number.FromText("abc") otherwise null
# 除零错误
try 10 / 0 otherwise 0

总结：建议先能够读懂M语言，并把常用的函数，比如文本函数、字符串函数、日期函数等浏览一遍，知道大概都有什么函数，分别是哪些功能。然后在数据处理过程中碰到鼠标操作难以完成的问题，能想到有哪个M函数可以利用，直接查找和并根据注释使用、或者会修改相应的M函数即可。

3.2 自定义M函数

目前已经有超过700个M函数了，基本上各式各样的数据处理需求都可以使用M函数实现。如果你觉得这些还不够，或者使用起来不是很方便，也可以在PQ中自定义函数。

3.2.1 语法

自定义函数的基本语法是：

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

函数名可以任意写，只要和M语言里的关键字不要重复就行，参数至少有1个，放在括号中，如果有多个参数，以逗号分隔，=>后面就是自定义函数的表达式。

  1. 限定参数类型：为了保证结果的准确性，可以预先限定参数的数据类型，假如有两个参数a和b，限制为数值型，可以这样写：

    函数名=(a as number,b as number)=>表达式

  2. 可选参数：参数还可以是可选的，在参数前加上optional就可以了，比如第二个参数b是可选参数

    函数名=(a,optional b)=>表达式
3.2.2 调用定义好的自定义函数

新建一个空查询，在编辑器重输入：= (x)=>{1..x}，确定后出现参数调用窗口：

在这里插入图片描述
  这个自定义函数只有一个参数x，函数会生成从1到x的序列。指定为自定义函数后，左边的空查询类型就变成了fx,名称可以根据自定义函数的含义进行重命名，这里把函数名改成mylist。在参数x输入10，点击"调用"，就生成了从1到10到一个序列。

在这里插入图片描述

自定义函数的参数不是每次都要手动输入进去，同样可以调用其他数据，比如将上面的这个序列转化为表，然后添加一个自定义列=mylist([Column1])

在这里插入图片描述

这个自定义列的每一行数据，就是从1到第一列数据的序列：

在这里插入图片描述

3.2.3 直接调用自定义函数

自定义函数也并不是一定要提前定义好，还可以在需要的时候随时自定义，并立即使用，还是上面的这张表，需要对第一列累计求和，即计算从第一行到当前行的合计数。添加自定义列，公式可以这样写：

累计=List.Sum(Table.SelectRows(转换为表,(x)=>x[Column1]<=[Column1])[Column1])

在这里插入图片描述

然后就生成一列累计数：

在这里插入图片描述

其中(x)=>x[Column1]<=[Column1]就是一个自定义函数，该自定义函数作为Table.SelectRows的条件返回小于等于当前行的所有行，然后对筛选出的这些行的[Column1]列，使用List.Sum来求和。

这种直接调用自定义函数的方式，只能供该查询使用，而前面生成的自定义函数mylist，可以供该文件内的所有查询使用。

自定义函数还有许多用法，比如生成日期表，详见《日期表制作方式汇总》

3.2.4 自定义函数，轻松转换父子结构数据

参考《PowerQuery自定义函数，轻松转换父子结构数据》

在这里插入图片描述

假如有上面两列数据，一列是邀请人、一列是被邀请人，被邀请人还可以继续邀请其他人，并且所有的下级都视作上级的邀请，如何计算每个人的邀请人数？

这种结构的数据虽然只有两列，但邀请人和被邀请人是可以来回变动的，每个人的角色并不固定，并且下面有多少邀请层级也是未知。这种数据结构就是父子层级结构，邀请人是父级别，被邀请人是子级别。

在PowerBI中，有一类DAX函数：父子函数，专门处理这种结构的计算，它可以将父子层级结构转换为扁平的普通层级结构。

父子函数要求，所有父级列的数据都必须存在于子级列中。对于以上示例，添加一行，将最顶级的人员“李一”，添加到被邀请人中，这也容易理解，所有的人都是被邀请人，只是最顶级的人员，无上级邀请人而已。然后对子级列，也就是每个被邀请人添加一列编号，并相应的把邀请人的编号匹配上去。

在这里插入图片描述
  调用父子函数，可以将将父子结构转换为普通层级结构（过程详见《利用Power BI父子函数，进行特殊数据结构的分析》）：

在这里插入图片描述

  1. 创建自定义函数，重命名为层级转换
    在pq中新建查询>空查询，打开高级编辑器，将该空查询中的公式全部删除，然后将下面的公式复制进去。公式很长，你可以不用管具体细节。
///*
let func =  
 
(ParChTable as table,
ChildKey as text,
ParentKey as text,
LevelColumnName as text) =>
//*/

let
/*/Debug Parameters
ParChTable = SourceData_Loop,
ChildKey = "NodeKey",
ParentKey = "ParentKey",
LevelColumnName = "Name",
*/

    SelectRelevantColumns = Table.SelectColumns(ParChTable, {ChildKey, ParentKey, LevelColumnName}),
    #"Changed Type" = Table.TransformColumnTypes(SelectRelevantColumns ,{ {ChildKey, type text}, {ParentKey, type text} }),
    ReplaceNulls = Table.ReplaceValue(#"Changed Type",null,"",Replacer.ReplaceValue,{ParentKey}),
    //    CleanParChTable = Table.Distinct(ReplaceNulls , {ChildKey, ParentKey}),
    MissingParents = List.Buffer(List.Select(List.Difference(List.Distinct(Table.Column(ReplaceNulls , ParentKey)), List.Distinct(Table.Column(ReplaceNulls , ChildKey))), each _ <> "")),
    AddMissingParents = Table.Buffer(Table.Combine({ReplaceNulls , #table({ChildKey, LevelColumnName, ParentKey}, List.Transform(MissingParents, each {_, "Unknown TopLevel"& Text.From(List.PositionOf(MissingParents, _)), ""}))})),
    #"Merged Queries0" = Table.NestedJoin(AddMissingParents,{ChildKey},AddMissingParents,{ParentKey},"SelectRelevantColumns",JoinKind.LeftOuter),
    CheckIfIsLeaf = Table.AddColumn(#"Merged Queries0", "IsLeaf", each if Table.IsEmpty([SelectRelevantColumns]) then "yes" else "no"),
    #"Replaced Value1" = Table.ReplaceValue(CheckIfIsLeaf,null,"",Replacer.ReplaceValue,{ParentKey, LevelColumnName}),
    AddStartPath = Table.AddColumn(#"Replaced Value1", "Path", each Text.Trim(Record.Field(_, ChildKey)&"|"&Record.Field(_,ParentKey), "|")),
    #"Duplicated Column" = Table.DuplicateColumn(AddStartPath, LevelColumnName, "FirstName"),
    Feed = Table.DuplicateColumn(#"Duplicated Column", ParentKey, "FirstParentKey"),

// Retrieve all parents per row
    fnAllParents = List.Generate(()=>
    [Result= Feed, Level=1, EndlessLoop = false, StopEndlessLoop = false],
    each Table.RowCount([Result]) > 0 and not [StopEndlessLoop],
    each [ Result= let
            #"Merged Queries" = Table.NestedJoin([Result],{ParentKey},AddMissingParents,{ChildKey},"Added Custom",JoinKind.Inner),
            #"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{ParentKey}),
            #"Expanded Added Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Added Custom", {ParentKey, LevelColumnName}, {"ParentKey.1", "Name.1"}),
            #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Added Custom", "ParentKey.1", ParentKey),
            #"Merged Columns" = Table.CombineColumns(#"Duplicated Column",{"Path", "ParentKey.1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Path"),
            #"Merged Columns2" = Table.CombineColumns( #"Merged Columns" ,{LevelColumnName, "Name.1"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),LevelColumnName)
          in
            Table.Buffer(#"Merged Columns2"),
          Level = [Level]+1,
          EndlessLoop = List.Sort(List.Distinct(Table.Column(Result, ChildKey))) = List.Sort(List.Distinct(Table.Column([Result], ChildKey))),
          StopEndlessLoop = [EndlessLoop]
        ]),
    ConvertToTable = Table.FromList(fnAllParents, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandLevel = Table.ExpandRecordColumn(ConvertToTable, "Column1", {"Result", "Level", "EndlessLoop"}, {"Result", "Level", "EndlessLoop"}),
    ExpandLG = Table.ExpandTableColumn(ExpandLevel, "Result", {LevelColumnName, ParentKey, ChildKey, "Path", "FirstName", "FirstParentKey"}, {"Name", "ParentKey", "NodeKey", "Path", "FirstName", "FirstParentKey"}),
    FilterParents = Table.SelectRows(ExpandLG, each ([ParentKey] = null or [ParentKey] = "")),
    #"Removed Columns" = Table.RemoveColumns(FilterParents,{"ParentKey"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{ {"Path", each Text.Trim(_, "|")} }),
    ReverseOrderName = Table.TransformColumns(#"Trimmed Text",{ {"Name", each Text.Combine(List.Reverse(Text.Split(_, "|")), "|")} }),
    ReverseOrderPath = Table.TransformColumns(ReverseOrderName,{ {"Path", each Text.Combine(List.Reverse(Text.Split(_, "|")), "|")} }),
    #"Reordered Columns" = Table.ReorderColumns(ReverseOrderPath,{"NodeKey", "FirstParentKey", "Path", "FirstName", "Level", "Name"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Reordered Columns", "Name", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform({1..Table.RowCount(ConvertToTable)}, each "Level "&Text.From(_))),
    #"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter",{"NodeKey", "FirstParentKey"},ParChTable ,{ChildKey, ParentKey},"Split Column by Delimiter",JoinKind.LeftOuter),
    #"Expanded Split Column by Delimiter" = Table.ExpandTableColumn(#"Merged Queries", "Split Column by Delimiter", List.Difference(Table.ColumnNames(ParChTable), Table.ColumnNames(#"Replaced Value1"))),
    Rename = Table.RenameColumns(#"Expanded Split Column by Delimiter",{ {"Level", "HierarchyDepth"} }),
    Parents = List.Buffer(Rename[FirstParentKey]),
    IsLeaf = Table.AddColumn(Rename, "IsLeaf", each not List.Contains(Parents, [NodeKey])),
    NoOfInterations = List.Count(fnAllParents),
    LastIteration = Table.SelectRows(ExpandLG, each ([Level] = NoOfInterations)),
    EndlessLoops = LastIteration[EndlessLoop],
    IsEndlessLoop = EndlessLoops{0},
    RemainingResults = Table.NestedJoin(IsLeaf, {ChildKey}, LastIteration, {ChildKey}, "x", JoinKind.LeftAnti),
    Custom1 = if IsEndlessLoop then [Message= "The data is in an endless loop. Check Table in  ""Endless Loop""", #" Endless Loop"= LastIteration] meta [ResultsSoFar = RemainingResults] else IsLeaf
in
    Custom1
// /*
, documentation = [
Documentation.Name =  " Table.SolveParentChild
", Documentation.Description = " Creates columns for all parents, multiple parents are supported
" , Documentation.LongDescription = " Creates columns for all parents, multiple parents are supported
", Documentation.Category = " Table
", Documentation.Version = " 2.0: Checking for endless loops 
", Documentation.Source = " local
", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com
", Documentation.Examples = {[Description =  " See: http://wp.me/p6lgsG-sl for more details
" , Code = " 
 ", Result = " 
"]}] 
 in 
  Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
 //*/
  1. 调用以上函数，输入参数。
    在这里插入图片描述
    点击调用之后，即可得到一样的处理结果：

在这里插入图片描述

3.2.5 自定义的批量合并/拆分函数

在这里插入图片描述

假设有以上表格，需将课程和成绩列合并成如下格式：

在这里插入图片描述

在列数不多的时候，可以手动合并，如果列数很多，可以考虑使用自定义函数处理。

=let
多列组合=(需要操作的表 as table, x as number, y as number, optional 固定列终点 as number) as table=>
Table.Combine(List.Transform({1..x},
            each Table.FromColumns(
                                   List.Range( Table.ToColumns(需要操作的表),0,
                                               if 固定列终点=null then 1 else 固定列终点                                                
                                              )&
                                   List.Range( Table.ToColumns(需要操作的表),((_-1)*y+固定列终点),y)
                                  )
                            )          
            ),
元数据=[Documentation.Name="批量多列合并",
      Documentation.Description="可以把多列相同的数据合并到一起。
第1参数是需要操作的表，第2参数x代表的是循环几次，第3参数代表的是多少列循环，第4参数是固定标题的结束位置",
      Documentation.Examples={[Description="第1列为固定列，每3列进行合并存放，一共循环2次",
                            Code="批量多列合并(源,2,3,1)",
                            Result="  "]
                             }
                             ]
in
Value.ReplaceType(多列组合,Value.Type(多列组合) meta 元数据)

点击调用自定义函数，就可以直接得到多列合并的结果：

在这里插入图片描述

  1. 参数1：需要处理的表
  2. 参数2：做几次循环合并(本示例做3次合并列）
  3. 参数3：每次合并几列（本示例是每次进行课程和成绩2列的合并）
  4. 参数4：前面固定几列不做合并（本示例中第1列姓名不需要合并）

以下函数用于批量拆分列，默认从起始位置到最后一列都进行拆分， 且每列都拆成相同的份数。

= let
    批量拆分 = (
        待拆分的表 as table,
        optional 拆分列起始位置 as number,
        optional 分隔符 as text
    ) as table =>
    let
        // 处理参数默认值
        拆分列起始位置 = if 拆分列起始位置 = null then 2 else 拆分列起始位置,
        分隔符 = if 分隔符 = null then "," else 分隔符,

        // 获取所有列名
        columns = Table.ColumnNames(待拆分的表),

        // 确定要拆分的列列表
        splitColumns = List.Skip(columns, 拆分列起始位置 - 1),

        // 处理空表的情况
        safeTable = if Table.IsEmpty(待拆分的表) then 待拆分的表 else
        // 使用List.Accumulate逐个处理拆分列
        List.Accumulate(
            splitColumns,
            待拆分的表,
            (state, col) =>
                let
                    // 获取当前列的第一个值（处理null和空值）
                    firstRow = if Table.IsEmpty(state) then null else Record.FieldOrDefault(state{0}, col, null),
                    splitParts = if firstRow = null then List.Repeat(null, 1) else Text.Split(firstRow, 分隔符),
                    numSplits = List.Count(splitParts),
                    // 生成新列名
                    newColNames = List.Transform(List.Numbers(0, numSplits), each col & "_" & Text.From(_)),
                    // 拆分当前列
                    splitCol = Table.SplitColumn(
                        state,
                        col,
                        Splitter.SplitTextByDelimiter(分隔符, QuoteStyle.None),
                        newColNames
                    )
                in
                    splitCol
        )
    in
        safeTable
in
    批量拆分

在这里插入图片描述

  • 起始位置：可选，默认为2，即第2列到最后一列都需要拆分。
  • 分隔符：可选，默认为逗号

3.3 数字函数

公式描述示例
Number.Round([number], [digits])四舍五入到指定小数位数Number.Round(3.14159, 2) → 3.14
Number.Abs([number])返回数字的绝对值Number.Abs(-5) → 5
Number.IntegerDivide([num1], [num2])执行整数除法Number.IntegerDivide(10, 3) → 3
Number.Power([base], [exponent])计算幂运算Number.Power(2, 3) → 8
Number.Mod([num1], [num2])取模（余数）Number.Mod(10, 3) → 1
Number.ToText([number], [format])将数字转换为文本Number.ToText(123) → “123”
Number.Random()生成一个随机数（0到1）Number.Random() → 0.456789

当我们在 Power Query 中进行保留 2 位小数的操作，可以通过界面功能实现：

在这里插入图片描述
  仔细检查会发现，部分结果并非是我们所预期的“四舍五入”，比如3.145 被舍入成了 3.14，而非 3.15。这是因为Power Query中的默认舍入方法是一种被称为“Banker‘s rounding”（银行家舍入）的舍入方法，也被称为 “四舍六入五成双”。

当遇到数值舍入位数的后一位是 5 时，它距离舍入位数两端的数值都相同，那它会被舍入到距离它最近的偶数。如果要进行四舍五入，可以使用Number.Round函数，他有两个参数：

  • digits：舍入位数
  • roundingMode：舍入模式。
舍入模式值舍入的最后一位为5时
RoundingMode.Up0向上舍入。
RoundingMode.Down1向下舍入。
RoundingMode.AwayFromZero2向远离零的方向舍入。
RoundingMode.TowardZero3向零舍入。
RoundingMode.ToEven，默认值4舍入到最接近的偶数。

RoundingMode.Up 和 RoundingMode.AwayFromZero 都可以做到对正数进行“四舍五入”，如果是负数，则应使用后者。另外还有其它几个函数：

函数名描述示例示例结果
Number.RoundDown向下取整，忽略小数部分Number.RoundDown(3.14159)3
Number.RoundUp向上取整，忽略小数部分Number.RoundUp(3.14159)4
Number.Floor向下取整到最接近的整数Number.Floor(3.999)3
Number.Ceiling向上取整到最接近的整数Number.Ceiling(3.001)4
Number.Int截取整数部分，忽略小数部分Number.Int(3.999)3

3.4 文本函数

3.4.1 常见文本函数
公式描述示例
Text.Start([text], [n])
Text.End([text], [n])提取文本的前n个/后n个字符Text.Start("Hello", 1,2) → “Hel”
Text.Middle([text], [start],[n])
Text.Range()提提取start位置开始的n个字符，两个函数作用相同= Text.Middle("Hello World", 6, 5) → “World”
Text.StartsWith(text,substring,comparer)
Text.EndsWith检测文本是否以substring开头或结尾，第三个参数用于控制是否忽略大小写Text.StartsWith("Hello, World", "hello", Comparer.OrdinalIgnoreCase) → True
Text.PadStart([text], [totalLength], [paddingCharacter])在文本开头填充字符Text.PadStart("123", 5, "0") → “00123”
Text.PadEnd([text], [totalLength], [paddingCharacter])在文本末尾填充字符Text.PadEnd("123", 5, "0") → “12300”
Text.Replace([text], [old], [new])
Text.ReplaceRange()替换文本中的部分字符
根据位置更换字符Text.Replace("the dog", "the", "a") → “a dog”
Text.Trim([text,[ character]])
Text.TrimEnd去除文本字符，默认去除空格 ,
去除文本末尾的字符Text.Trim(" Hello ") → “Hello”
Text.TrimEnd("03.487700000", "0") → “03.4877”
Text.Length([text])返回文本的长度Text.Length("Hello") → 5
Text.Upper([text]) 、Text.Lower([text])将文本转换为大写、小写
Text.Contains([text], [substring])检查文本是否包含子字符串Text.Contains("Hello", "ell") → true
默认区分大小写，使用第三个参数Comparer.OrdinalIgnoreCase可忽大小写
Text.ToList([text])将文本转换为字符列表Text.ToList("Hello") -> {"H", "e", "l", "l", "o"}
Text.Reverse反转字符Text.Reverse("123") → “321”
Text.Combine({text1,text2},separator)合并文本列表Text.Combine({"Seattle", "WA"}, ", ") → “Seattle, WA”
3.4.2 根据分隔符提取文本
  1. Text.BeforeDelimiter：提取指定分隔符之前的部分。
    • text：要处理的文本。
    • delimiter：分隔符。
    • index：可选指定分隔符的出现次数，可以是数字（表示从开头计算）或包含 RelativePosition.FromEnd 的列表（表示从文本末尾开始计算）。
# 获取第一个连字符前的部分，结果是"111"
Text.BeforeDelimiter("111-222-333", "-")
# 获取第二个连字符前的部分，结果是"111-222"
Text.BeforeDelimiter("111-222-333", "-", 1)
# 获取倒数第二个连字符前面的部分， RelativePosition.FromEnd表示从末尾开始计算，结果也是"111"
Text.BeforeDelimiter("111-222-333", "-", {1, RelativePosition.FromEnd})
  1. Text.AfterDelimiter：用法与上一个函数相同
  2. Text.BetweenDelimiters：
    • text：要处理的文本。
    • startDelimiter：起始分隔符。
    • endDelimiter：结束分隔符。
    • startIndex（可选）：指定起始分隔符的出现次数，可以是数字（从开头计算）或包含 RelativePosition.FromEnd 的列表。
    • endIndex（可选）：指定结束分隔符的出现次数，可以是数字（从开头计算）或包含 RelativePosition.FromEnd 的列表。
# 1. 获取（第一个）左括号及其随后（第一个）右括号之间的部分，输出"222"
Text.BetweenDelimiters("111 (222) 333 (444)", "(", ")")

# 2.  获取第二个左括号及其随后第一个右括号之间的部分，输出"444"
Text.BetweenDelimiters("111 (222) 333 (444)", "(", ")", 1, 0)

# 3. 获取 倒数第二个左括号及其随后第二个右括号之间的部分输出，"222) 333 (444"
Text.BetweenDelimiters("111 (222) 333 (444)", "(", ")", {1, RelativePosition.FromEnd}, {1, RelativePosition.FromStart})
  1. Text.Split：根据指定分隔符将文本分隔为一个列表。如果指定的是多个分隔符，则将其视为一个整体。
  2. Text.SplitAny：根据指定分隔符将文本分隔为一个列表。如果指定的是多个分隔符，则每个分隔符单独区分。
# 1. 将"ven"视为整体，结果是{"Ad","ture"}
Text.Split("Adventure","ven")

# 2. 将".｜"视为单个分隔符
Text.SplitAny("Jamie|Campbell|Admin|Adventure Works|www.adventure-works.com", ".|")

在这里插入图片描述

3.4.3 文本选择与删除函数（Text.Remove 和 Text.Select）

下面介绍两个常用的文本处理 M 函数：Text.Remove 和 Text.Select。原始数据如下：

在这里插入图片描述

  1. 去除大写英文：如果只想要中文名，可以在添加自定义列时使用Text.Remove函数，去掉所有大写英文字母。
姓名=Text.Remove([客户],{"A".."Z"})

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

姓名=Text.Remove([客户],{"A".."z"})
  1. 去除所有中文字符：
英文名=Text.Remove([客户],{"一".."龟"})

在这里插入图片描述

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

  1. 去除标点符号
Text.Remove("a,b;c", {",",";"})       //"abc"
  1. 复合提取：如果有更多种类的文本数据不规则的堆放在一起，想把联系方式提取出来，第二个参数还可以这样写：
联系方式=Text.Remove([客户],{"A".."z","一".."龟"})

另外，还有类似功能的函数 Text.RemoveRange，用于移除指定位置的 n 个字符。

# 删除文本中，从位置 2 开始的 2 个字符。
Text.RemoveRange("ABEFC", 2, 2)

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

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

在这里插入图片描述

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

3.5 列表函数

3.5.1 常见列表函数
  1. 信息函数
函数描述示例
List.Count返回列表中元素的数量List.Count({1, 2, 3, 4, 5}) → 5
List.IsEmpty检查列表是否为空List.IsEmpty({}) → true
  1. 聚合函数
函数描述示例
List.Sum计算列表中所有数值的总和List.Sum({1, 2, 3}) → 6
List.Max返回列表中的最大值List.Max({1, 2, 3}) → 3
List.Min返回列表中的最小值List.Min({1, 2, 3}) → 1
List.Average计算列表中数值的平均值List.Average({1, 2, 3}) → 2
  1. 筛选函数
函数描述示例
List.Select根据条件筛选列表中的元素List.Select({1, 2, 3, 4}, each _ > 2) -> {3, 4}
List.RemoveNulls移除列表中的空值List.RemoveNulls({1, null, 3}) -> {1, 3}
  1. 生成函数
函数描述示例
List.Repeat重复某个值生成列表List.Repeat({"a"}, 3) -> {"a", "a", "a"}
List.Numbers生成一个数字列表List.Numbers(1, 5) -> {1, 2, 3, 4, 5}
  1. 转换函数
函数描述示例
List.Transform对列表中的每个元素应用函数List.Transform({1, 2, 3}, each _ * 2) -> {2, 4, 6}
List.Distinct去除列表中的重复值List.Distinct({1, 1, 2, 3}) -> {1, 2, 3}
  1. 排序函数
函数描述示例
List.Sort对列表进行排序List.Sort({3, 1, 2}) -> {1, 2, 3}
3.5.2 列表生成
3.5.2.1 简单列表
  • 递增1的数字列表：新建查询，利用{开始值…结束值}的方式可生成递增数字列表。

在这里插入图片描述

  • 递减1的数字列表：以上方式写递减列表，比如写成{5..1}，将返回空表。此时可以利用List.Reverse函数反转一下：=List.Reverse（ {1..5}）。
3.5.2.2 生成任意数字序列（List.Numbers）

List.Numbers语法为：

List.Numbers(start as number, count as number, optional increment as nullable number) as list
  • start：列表中的初始值。
  • count：列表长度
  • increment：间隔

比如= List.Numbers(1,5,2)生成列表[1,3,5,7,9]，=List.Numbers(1.1 , 5 , -0.1)生成列表[1.1,1,0.9,0.8,0.7]

3.5.3 日期序列（List.Dates、List.Transform、List.Generate）

假设有以下数据，列出了每个产品的价格有效日期（起止日期），如何生成其有效日期列表？

产品名称价格开始日期结束日期
A102024-01-052024-01-09
B202024-01-052024-01-12
A112024-01-102024-01-20
B212024-01-132024-01-20
C312024-01-122024-01-20
  1. 使用List.Dates函数，其语法为：
List.Dates(start as date, count as number, step as duration) as list
  • start：开始日期
  • count：序列长度
  • step：日期间隔，为#duration(1, 0, 0, 0)的形式，表示间隔粒度——天、小时、分钟和秒。

添加以下自定义列即可：

List.Dates(
    [开始日期],
      Duration.Days([结束日期]-[开始日期])+1,
     #duration(1, 0, 0, 0)
 )
  1. List.Transform函数：先使用Number.From将日期转换为数字，再利用List.Transform将数字列表转换为日期列表。
List.Transform(
    {Number.From([开始日期])..Number.From([结束日期])},
    Date.From
 )
  1. List.Generate：这个函数非常灵活，允许用户根据指定的逻辑动态生成包含多个元素的列表，通常用于创建循环、递增或递减的序列，或者执行更复杂的生成逻辑，其语法为：
List.Generate(initial as function, condition as function, next as function, transformation as function) as list
  • initial: 定义了生成列表时的起始值。它是一个函数（如 ()=>起始值），表示你需要从哪个值开始生成列表。
  • condition: 条件函数，返回一个布尔值，指定何时停止列表生成。当返回 false 时，生成过程停止。
  • next: 定义了如何从当前值生成下一个值，每次列表生成的迭代会调用此函数。
  • transformation（可选）: 允许你对每个生成的元素应用一个转换函数。该函数可以用来对每个元素进行某种处理。
  1. 生成一个从 1 开始，步长为 2，直到 10 为止的列表{1, 3, 5, 7, 9}
List.Generate(
    () => 1,               // 起始值为 1
    each _ <= 10,          // 生成条件：当当前值小于等于 10 时继续
    each _ + 2,            // 每次迭代时当前值加 2
    each _                 // 转换函数：不做任何转换
)
  1. 生成日期序列
let 
    a = [开始日期],               // 起始日期
    b = [结束日期]             	 // 结束日期
in
    List.Generate(
        () => a,             	 // 初始值：开始日期
        each _ <= b,         	 // 生成条件：当前日期小于或等于结束日期
        each Date.AddDays(_, 1)	 // 递增规则：每次增加一天
    )

在这里插入图片描述

3.5.4 示例：统计文本中字符出现的次数

以下面这个数据为例，如何计算出每一行的文本中，斜杠字符"/"有多少个？
在这里插入图片描述

  1. 利用M直接统计字符法：先通过 Text.ToList将这一串文本拆分成每个字符的列表，然后通过List.Select筛选其中为"/"字符，最后通过List.Count统计字符的个数。
    在这里插入图片描述
  2. 替换后计算长度差异：
    在这里插入图片描述

3.6 表函数

3.6.1 常见表函数
公式描述示例
Table.SelectRows([table], [condition])根据条件筛选行Table.SelectRows(#table, each [Column1] > 10) → 筛选列1大于10的行
Table.TransformColumns([table], [transformations])转换表中的列``Table.TransformColumns(#table, {“Column1”, Text.Proper})` → 将列1的每个单词首字母大写
Table.AddCustomColumn([table], [newColumnName], [formula])添加自定义列Table.AddCustomColumn(#table, "NewColumn", each [Column1] * 2) → 添加新列，值为列1的两倍
Table.RemoveColumns([table], [columns])移除指定列Table.RemoveColumns(#table, {"Column1"}) → 移除列1
Table.RenameColumns([table], [renames])重命名列Table.RenameColumns(#table, { {"Column1", "NewName"} }) → 将列1重命名为NewName
Table.Sort([table], [order])对表进行排序Table.Sort(#table, { {"Column1", Order.Ascending} }) → 按列1升序排序
Table.Distinct([table])去除重复行Table.Distinct(#table) → 移除表中的重复行
Table.Combine({[table1], [table2]})合并多个表Table.Combine({#table1, #table2}) → 将表1和表2合并为一个表
Table.Pivot([table], [listOfColumns], [keyColumn], [valueColumn])对表进行透视操作Table.Pivot(#table, {"Column1"}, "KeyColumn", "ValueColumn") → 按列1进行透视
3.6.2 创建自定义表格进行批量列名修改

导入到PowerBI中的数据，如果想要更改数据的列名，可以在PQ编辑器中直接双击列名进行修改。如果需要修改的列名比较多，可以使用M函数Table.RenameColumns进行批量修改（其实双击更改列名也是利用这个函数）。以下是示例数据：

在这里插入图片描述

双击标题把“日期”更改为“订单日期”，在编辑栏就会看到这个M函数：

= Table.RenameColumns(
    更改的类型,
    { {"日期", "订单日期"} }
)
  • 第一个参数是表名（“更改的类型”是上一个步骤名）
  • 第二个参数是由原列名和新列名组成的一个列表，比如这里的{ {“日期”, “订单日期”} }。

当需要更改的列名比较多时，就可以利用第二个参数生成一个列表来批量更改。比如先制作一个列名修改表，输入原列名和新列名并导入到PQ中：

在这里插入图片描述

现在想办法把这个表变成一个list列表，并把这个列表作为 Table.RenameColumns的第二个参数就可以了。

  1. 转置列名修改表
    在这里插入图片描述

  2. 将列名表变为list列表：插入步骤= Table.ToColumns(转置表)
    在这里插入图片描述

  3. 批量更改列名：在需要更改的查询表中，插入步骤，编辑栏输入以下内容，就可以一次性更改完所有的列名了。

    = Table.RenameColumns(
    更改的类型,
    列名,
    MissingField.Ignore
)

在这里插入图片描述

如果原列名的列表中，含有表中不存在的列名，则会报错.为了避免出现这种情况，添加第三个参数MissingFiled.Ignore，这样当出现列名不匹配的情况时，会自动跳过，而不会报错。

3.6.3 规范数据表示（“北京”、“北京市”、“Beijing”）

从不同数据源得到的数据，同一个概念会有不同的表示，比如地名“北京”，不同的数据源中可能是“北京”，“北京市”，“Beijing”甚至“北平”。多数情况下，简单替换的方法并不可行。Table.AddFuzzyClusterColumn函数可以对数据进行模糊匹配并分组，从而规范数据源中的数据。

3.6.3.1 简单用法

Table.AddFuzzyClusterColumn函数语法如下：

Table.AddFuzzyClusterColumn（
    源表，  //【必要】
    目标列列名， //【必要】
    规整后的列名， //【必要】
    其他参数 //【可选】
）

以下表为例，里面包含英文城市温哥华和西雅图（如下），你可以留意到其中的单词大小写不一，且存在拼写错误（如seattl）:

在这里插入图片描述
针对以上数据，添加步骤，编写M语句如下：

= Table.AddFuzzyClusterColumn(
        #"Changed Type",
        "Location",
        "Location_Cleaned"
    )

这样，我们就可以得到如下结果，数据得到完美规范：

在这里插入图片描述

3.6.3.2 进阶用法（可选参数）

在基本用法的基础上，需要理解并合理使用函数参数。首先利用以下M语句模拟一个火车站站名不规范的简易数据表（当然你也可以手动先做好这个数据）：

--------
let
 DATA =
  Table.FromRecords(
    {
      [ID = 1, Location = "北京西站"],
      [ID = 2, Location = "北京西站"],
      [ID = 3, Location = "广州东站"],
      [ID = 4, Location = "广州东站"],
      [ID = 5, Location = "广州东火车站"],
      [ID = 6, Location = "西九龙站"],
       [ID = 7, Location = "西九龙站"],
      [ID = 8, Location = "西九龙火车站"],
      [ID = 9, Location = "香港西九龙站"],
      [ID = 10, Location = "HK West Kowloon Railway Station"]
     },
     type table [ID = nullable number, Location = nullable text]
    ) 
in
 DATA
--------

在这里插入图片描述

  1. Culture：可选参数，指定要处理的语言，默认为英文。本示例设为中文后，空格问题得到处理。

    = Table.AddFuzzyClusterColumn(
  DATA,//源表名
    "Location",
    "Location_Cleaned",
    [Culture="cn-ZH"]
 )

在这里插入图片描述

  1. Threshold：可选参数，表示纠正数据的阈值，越低表示纠正力度越低，容错率越高，默认值是0.8（0-1）。此处将该参数设定为0.6后，就解决了“广州东站zhan”这种混杂了拼音的记录。

    = Table.AddFuzzyClusterColumn(
    DATA,
    "Location",
    "Location_Cleaned",
    [
        Culture="cn-ZH",
       Threshold=0.6
      ]
    )

在这里插入图片描述
  至此还未能解决表中第六行至第十行的命名杂乱问题，因为PQ没有足够的依据去智能地给他们归类，PQ并不清楚西九龙是香港的一个地名，当然更不可能了解"HKWest Kowloon Railway Station"的含义。

  1. TransformationTable：可选参数。允许我们自定义一个转换表，以使得PQ可以按照转换表的定义来规范数据，从而彻底解决上述问题。

  • 定义转换表TRANS_TABLE：

    = Table.FromRecords(
  {
     [From = "西九龙站", To = "香港西九龙站"],
     [From = "西九龙火车站", To = "香港西九龙站"],
     [From = "HK West Kowloon Railway Station", To = "香港西九龙站"]
  },
     type table [From = nullable text, To = nullable text]
    )

在这里插入图片描述

  • 在参数 TransformationTable中引用该表，这样我们就完美地解决了问题。
= Table.AddFuzzyClusterColumn(
        DATA,
        "Location",
        "Location_Cleaned",
        [   
            Culture="cn-ZH",
            Threshold=0.6,
            TransformationTable=TRANS_TABLE
        ]
    )

在这里插入图片描述

Table.AddFuzzyClusterColumn函数在数据源不规范时十分有用，掌握它的用法就可以轻松处理这类问题，但对于企业BI解决方案而言，通过ETL等方式从源头上解决数据杂乱的问题才是最规范的做法。

3.6.4 数据筛选（List.Contains、Table.SelectRows）
3.6.4.1 手写条件列表

在这里插入图片描述
  假设有上述表格，需要筛选出客户名称是"吴雷"、“孙静”、"林永"的数据，最简便的做法，可以直接使用筛选功能。但是当需要筛选的数据比较多时，这样手动一个个勾选也不方便，此时，可以使用M函数实现。

  1. 插入步骤：
    在这里插入图片描述
  2. 编辑M函数：
= Table.SelectRows(
    更改的类型, 
    each List.Contains({"吴雷","孙静","林永"},[客户])
)

在这里插入图片描述

  • List.Contains：判断列表list（参数1）中是否包含值value（参数2），返回True或False。如果需要忽略大小写，添加可选参数3：Comparer.OrdinalIgnoreCase。
  • Table.SelectRows：从 table 中返回符合 condition 的行。例如：
# 示例一：筛选表中 [CustomerID] > 2的行

Table.SelectRows(
    Table.FromRecords({
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
    }),
    each [CustomerID] > 2
)
# 示例二：筛选表中 名称不包含“B”的行

Table.SelectRows(
    Table.FromRecords({
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
    }),
    each not Text.Contains([Name], "B")
)
3.6.4.2 添加条件表

如果筛选的名称很多，每次手写列表很麻烦。可以先添加一个条件列，将其作为List.Contains的第一个参数。

  1. 新建表，输入筛选内容，右键该列，点击“深化”，将它变成一个list列表。
    在这里插入图片描述
    重命名该表：
    在这里插入图片描述
  2. 修改M函数：
= Table.SelectRows(
    更改的类型, 
    each List.Contains(条件表,[客户])
)

在这里插入图片描述

如果是用DAX来实现上述需求，可以直接这样来做，新建表：

表 = FILTER('示例表','示例表'[客户] IN '条件表')

在这里插入图片描述

3.7 日期函数

3.7.1 常见日期函数
公式描述示例
Date.From([value])将文本、数字或其他日期格式的值转换为日期类型，如果输入的值无法解析为日期，可以结合 try 和 otherwise 来处理错误。Date.From("2024-12-25") → #date(2024, 12, 25)
Date.FromText([text])将文本转换为日期Date.FromText("2024-12-25") → #date(2024, 12, 25)
Date.ToText([date], [format])将日期转换为文本Date.ToText(#date(2024, 12, 25), "yyyy-mm-dd") → “2024-12-25”
Date.Year([date])提取日期的年份Date.Year(#date(2024, 12, 25)) → 2024
Date.Month([date])提取日期的月份Date.Month(#date(2024, 12, 25)) → 12
Date.Day([date])提取日期的日Date.Day(#date(2024, 12, 25)) → 25
Date.AddDays([date], [days])在日期上增加天数（同样也有增加年、月、季度、周的函数）Date.AddDays(#date(2024, 12, 25), 1) → #date(2024, 12, 26)
Date.DayOfWeek([date])返回日期所在的星期几（同样有返回年、月、季度值的函数）Date.DayOfWeek(#date(2024, 12, 25)) → 3 (星期三，从0开始计数)
Date.StartOfYear([date])返回日期所在年的第一天（同样有返回年、月、季度第一天的函数）Date.StartOfYear(#date(2024, 12, 25)) → #date(2024, 1, 1)
3.7.2 使用公式计算来统一日期格式

在这里插入图片描述
  假如有上述文本格式的时间数据，有的是完整的时分秒，而有的只有小时或者分钟，该如何进行统一转换呢？基本思路是将文本的小时、分钟和秒根据单位，替换为相应的计算表达式。

在这里插入图片描述

将"小时"替换为"*3600+“，将"分钟"替换为”*60+"、将"秒"替换为空，就可以将以上的时间字符串转换为数学表达式：

在这里插入图片描述

然后利用M函数Expression.Evaluate来计算这个表达式，添加自定义列。计算时，由于数据中有些时间的秒是不全的，导致上面的替换操作后，有的值最后带有“+”，所以这里用了Text.TrimEnd先将尾部的字符"+"清除掉。

Expression.Evaluate(
     Text.TrimEnd([时间],"+")
 )

在这里插入图片描述

如果想要的是小时数，在这个基础上再除以3600即可。解决这种需求的方法不止这一种，就像代码的写法不止一种。很多数据的处理，不要把它想的太复杂。

3.7.2 Date.FromText

当我们将数据导入到Power Query时，首先需要将每个字段调整为正确的类型，比如将文本格式日期数据，转为日期格式。

在这里插入图片描述
  左边是我们常用的"年月日"格式，右边是美式的"月日年"格式，这两种文本格式都可以点击字段名左边的格式符号，直接调整为日期类型：

在这里插入图片描述

如果碰到其它格式的日期，可能无法正确解析，此时可以使用Date.FromText 函数来处理。比如使用Date.FromText([日期],"en-GB")来处理英国以及北欧常用的日月年格式的日期：

在这里插入图片描述

如果是更奇怪的日期格式，可以使用Format参数来调整：

在这里插入图片描述

在这里插入图片描述
在这里插入图片描述

格式符描述示例格式符描述示例
d月份中的日期 (1到31)13（表示13号）M月份 (1到12)2（表示2月）
dd月份中的日期 (两位数字)13（表示13号，始终两位数）MM月份 (两位数字)02（表示2月，始终两位数）
MMM月份的缩写Feb（表示2月）MMMM月份的全称February（表示2月）
y年份 (2位数)25（表示2025年）yy年份 (两位数字)25（表示2025年）
yyyy年份 (四位数)2025（表示2025年）h小时 (1到12)3（表示下午3点）
hh小时 (两位数字)03（表示3点，始终两位数）H小时 (0到23)15（表示15点）
HH小时 (两位数字，0到23)15（表示15点，始终两位数）m分钟 (0到59)5（表示5分钟）
mm分钟 (两位数字)05（表示5分钟，始终两位数）s秒 (0到59)9（表示9秒）
ss秒 (两位数字)09（表示9秒，始终两位数）fff毫秒123（表示123毫秒）
tt上午/下午标记AM 或 PMt上午/下午标记的单字符A 或 P
z时区缩写（如 UTC）UTCzzz时区偏移 (小时:分钟)+08:00
Z时区（RFC 3339 格式）+08:00
  1. 使用自定义格式和德语区域性进行转换：
Date.FromText("30 Dez 2010", [Format="dd MMM yyyy", Culture="de-DE"])
# 返回2010/12/30
  1. 将1400从文本格式的公历年转为回历年：
Date.FromText("1400", [Format="yyyy", Culture="ar-SA"])
# 返回1979/11/20

Format=“yyyy”，所以1400被解析为年份；[Culture=“ar-SA”] 中ar-SA表示代表的是阿拉伯语（沙特阿拉伯）。

  1. 将以下意大利格式的缩写日期转为标准日期格式（意大利语中，gen.,lug.,apr.分别表示1月、7月和4月）：

在这里插入图片描述

3.7.3 Date.ToText

Date.ToText用于返回 date 的文本化表示形式，其语法为：

Date.ToText(date as nullable date, optional options as any, optional culture as nullable text) as nullable text
  • date (必需)：你要转换为文本的日期值
  • options（可选）：你可以提供一个记录参数来指定额外的指令，包括以下字段： 
# 示例1：输出 “16/02/2025”
Date.ToText(#date(2025, 2, 16), "dd/MM/yyyy", "en-GB")
# 示例2：输出 “31 Dez 2010”
Date.ToText(#date(2010, 12, 31), [Format="dd MMM yyyy", Culture="de-DE"])
# 示例3：输出 “1400”
Date.ToText(#date(2000, 1, 1), [Format="yyyy", Culture="ar-SA"])

3.8 日期时间函数

创建并操纵 datetime 值和 datetimezone 值

名称说明示例
DateTime.AddZone将时区信息添加到日期/时间值。DateTime.AddZone(#datetime(2024, 12, 25, 15, 30, 0), 8) → #datetimezone(2024, 12, 25, 15, 30, 0, 8)
DateTime.Date获取日期时间值的日期部分（年、月、日）DateTime.Date(#datetime(2024, 12, 25, 15, 30, 0)) → #date(2024, 12, 25)
DateTime.From根据给定值创建日期/时间。DateTime.From("2024-12-25T15:30:00") → #datetime(2024, 12, 25, 15, 30, 0)
DateTime.FromText从本地和通用日期时间格式创建日期时间。DateTime.FromText("2024-12-25T15:30:00Z") → #datetimezone(2024, 12, 25, 15, 30, 0, 0)
DateTime.LocalNow返回本地时区中的当前日期和时间。DateTime.LocalNow() → #datetime(2024, 12, 25, 15, 30, 0)（示例值）
DateTime.Time返回给定日期/时间值的时间部分。DateTime.Time(#datetime(2024, 12, 25, 15, 30, 0)) → #time(15, 30, 0)
DateTime.ToRecord返回包含日期/时间值部分的记录。DateTime.ToRecord(#datetime(2024, 12, 25, 15, 30, 0)) → [Year = 2024, Month = 12, Day = 25, Hour = 15, Minute = 30, Second = 0]
DateTime.ToText返回日期/时间值的文本表示形式。DateTime.ToText(#datetime(2024, 12, 25, 15, 30, 0), "yyyy-MM-dd HH:mm:ss") → “2024-12-25 15:30:00”
#datetime使用年、月、日、小时、分钟和秒创建一个日期/时间值。#datetime(2024, 12, 25, 15, 30, 0) → #datetime(2024, 12, 25, 15, 30, 0)

3.9 Power Query报错处理（以源数据更改列名导致报错举例）

Power Query很强大，但也非常容易出错，比如经常会碰到源文件的列名修改以后，导致在Power Query中刷新后报错，这种常见错误该如何处理呢，下面逐步讲解其一般处理思路。

  1. 数据准备：假设数据源是下面这个Excel文件，将其导入Power Query，如果源数据格式不发生变动，将会正常刷新。
    在这里插入图片描述
  2. 源数据的列名修改：将源文件最后一列的列名从"金额"改为"销售额"，PQ刷新后将报错：找不到表的"金额"列。
    在这里插入图片描述
  3. 找到开始出错的步骤：从上到下用鼠标依次点击右侧的步骤，检查是从哪个步骤开始出错的。此示例中，点到第三个步骤“更改的类型”，发现是在这个步骤开始报错的。由于数据中不存在"金额"这一列，所以导致报错了。
    在这里插入图片描述
  4. 更正错误：点击上一步“提升的标题”，在这个界面，可以看到最后一列的列名是“销售额”而不是"金额"，所以只需“更改的类型”这个步骤的公式中，将“金额”改成“销售额”即可，并不需要去修改源文件或者重新导入一遍。
    在这里插入图片描述

一般的PQ界面报错，都可以按这种方式一个个步骤去排查，从出错步骤的编辑栏公式中找原因并做相应修改。再说回本章节的那个问题，如果这个列名经常被改动，比如明天又被人改成了"金额"、后天又改成了"销售收入"，那么每次刷新都会报错，都要手动去修改也挺麻烦。可以在上一步"提升的标题"中，用函数获取第四列列名：Table.ColumnNames(提升的标题){3}。

Table.ColumnNames返回表中所有列名组成的一个列表，索引从0行开始。

在这里插入图片描述

3.10 数据脱敏

详见《Power BI可视化大赛火热进行中，利用这个方法轻松脱敏你的数据》

3.11 十进制/十六进制的转换

详见《十进制/十六进制的转换》

本文转自 https://blog.csdn.net/qq_56591814/article/details/145668977，如有侵权，请联系删除。

2023-2025 Powered by Kai with ❤️