Excel 数据清洗,使用 Power Query 原来这么地简单

今天通过一个数据清洗的例子继续讲解一下Excel Power Query中关于数据清洗的例子。
file
如下所示,将下面的左侧的数据清洗成右侧的数据。
file

这个例子,科目这一栏中,语文,数学,英语的顺序每个人是一样的,所以就好处理多了。先将数据加载至Power Qeury中。如下图所示:
file
接下来先筛选掉第2列中的null值。

= Table.SelectRows(源, each [#"语文|数学|英语"] <> null)

file
然后再对第1列进行填充。

= Table.FillDown(筛选,{"姓名"})

file
然后再对第1列进行填充。

= Table.FillDown(筛选,{"姓名"})


接下来是处理的重要的环节,按姓名进行分组,分组后取第2列,利用List.Zip函数(也叫拉链函数)将自定的{"语文","数学","英语"}连接后,最后再用Table.FromList函数进行转换。


= Table.Group(填充,
                      "姓名",
                      {
                          "n",
                          each 
                              Table.FromRows(
                                  List.Zip(
                                      {
                                          {"语文","数学","英语"}
                                          ,_[#"语文|数学|英语"]
                                      }  
                                      ) 
                                      ,{"科目","成绩"} 
                                      )              
                       }
                       )

file
至此已经就完成80%的转换的工作了,接下来只需要将n展开,然后再进行透视一下就OK了。
file

整体的代码如下图所示:

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    筛选 = Table.SelectRows(源, each [#"语文|数学|英语"] <> null),
    填充 = Table.FillDown(筛选,{"姓名"}),
    分组 = Table.Group(填充,
                      "姓名",
                      {
                          "n",
                          each 
                              Table.FromRows(
                                  List.Zip(
                                      {
                                          {"语文","数学","英语"}
                                          ,_[#"语文|数学|英语"]
                                      }  
                                      ) 
                                      ,{"科目","成绩"} 
                                      )              
                       }
                       ),
    展开 = Table.ExpandTableColumn(分组, "n", {"科目", "成绩"}),
    透视 = Table.Pivot(展开, List.Distinct(展开[科目]), "科目", "成绩")
in
    透视

file
本节例子属于一般性的常规的解法,还有很多的解法,小伙伴们可以在下面的留言区写出你对于本题中的一些问题的看法。

Excel和PowerBI聚焦:小必