Power Query 中用于数据整理的转换函数

适用于： Azure 数据工厂 Azure Synapse Analytics

使用 Azure 数据工厂中的数据整理，可以通过将 Power Query M 脚本转换为数据流脚本，进行云规模的无代码敏捷数据准备和整理。 ADF 与 Power Query Online 集成，并通过执行 Spark 使用数据流 Spark 基础结构，使 Power Query M 函数可用于数据整理。

目前，尽管在创作过程中可以使用 Power Query M 函数，但并非所有的 Power Query M 函数都支持用于数据处理。 在构建 Power Query 混合时，如果函数不受支持，系统将提示以下错误消息：

UserQuery : Expression.Error: The transformation logic is not supported as it requires dynamic access to rows of data, which cannot be scaled out.

下面列出了支持的 Power Query M 函数。

列管理

• 选择：Table.SelectColumns
• 删除：Table.RemoveColumns
• 重命名：Table.RenameColumns、Table.PrefixColumns、Table.TransformColumnNames
• 重新排序：Table.ReorderColumns

行筛选

使用 M 函数 Table.SelectRows 根据以下条件进行筛选：

• 相等和不相等
• 数字、文本和日期比较（但不是日期/时间）
• 数字信息，如 Number.IsEven/Odd
• 使用 Text.Contains、Text.StartsWith 或 Text.EndsWith 的文本包含
• 日期范围，包括所有“IsIn”日期函数
• 使用 and、or 或 not 条件的组合

添加和转换列

以下 M 函数添加或转换列：Table.AddColumn、Table.TransformColumns、Table.ReplaceValue、Table.DuplicateColumn。 下面是支持的转换函数。

• 数值算术
• 文本串联
• 日期和时间算术（算术运算符、Date.AddDays、Date.AddMonths、Date.AddQuarters、Date.AddWeeks、Date.AddYears）
• 持续时间可用于日期和时间算术，但必须转换为另一种类型，然后才能写入接收器（算术运算符、#duration、Duration.Days、Duration.Hours、Duration.Minutes、Duration.Seconds、Duration.TotalDays、Duration.TotalHours、Duration.TotalMinutes、Duration.TotalSeconds）
• 大多数标准、科学和三角数字函数（Operations、Rounding 和 Trigonometry 下除了 Number.Factorial、Number.Permutations 和 Number.Combinations 以外的所有函数）
• 替换（Replacer.ReplaceText、Replacer.ReplaceValue、Text.Replace、Text.Remove）
• 位置文本提取（Text.PositionOf、Text.Length、Text.Start、Text.End、Text.Middle、Text.ReplaceRange、Text.RemoveRange）
• 基本文本格式（Text.Lower、Text.Upper、Text.Trim/Start/End、Text.PadStart/End、Text.Reverse）
• 日期/时间函数（Date.Day、Date.Month、Date.YearTime.Hour、Time.Minute、Time.Second、Date.DayOfWeek、Date.DayOfYear、Date.DaysInMonth）
• If 表达式（但分支必须具有匹配的类型）
• 作为逻辑列的行筛选器
• Number、text、logical、date 和 datetime 常量

合并/联接表

• Power Query 将生成嵌套联接（Table.NestedJoin；用户还可以手动写入 Table.AddJoinColumn）。 然后，用户必须将嵌套联接列扩展为非嵌套联接（Table.ExpandTableColumn，在任何其他上下文中不受支持）。
• 可以直接写入 M 函数 Table.Join，以避免执行额外的扩展步骤，但用户必须确保联接表中没有重复的列名
• 支持的联接类型：Inner、LeftOuter、RightOuter、FullOuter
• Value.Equals 和 Value.NullableEquals 都支持用作键值等同性比较器

Group by

使用 Table.Group 聚合值。

• 必须与聚合函数一起使用
• 支持的聚合函数：List.Sum、List.Count、List.Average、List.Min、List.Max、List.StandardDeviation、List.First、List.Last

排序

使用 Table.Sort 对值进行排序。

减少行

保留和删除前几行，保留范围（对应的 M 函数，仅支持计数，而不支持条件：Table.FirstN、Table.Skip、Table.RemoveFirstN、Table.Range、Table.MinN、Table.MaxN）

不支持的已知函数

函数	状态
Table.PromoteHeaders	不支持。 可以通过在数据集中设置“第一行作为页眉”来实现相同的结果。
Table.CombineColumns	这是一个不直接支持的常见方案，但可通过添加一个连接两个给定列的新列来实现。 例如，Table.AddColumn（RemoveEmailColumn、"Name"、each [FirstName] & " " & [LastName]）
Table.TransformColumnTypes	大多数情况下都支持此项。 不支持以下方案：将字符串转换为货币类型，将字符串转换为时间类型，将字符串转换为百分比类型，以及用区域设置进行转换。
Table.NestedJoin	仅执行联接将导致验证错误。 必须对列进行扩展，才能使其正常工作。
Table.RemoveLastN	不支持删除最后几行。
Table.RowCount	不支持，但可通过添加包含值 1 的自定义列，然后使用 List.Sum 聚合该列来实现。 支持 Table.Group。
行级别错误处理	当前不支持行级别错误处理。 例如，若要筛选出列中的非数字值，一种方法是将文本列转换为数字。 无法转换的每个单元都将处于错误状态，需要对其进行筛选。 此方案在向外扩展的 M 中无法进行。
Table.Transpose	不支持

M 脚本解决方法

SplitColumn

下面列出了一个备用项，用于按长度和位置拆分

• Table.AddColumn(Source, "First characters", each Text.Start([Email], 7), type text)
• Table.AddColumn(#"Inserted first characters", "Text range", each Text.Middle([Email], 4, 9), type text)

可从功能区中的“提取”选项访问此选项

Table.CombineColumns

• Table.AddColumn(RemoveEmailColumn, "Name", each [FirstName] & " " & [LastName])

透视

• 从 PQ 编辑器中选择“透视转换”，然后选择透视列

• 接下来，选择值列和聚合函数

• 单击“确定”时，将看到编辑器中数据已使用透视值进行更新
• 还会看到一条警告消息，指出转换可能不受支持
• 若要修复此警告，使用 PQ 编辑器手动展开透视列表
• 从功能区中选择“高级编辑器”选项
• 手动展开透视值列表
• 将 List.Distinct() 替换为值列表，如下所示：

#"Pivoted column" = Table.Pivot(Table.TransformColumnTypes(#"Changed column type 1", {{"genres", type text}}), {"Drama", "Horror", "Comedy", "Musical", "Documentary"}, "genres", "Rating", List.Average)
in
  #"Pivoted column"

设置日期/时间列的格式

要在使用 Power Query ADF 时设置日期/时间格式，请按照以下设定来设置格式。

1. 在 Power Query UI 中选择列，然后选择“更改类型”>“日期/时间”
2. 你将看到一条警告消息
3. 打开高级编辑器，并将 TransformColumnTypes 更改为 TransformColumns。 根据输入数据指定格式和区域性。

#"Changed column type 1" = Table.TransformColumns(#"Duplicated column", {{"start - Copy", each DateTime.FromText(_, [Format = "yyyy-MM-dd HH:mm:ss", Culture = "en-us"]), type datetime}})

相关内容

了解如何在 ADF 中创建数据整理 Power Query。