杰人编程网

PowerQuery是Excel中的新功能，其强大的功能主要依托于强大且丰富的M函数来完成。对于日常中的一些不规范的数据，使用该功能处理可以快速方便地完成。

今天小必老师给大家讲一下关于如何使用EXcel中的Power Query功能完成不规范的数据的清理。

如下图所示，是从教育部高等教育司的网站上下载下来的一份2019年6月的全国成人高等院校名单。具体的素材下载的地址为：

http://www.moe.gov.cn/srcsite/A03/moe_634/201706/t20170614_306900.html

将上面的数据处理成下面的规范的易于分析的数据。

具体的操作思路为：主要对数据进行局部条件分组。是解决问题的最核心的问题。

对于上面的问题，主要的步骤如下。

Step-01：将源数据加载至Power Query中。依次单击【数据】-【获取数据】-【来自文件】-【从工作簿】选项，在弹出的对话框中打开要处理的工作簿。如图所示。

加载至Power Query中结果如下图所示，然后单击【转换数据】。

Step-02：在Power Query中将多余的步骤删除掉，只剩余一下“源”.如图所示。

在原代码的基础上进行深化数据。具体的代码如下：

= Excel.Workbook(File.Contents("C:\Users\81532\Desktop\数据.xlsx"), null, true){[Name="sheet1"]}[Data]

Step-03：删除前面的两行没有用的数据，然后再次提升标题。

= Table.Skip(源,2)
= Table.PromoteHeaders(删除行)

Step-04：将“”序号列的数据类型修改为“文本”。如下图所示。

= Table.TransformColumnTypes(提升标题,{"序号", type text})

Step-05：对数据进行局部分组。如下图所示。

局部分组 = Table.Group(更改类型,
                         "序号",
                         {
                             "m",
                             each 
                             Table.RemoveColumns(
                                                  Table.Skip(_,1),
                                                  {"序号","Column6"}
                                                  )
                         },
                         0,
                         (x,y)=> if Text.Length(y)>=5 then 1 else 0
                         )

Step-06：展开数据，如下图所示。

Step-07：处理首列，提升左括号前面的数据。如下图所示。

= Table.TransformColumns(展开,{"序号",each Text.BeforeDelimiter(_,"（")})

Step-08：最后加载数据至工作表中即可。

完整的代码如下图所示。

let
    源 = Excel.Workbook(File.Contents("C:\Users\81532\Desktop\数据.xlsx"), null, true){[Name="sheet1"]}[Data],
    删除行 = Table.Skip(源,2),
    提升标题 = Table.PromoteHeaders(删除行),
    更改类型 = Table.TransformColumnTypes(提升标题,{"序号", type text}),
    局部分组 = Table.Group(更改类型,
                         "序号",
                         {
                             "m",
                             each 
                             Table.RemoveColumns(
                                                  Table.Skip(_,1),
                                                  {"序号","Column6"}
                                                  )
                         },
                         0,
                         (x,y)=> if Text.Length(y)>=5 then 1 else 0
                         ),
    展开 = Table.ExpandTableColumn(局部分组, "m", {"学校名称", "学校标识码", "主管部门", "备注"}),
    提取 = Table.TransformColumns(展开,{"序号",each Text.BeforeDelimiter(_,"（")})
in
    提取