一、需求
如果一个excel文件中，有N个列标题一模一样的表格栖息在同一个sheet上。这些表格彼此之间用数量不等的空白列区隔，有可能最左侧的表格前面也有数量不等的空白列，那么如何用PowerQuery把这个N个表格合并成一个？
二、分析
如果按照严格意义来理解需求中的“表格”。那么它是类似于这样的：
栖息在sheet中的表格.png
但很多时候，我们把区域也叫做表格，类似于这样的：
栖息在sheet中的区域.png
三、解决sheet中栖息多个区域的问题
示例文件下载地址：http://pan.baidu.com/s/1nvUb7df 密码：3oz8
如果是表格最好办，难的是区域。目前找到了三种解决这个问题的方法：
方法一：利用Table.Combine()
此方法是Excel120-A群（453524740）施阳大神提供。最简洁，当然理解起来也是最难的。尤其是“each”和“_”的使用，还有List.Transform()的使用也需要很好的想象力才行。
后面两种方法，尤其是第三种方法借鉴了很多施阳大神处理问题的思路，获益匪浅。
注释是我根据自己的理解所加。后两种方法没加注释。
let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
//下面这一步是处理空白列的关键开始
转换成列 = Table.ToColumns(源),
//下面这一步利用List.Skip()来去除第一个表格左侧的空白列；如果没有空白列会返回从第一个表格到最后一个表格的列所生成的list列表。
去除最开始的空白列 = List.Skip(转换成列,each _{0}=null),
//下面这一步假设表格与表格之间有空白列存在，得出的结果其实就是每个表的列数；如果没有空白列，则不能这样处理。
连续列数 = List.Count(List.FirstN(去除最开始的空白列,each _{0} <> null)),
//下面这一步开始剔除空列
筛选不为空的列 = List.Select(去除最开始的空白列,each _{0}<>null),
//获取总列数的目的是为再下一步确定表格打定基础
总列数 = List.Count(筛选不为空的列),
//下面这一步，施阳大神在确定了表格个数后，直接把每个表格的数据装在对应的list列了。
确定表格个数 = List.Transform({0..总列数/连续列数-1},each List.Range(筛选不为空的列,_*连续列数,连续列数)),
//下面是关键一步，我之前用别的方法也是卡在把list转成表格的这一步上。注意最后那个Table.FromColumns没有括号也没有参数，很神奇的用法。
把list中的list合并成table = List.Transform(确定表格个数,Table.FromColumns),
//由于原始表格有很多空白行，所以下面这一步剔除这些空白行。
筛选table中的null = List.Transform(把list中的list合并成table,each Table.SelectRows(_,each _[Column1] <> null)),
//对每一个list中的表格，提升第一行为标题。这样就避免到最后合并表格后，每个表格的标题都被当做一个row进入到真正的数据里边去，那样一来要剔除这些表头就很麻烦，至少这个方法失去通用性了。
对list中的table提升标题 = List.Transform(筛选table中的null,Table.PromoteHeaders),
table合并 = Table.Combine(对list中的table提升标题)
in
table合并
方法二：利用group和Table.Combine()
方法二是Excel120-A群（453524740）雷公子在我前期基础上花费十分钟不到做出来的。当时我根据群里畅心的提示，用Table.ToColumns()折腾，卡在把最后获取的list转化成表这一步过不去，于是去群里请教高手。雷公子伸出了援手，在我去厕所期间就做出来了，并基本保留了前面我写的那些“不优雅”的代码。但是当时的原始数据最开始并没有空白列，我最先折腾的步骤，到中间就会出错。所以结合施阳大神的思路，对代码进行了简单优化。
let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
转换成列 = Table.ToColumns(源),
去除最开始的空白列 = List.Skip(转换成列,each _{0}=null),
连续列数 = List.Count(List.FirstN(去除最开始的空白列,each _{0} <> null)),
总列数 = List.Count(List.Select(去除最开始的空白列,each _{0}<>null)),
自定义1 = List.Select(去除最开始的空白列,each _{0}<>null),
转换为表 = Table.FromList(自定义1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
已添加索引 = Table.AddIndexColumn(转换为表, "索引", 0, 1),
被用整数除的列 = Table.TransformColumns(已添加索引, {{"索引", each Number.IntegerDivide(_, 连续列数), Int64.Type}}),
分组的行 = Table.Group(被用整数除的列, {"索引"}, {{"计数", each _[Column1]}}),
已添加自定义1 = Table.AddColumn(分组的行, "Custom", each Table.FromColumns([计数])),
删除的其他列1 = Table.SelectColumns(已添加自定义1,{"Custom"}),
自定义3 = List.Transform(删除的其他列1[Custom],each Table.SelectRows(_,each _[Column1] <> null)),
自定义5 = List.Transform(自定义3,Table.PromoteHeaders),
自定义4 = Table.Combine(自定义5)
in
自定义4
方法三：利用unpivot
由于雷公子通过方法二教会我怎么把list转成table，因此我自己摸索了这个方法。并结合施阳大神的代码思路（其实有些步骤是直接照抄），用unpivot做了一个结果出来：
let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
转换成列 = Table.ToColumns(源),
去除最开始的空白列 = List.Skip(转换成列,each _{0}=null),
连续列数 = List.Count(List.FirstN(去除最开始的空白列,each _{0} <> null)),
总列数 = List.Count(List.Select(去除最开始的空白列,each _{0}<>null)),
逆透视的列 = Table.UnpivotOtherColumns(源, {}, "属性","值"),
删除的列 = Table.RemoveColumns(逆透视的列,{"属性"}),
删除的顶端行 = Table.Skip(删除的列,连续列数*(总列数/连续列数-1)),
已添加索引 = Table.AddIndexColumn(删除的顶端行, "索引", 0, 1),
已添加自定义 = Table.AddColumn(已添加索引, "自定义", each Number.IntegerDivide([索引],8)),
更改的类型 = Table.TransformColumnTypes(已添加自定义,{{"值", type text}}),
分组的行 = Table.Group(更改的类型, {"自定义"}, {{"计数", each _[值], type table}}),
删除的其他列 = Table.SelectColumns(分组的行,{"计数"}),
已添加自定义1 = Table.AddColumn(删除的其他列, "自定义", each Table.FromList([计数])),
删除的其他列1 = Table.SelectColumns(已添加自定义1,{"自定义"}),
已添加自定义2 = Table.AddColumn(删除的其他列1, "自定义.1", each Table.Transpose([自定义])),
删除的其他列2 = Table.SelectColumns(已添加自定义2,{"自定义.1"}),
#"展开的“自定义.1”" = Table.ExpandTableColumn(删除的其他列2, "自定义.1"),
提升的标题 = Table.PromoteHeaders(#"展开的“自定义.1”", [PromoteAllScalars=true])
in
提升的标题
当我们有无数个文件都是示例的原始数据那样时，可以采用以上三种方法中的任意一种（哪个方法运行效率最高，还没测试过），所要做的只是修改下第一行的“源”。
再次感谢Excel120-A群（453524740）群里施阳、雷公子及其他小伙伴，有这个群有这个氛围有大家积极交流讨论，真好，获益匪浅。这个帖子的目的也是把这次经典讨论的结果形成案例，留下一个处理类似问题的通用方法。
四、解决sheet上栖息多个表格的问题
如果sheet上栖息的都是表格，处理起来就非常方便，而且对表格位置没有任何限制，错位对齐都可以。比如下面这样的：
错位表格.png
不过处理这样的情形时，保持原始数据文件和PowerQuery查询处理文件分开就很有必要了。因为如果直接在原始数据文件上创建PowerQuery，这意味着要从“表格”创建查询。但原始数据文件一个sheet上栖息了多个表格，无论我们选择所有表格，还是只选择一个表格，都会提示：
表不能相互重叠.png
因此，我们要在单独的文件上创建PowerQuery查询，将整个原始数据文件作为数据源引入。
引入整个原始数据文件时，我们有两种选择：
一是按照PowerQuery引入数据源界面提示，选择多个表格：
多选表格的引入方式.png
但是这种方式很麻烦：当表格很多时，我们要一个一个去选；并且PowerQuery会为每一个表格创建一个查询，想象下有几千个表格的情形；最后还得把这些表格合并起来，更麻烦。
多选后会为每个表格创建一个查询.png
所以，明智的做法是任意选择一个表格，生成查询后，编辑这个查询。
删除多余步骤.png
删除其他步骤，只保留“源”这一步。
然后筛选“Kind”列，选择“Table”类型，这样就一次性将所有表格都筛选出来了，展开，收工。这种方式，唯一的缺点是不够直观，但是操作简便，而且可以适用于任何表格栖息于sheet上的场景。
过滤Table的操作方式.png
五、举一反三
另外，在这个例子的基础上，还可以举一反三，进行扩展：
1.如果有表格在某些表格的正下方，要如何通用化处理？主要是要剔除这些下面的表格的标题行。
2.假如有些表格不在示例文件的表格正下方，而是随机分布的，该如何处理？
网友评论