Power Query 系列 (15) - Table 和 List 转换应用案例
原创Power Query 系列 (15) - Table 和 List 转换应用案例
原创
本文通过一个数据处理的案例，说明 table 和 list 相互转换在 Power Query (PQ) 数据处理中的作用。假设有下面的销售数据，需要按月份进行汇总，输出为右边的格式：
这种数据处理，无论是 Excel 还是 PQ，都比较简单，比如在 PQ 中，先对月份进行逆透视：
得到如下格式：
再对 Month 进行分组：
得到下面的结果：
为了特意练习 Table 和 List 之间数据转换，今天我们用另外一种方法来求解。首先介绍本篇用到的几个函数：
Table.ColumnNames 获取 table 的列名称，返回值为 list 类型。
Table.ColumnNames(table as table) as list比如刚才 SalesByMonth 表的数据为：
则：
这是一个 list 类型的数据，如果我们需要把这个 list 转换成列表，可以使用 Table.FromList 函数，函数的语如下：
Table.FromList(list as list,
optional splitter as nullable function,
optional columns as any,
optional default as any,
optional extraValues as nullable number) as table在查询编辑器中，选择右键菜单的【到表】菜单，可以将 list 转换为 table:
在弹出对话框中，使用默认选项：
这样就将 list 转换为 table:
图形界面操作，对应公式为：
Table.FromList(源, Splitter.SplitByNothing(), null, null, ExtraValues.Error)对照上面 Table.FromList 的语法，一般我们只关心三个参数即可。比如刚才的步骤，如果用下面的三个参数更为简洁：
Table.FromList(源, null, {"Month"})·Table.ToColumns· 函数将 table 按照列分解为 list，相当于 {list, list, list}。即使只有一行，也会分解为 {list} 的格式。比如 SalesByMonth 查询，下面的查询：
let
Source = SalesByMonth,
ConvertedToList = Table.ToColumns(Source)
in
ConvertedToList在查询编辑器中显示为（选中的为第一列）：
下面我们用上面介绍的方法，对数据进行处理。首先得到 查询 SalesByMonth 的列名，筛选出月份：
let
Source = List.Select(
Table.ColumnNames(SalesByMonth),
(x) => Text.Contains(x, "月"))
in
Source此时查询编辑器界面：
将 list 转换为 table：在查询编辑器界面中，点击公式栏 fx 图标，添加一个步骤：
此时查询编辑器界面如下：
对应的 M 语言脚本如下：
let
Source = List.Select(
Table.ColumnNames(SalesByMonth),
(x) => Text.Contains(x, "月")),
ConvertedToTable = Table.FromList(Source, null, {"Month"})
in
ConvertedToTable添加一个步骤，得到月份对应的销售数据。这一步相对复杂一点，建议在高级编辑器中编写：
let
Source = List.Select(
Table.ColumnNames(SalesByMonth),
(x) => Text.Contains(x, "月")),
ConvertedToTable = Table.FromList(Source, null, {"Month"}),
// Added sales data
AddedSalesData = Table.AddColumn(
ConvertedToTable,
"SalesData",
(row) => Table.SelectColumns(SalesByMonth, row[Month]))
in
AddedSalesDataSalesData 的每一个单元格，根据行的月份，获取了对应月份的销售数据：
但此时，单元格的值是一个 table，为了计算出合计数，需要将 sub-table 转换为 list。使用 Table.ToColumns 函数。在高级编辑器中，将代码变更如下：
let
Source = List.Select(
Table.ColumnNames(SalesByMonth),
(x) => Text.Contains(x, "月")),
ConvertedToTable = Table.FromList(Source, null, {"Month"}),
// Added sales data
AddedSalesData = Table.AddColumn(
ConvertedToTable,
"SalesData",
(row) => Table.ToColumns(Table.SelectColumns(SalesByMonth, row[Month])))
in
AddedSalesData
回到查询编辑器界面，此时 SalesData 列变为 list:
需要对列的结构化数据进行展开。点击展开图标，选择添加到新行，完成后查询编辑器已经将单元格的 list 展开，可以看到月份的销售数据：
此步骤对应的 M 语言脚本：
let
Source = List.Select(
Table.ColumnNames(SalesByMonth),
(x) => Text.Contains(x, "月")),
ConvertedToTable = Table.FromList(Source, null, {"Month"}),
// Added sales data
AddedSalesData = Table.AddColumn(
ConvertedToTable,
"SalesData",
(row) => Table.ToColumns(Table.SelectColumns(SalesByMonth, row[Month]))),
// Expand sales data
ExpandedSales = Table.ExpandListColumn(AddedSalesData, "SalesData")
in
ExpandedSales因为 SaleData 这一列是 list 类型，添加一列，使用 List.Sum 函数计算出合计数。
回到查询编辑器界面，删除不需要的字段。完成后 M 语言脚本如下：
let
Source = List.Select(
Table.ColumnNames(SalesByMonth),
(x) => Text.Contains(x, "月")),
ConvertedToTable = Table.FromList(Source, null, {"Month"}),
// Added sales data
AddedSalesData = Table.AddColumn(
ConvertedToTable,
"SalesData",
(row) => Table.ToColumns(Table.SelectColumns(SalesByMonth, row[Month]))),
// Expand sales data
ExpandedSales = Table.ExpandListColumn(AddedSalesData, "SalesData"),
// Added total column
AddedTotalCol = Table.AddColumn(
ExpandedSales,
"TotalQty",
each List.Sum([SalesData])),
RemovedUnrelated = Table.RemoveColumns(AddedTotalCol,{"SalesData"})
in
RemovedUnrelated原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。