PowerQuery 汇总系列 数据源标题不在第一行

---

文章难免有疏漏，若发现某处编辑有误，可评论留言。

---

相关问题也可找作者进行咨询，DIY定制皆可。

---

Authors

@ 樊笼星海

@ w180361

@ Email：ou251@outlook.com

---

  在汇总数据的时候，发现我们需要的数据并不是从第一行开始，假如数据字段标题是从第5行开始。

  那么这种情况下，Excel.Workbook在修改第二参数后，还能正确的识别到标题吗？

---

1. 此节将涉及到的内容

• 转置，表格的行、列取值
• 将单个复杂的操作逻辑应用到多个需要重复的操作逻辑上
• 将自动生成的公式转换为自定义函数
• 应用自定义函数

---

2. 标题不在第一行

2.1 是否能够正确识别？

源数据：数据字段标题从第5行开始

1. 使用PowerQuery获取此工作表数据，在不修改任何M公式的情况下，能够识别到标题。

2. 修改Excel.Workbook的第二参数，能够识别到标题
   

3. 可以看到的是，即使数据字段标题从第5行开始，PowerQuery依然能够识别到数据是从哪一行开始的，这是因为工作表中的有效数据是从第5行开始。

但如果工作表中第1行至4行中任意一个单元格有空格，那就会出现问题。好在这种问题并不常见，如果出现，那就是做表人的锅。

2.2 跳过不需要的行（删除最前面几行）

1. 直接使用PowerQuery提供的功能：删除最前面几行。
   

2. 删除结果：到这里可以将第一行提升为标题
   

3. 这里自动生成的函数是：Table.Skip，第1参数是表（上一个查询步骤返回的表），第2参数是4；即删除前4行。

2.3 多个工作表的数据都需要跳过前几行

具体解决步骤见下一节

3. 标题的上面有报表信息

  第一行到第N行是关于报表的属性信息，比如说制作时间、制作人等。这些信息是否要添加到汇总的数据表格中？

以下图为例：需要将四个字段添加数据字段

结果如下：

3.1 报表信息可忽略

可忽略的情况下，直接跳过前几行，即删除前几行

1. 在开始套Table.Skip函数之前，可以使用转换 - 大写功能来自动生成公式后，然后改写公式来解析工作薄数据。

  这里在改写Excel.Workbook时不必添加第二参数true。

3. 进行以上操作后，在Excel.Workbook外面再套上Table.Skip，到这里还不能开始合并表格数据，还需要将第一行提升为标题。
   

4. 操作的子表，要将标题处理好，然后再合并表格数据。

  在套函数的时候，不用担心自己记不住单词，PowerQuery有自动提示并补全，我们只需补全。

5. 在Table.Skip外面再套上Table.PromoteHeaders来将第一行提升为标题。
   

6. 以上步骤完成了跳过前4行，提升标题，全部在一个查询步骤里面完成。剩下的数据汇总需要新建查询步骤来完成，新的查询步骤取Content列，然后套上Table.Combine，即可完成多个工作表的汇总
   

3.2 报表信息需要添加新的数据字段

  不能忽略的情况下，将表格分为两部分进行处理，在改写时较为麻烦，尤其是多个工作表需要进行相同的操作时。
  掌握let..in的结构，将单个工作表处理的过程转换为自定义函数来解决这个问题。

3.2.1 单个工作表的处理方法

1. 照例，先使用Table.TransformColumns处理Content列来获取到表格内容，预览表格数据，并点击Table文字来获取单个表。
   

2. 单个工作表数据结构如下，两部分需要单独处理，然后合并在一起。
   

3. 双击查询步骤（红色框），修改一下查询步骤名称，方便我们接下来的查询步骤。查询步骤名称暂修改为：Start。

   双击无效的，可先单击，然后按下F2；或进入【高级编辑器】进行修改。

4. 先提取上半部数据，提取后进行转置、标题提升，查询步骤取名Cols。

   插入下一步的时候，记得要修改查询步骤的来源是：Start

   Table.FirstN(Start,4)，FirstN用来提取表格的前几行。使用功能：主页 - 保留行 - 保留最前面几行
   

5. 转置，转置后需要将第一行提升为标题并删除空行
   

6. 处理好的上半部数据如下，只有一行记录。将最后处理好的查询步骤改名为AddCols。
   

7. 再提取下半部数据，注意函数的第一参数是查询步骤Start，新查询步骤取名Data。

   Table.PromoteHeaders(Table.Skip(Start,4))
   

   

8. 两部分数据准备好之后，想办法把这两部分拼起来，有两种方法。

   这一步即是把AddCols表格中的唯一行，扩展至Data表格的每一行。

   1. 第一种：Table.AddColumn，添加自定义列。

   1. 以添加【制作人】为例：把AddCols当作字段内容添加进去，然后在M公式中对AddCols取行、列。
      

   2. 取行、取列，拿到制作人的值。其他的列信息，依次添加Table.AddColumn函数取值
      

   2. 第二种：Table.TranformRows，循环处理每一行，同时增加多个字段。

   上述自定义列添加的方法，过于麻烦，如果要添加的信息过多，需要写的函数也很多，并且没有扩展性不强

   1. 使用Table.TranformRows取每一行，M公式中的_代表记录行record。

      这个函数将表Data转换成了一个列表，列表中的每一个元素是表Data的每一行记录，那可以将AddCols中的唯一行与Data中的每一行进行拼接。
      

   2. 将记录行拼接：_&AddCols{0}，再次预览时Record时，新的字段已经被添加进去了
      

   3. 将Record的列表，恢复成表格：Table.FromRecords，新的表格已经有了新的字段，并且没有任何硬编码，理论上AddCols中有N个字段都能够拼接。不会有自定义列的限制。
      

3.2.2 将单个工作表的处理方法转换为自定义函数

  简单认识一下自定义函数的结构，其实PowerQuery的自定义函数结构随处可见，只不过因为形式多变，都不怎么注意的到它。

  更为详细的可阅读网络上其他材料：自定义函数 – Power Query爱好者

1. 最简单的结构：(x)=>x，通常被简写成each _。若某个函数只需要接收_一个参数的时候，each _可被省略。
   

2. 复杂一点的结构：(x) => let 源 = x in 源，let用来储存查询步骤，in用来输出最后的结果。查询步骤内也可以再套let .. in。
   

  从这里开始修改：

1. 右击查询名称，点击创建函数，这里会创建一个无参数的自定义函数，名称随意就好，这里取Fx。
   

2. 创建完成后，还需要进行修改；
   

3. 把查询步骤Start转换为输入参数，修改并不难，只需要两三步

   • ( )内填写Start
   • 第3行至第5行直接删除
   • 最外层的let..in想删就删，不会影响结果
     
     

3.2.3 应用自定义函数，处理多个工作表

1. 最后来检验一下自定义函数的效果
   

2. 复制一份新的查询，删除掉从Start开始的查询步骤。
   

3. 直接以Excel.Workbook(_){0}[Data]作为自定义函数Fx的参数。预览处理后的数据，需要转换的报表信息已经正常添加至数据表格。
   

4. 最后将数据表格汇总，依然是使用Table.Combine。