在现实工作中，我们还会遇到更复杂的结构化表格，比如3x2、3x3层级的结构化表格。通过前面几节的学习，我们可以总结出的核心规律是：行标题上的多层标题需要在填充后合并列，列标题上的多层标题则需要转置后填充。我们用N表示行标题的层级数，用M标题的层级数，则NxM层级结构化表格转换过程的具体 步骤如下：1）在Excel中行维度的前N-1列都有合并单元格，所以需要向下填充N-1列；2）使用指定分隔符合并行维度的前N列，测试表格转换为1xM层级的情形。4)列维度的前M-1列都包含合并单元格，所以需要向下填充M-1列。我们以3x2层级结构化表格为例。如下图所示。行标题上有年、季度和月份3个维度，列标题上有产品种类和产品名称两个维度。1）向下填充前两列，即年与季度列，对应的M函数：Table.FillDown()。2）使用分隔符"|"合并年、季度和月份这3列，对应M函数：Table.CombineColumns()。3）转置，对应M函数：Table.Transpose()。4)向下填充第一列，即产品种类列，对应M函数：Table.FillDown()。5）提升标题，对应M函数：Table.PromoteHeaders()。6）选中前两列，逆透视其他列，对应M函数：Table.UnpivotOtherColumns()。7）按分隔符"|"拆分属性列，对应M函数：Table.SplitColumn()。8）重命名列，对应M函数：Table.RenameColumns()。通过以上8个步骤就完成了3x2层级结构化表格的降维转换。转换后结果如下图所示。既然数据转换的过程是固定的，借助M函数就可以将整个过程"打包"成自定义函数。自定义函数时最重要的操作之一是找到标准化输入的部分，也就是函数的参数部分。然后明确相应步骤需要用到的M函数及其参数的类型。此案例中我们可以将源表、列维度以及行维度作为参数建立自定义函数，行及列维度以列表的形式提供，以标题为成员，比如，行标题是｛年，季度，月份｝，列标题是｛产品种类，产品名称｝。通过List.Count()函数可以获得N及M的值。通过文本参数指定逆透视以后数值列的名称，如销量或者销售额。1）源：用于转换的源表，就是加载到Power Query里的数据表；2）行标题：包含行维度标题的列表，需要以列表的形式输入；3）列标题：包含列维度标题的列表，需要以列表的形式输入；4）值名称：逆透视后数值列的名称，需要以文本形式输入。对于函数的主体，我们可以结合之前的转换步骤进行修改，选择的查询需要是行及列维度都大于或等于2的。我们这里复制查询"2x2层级"并进行修改。修改以后自定义函数如下图所示。(源表, 行标题, 列标题, 值名称)=>
let
所有表列 = Table.ColumnNames(源表),
N = List.Count(行标题),
M = List.Count(列标题),
//提取表列及N、M的数值，方便后续步骤直接调用
向下填充行 = Table.FillDown(源表,List.FirstN(所有表列,N-1)),
合并列 = Table.CombineColumns(向下填充行,List.FirstN(所有表列,N),
Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"已合并"),
转置表 = Table.Transpose(合并列),
//填充行标题后，转置表
向下填充列 = Table.FillDown(转置表,List.FirstN(所有表列,M-1)),
提升标题 = Table.PromoteHeaders(向下填充列, PromoteAllScalars=true]),
逆透视其他列 = Table.UnpivotOtherColumns(提升标题,
List.FirstN(Table.ColumnNames(提升标题),M),
"属性", 值名称),
//填充并提升列标题，后逆透视
按分隔符拆分列 = Table.SplitColumn(逆透视其他列, "属性", Splitter.SplitTextByDelimiter("|",QuoteStyle.Csv), 行标题),
重命名列 = Table.RenameColumns(按分隔符拆分列,
List.Zip(
{
List.FirstN(Table.ColumnNames(按分隔符拆分列),M),
列标题
})
)
in
重命名列
降维函数定义好以后就可以在应用步骤中直接调用了。调用函数时需要保证参数的格式是正确的。源表的顶端不能有空行，也不能有其他无关的空列。行标题及列标题按要求以列表的形式提供，且按正确的顺序列出。所有的标题都需要是文本类型的。比如转换3x3层级结构化表格时，按以下格式调用函数即可。=降维函数（源表，｛"年","季度","月份"｝,{"地区","产品种类","产品名称"},"销售额"）降维函数适用于任意NxM层级结构化表格，对于1x1,2x1,2x3等层级的结构化表格同样使用。
夜雨聆风
