文章目录
文章难免有疏漏,若发现某处编辑有误,可评论留言。
相关问题也可找作者进行咨询,DIY定制皆可。
-
Authors
- @ 樊笼星海
- @ w180361
- @ Email:ou251@outlook.com
在汇总数据的时候,发现我们需要的数据并不是从第一行开始,假如数据字段标题是从第5行开始。
那么这种情况下,Excel.Workbook
在修改第二参数后,还能正确的识别到标题吗?
1. 此节将涉及到的内容
- 转置,表格的行、列取值
- 将单个复杂的操作逻辑应用到多个需要重复的操作逻辑上
- 将自动生成的公式转换为自定义函数
- 应用自定义函数
2. 标题不在第一行
2.1 是否能够正确识别?
源数据:数据字段标题从第5行开始
-
使用PowerQuery获取此工作表数据,在不修改任何M公式的情况下,能够识别到标题。
-
修改
Excel.Workbook
的第二参数,能够识别到标题
-
可以看到的是,即使数据字段标题从第5行开始,PowerQuery依然能够识别到数据是从哪一行开始的,这是因为工作表中的有效数据是从第5行开始。
但如果工作表中第1行至4行中任意一个单元格有空格,那就会出现问题。好在这种问题并不常见,如果出现,那就是做表人的锅。
2.2 跳过不需要的行(删除最前面几行)
-
直接使用PowerQuery提供的功能:删除最前面几行。
-
删除结果:到这里可以将第一行提升为标题
-
这里自动生成的函数是:Table.Skip,第1参数是表(上一个查询步骤返回的表),第2参数是4;即删除前4行。
2.3 多个工作表的数据都需要跳过前几行
具体解决步骤见下一节
3. 标题的上面有报表信息
第一行到第N行是关于报表的属性信息,比如说制作时间、制作人等。这些信息是否要添加到汇总的数据表格中?
以下图为例:需要将四个字段添加数据字段
结果如下:
3.1 报表信息可忽略
可忽略的情况下,直接跳过前几行,即删除前几行
- 在开始套
Table.Skip
函数之前,可以使用转换 - 大写
功能来自动生成公式后,然后改写公式来解析工作薄数据。
这里在改写Excel.Workbook
时不必添加第二参数true
。
-
进行以上操作后,在
Excel.Workbook
外面再套上Table.Skip
,到这里还不能开始合并表格数据,还需要将第一行提升为标题。
-
操作的子表,要将标题处理好,然后再合并表格数据。
在套函数的时候,不用担心自己记不住单词,PowerQuery有自动提示并补全,我们只需补全。
-
在
Table.Skip
外面再套上Table.PromoteHeaders
来将第一行提升为标题。
-
以上步骤完成了跳过前4行,提升标题,全部在一个查询步骤里面完成。剩下的数据汇总需要新建查询步骤来完成,新的查询步骤取
Content
列,然后套上Table.Combine
,即可完成多个工作表的汇总
3.2 报表信息需要添加新的数据字段
不能忽略的情况下,将表格分为两部分进行处理,在改写时较为麻烦,尤其是多个工作表需要进行相同的操作时。
掌握let..in
的结构,将单个工作表处理的过程转换为自定义函数来解决这个问题。
3.2.1 单个工作表的处理方法
-
照例,先使用
Table.TransformColumns
处理Content列来获取到表格内容,预览表格数据,并点击Table
文字来获取单个表。
-
单个工作表数据结构如下,两部分需要单独处理,然后合并在一起。
-
双击查询步骤(红色框),修改一下查询步骤名称,方便我们接下来的查询步骤。查询步骤名称暂修改为:
Start
。双击无效的,可先单击,然后按下F2;或进入【高级编辑器】进行修改。
-
先提取上半部数据,提取后进行转置、标题提升,查询步骤取名
Cols
。插入下一步的时候,记得要修改查询步骤的来源是:
Start
Table.FirstN(Start,4)
,FirstN
用来提取表格的前几行。使用功能:主页 - 保留行 - 保留最前面几行
-
转置,转置后需要将第一行提升为标题并删除空行
-
处理好的上半部数据如下,只有一行记录。将最后处理好的查询步骤改名为
AddCols
。
-
再提取下半部数据,注意函数的第一参数是查询步骤
Start
,新查询步骤取名Data
。Table.PromoteHeaders(Table.Skip(Start,4))
-
两部分数据准备好之后,想办法把这两部分拼起来,有两种方法。
这一步即是把
AddCols
表格中的唯一行,扩展至Data
表格的每一行。1. 第一种:
Table.AddColumn
,添加自定义列。-
以添加【制作人】为例:把
AddCols
当作字段内容添加进去,然后在M公式中对AddCols
取行、列。
-
取行、取列,拿到制作人的值。其他的列信息,依次添加
Table.AddColumn
函数取值
2. 第二种:Table.TranformRows,循环处理每一行,同时增加多个字段。
上述自定义列添加的方法,过于麻烦,如果要添加的信息过多,需要写的函数也很多,并且没有扩展性不强
-
使用
Table.TranformRows
取每一行,M公式中的_
代表记录行record
。这个函数将表
Data
转换成了一个列表,列表中的每一个元素是表Data
的每一行记录,那可以将AddCols
中的唯一行与Data
中的每一行进行拼接。
-
将记录行拼接:
_&AddCols{0}
,再次预览时Record
时,新的字段已经被添加进去了
-
将
Record
的列表,恢复成表格:Table.FromRecords
,新的表格已经有了新的字段,并且没有任何硬编码,理论上AddCols
中有N个字段都能够拼接。不会有自定义列的限制。
-
3.2.2 将单个工作表的处理方法转换为自定义函数
简单认识一下自定义函数的结构,其实PowerQuery的自定义函数结构随处可见,只不过因为形式多变,都不怎么注意的到它。
更为详细的可阅读网络上其他材料:自定义函数 – Power Query爱好者
-
最简单的结构:
(x)=>x
,通常被简写成each _
。若某个函数只需要接收_
一个参数的时候,each _
可被省略。
-
复杂一点的结构:
(x) => let 源 = x in 源
,let
用来储存查询步骤,in
用来输出最后的结果。查询步骤内也可以再套let .. in
。
从这里开始修改:
-
右击查询名称,点击创建函数,这里会创建一个无参数的自定义函数,名称随意就好,这里取
Fx
。
-
创建完成后,还需要进行修改;
-
把查询步骤
Start
转换为输入参数,修改并不难,只需要两三步( )
内填写Start
- 第3行至第5行直接删除
- 最外层的
let..in
想删就删,不会影响结果
3.2.3 应用自定义函数,处理多个工作表
-
最后来检验一下自定义函数的效果
-
复制一份新的查询,删除掉从
Start
开始的查询步骤。
-
直接以
Excel.Workbook(_){0}[Data]
作为自定义函数Fx
的参数。预览处理后的数据,需要转换的报表信息已经正常添加至数据表格。
-
最后将数据表格汇总,依然是使用
Table.Combine
。