PowerQuery是在Excel中的一个数据清洗（ETL）工具，初次使用的时候发现非常简单且强大，但是后期因为这个步骤需要经常用到，还是把PowerQuery上的清洗思路移植到了Python上。
这个是在工作中碰到的问题，主要是想记录数据清洗的思路，所以就数据是虚拟的。
PowerQuery的入口
PowerQuery入口
问题：
用PowerQuery数据清洗
在下单数量这，数据类型是文本。每行数据给出了各个尺码的具体数量，但现在我们需要的是总的下单数。那么在PowerQuery中怎么做呢？
思路：
参考《Power Query：用Excel玩转商业智能数据处理》这本书，讲到了文本和数值的混合提取，当时特别开心，因为问题是一模一样的。
在PowerQuery中添加自定义列，下面的公式代码是
=List.Sum(List.Transform(Text.Split(Text.Remove([下单数量],{"A".."Z","a".."z","#(lf)",""""}),":"),Number.Form))
这里的思路是参考书上，从里到外，
1.先是去掉所有字母大小写、“ 和换行#(lf)，
2.然后用 ：切片，变成列表。
3.将列表内的内容变成数字
4.列表内的内容进行合计。
但是这里有个问题，当时以为所以的尺码就是s 、m、 l 和 xl 这些。后面导出的时候发现有很多错误，
原来尺码也有用数字的！！！
就是上面列出的第二行那样，当时因为数据量很大所以没有注意到，当加载出错才发现。
这样我们去除字母这个办法就行不通。
所以换了个思路
=List.Sum(List.Transform(List.Alternate(Text.Split(Text.Remove([下单数量],{"#(lf)",":"}),""""),1,1,1),Number.From))
1.将文本内的换行#(lf)和:去掉，
2.文本按"进行切片变成列表
3.列表从第一个数开始取，隔一个取数。
4.将列表变为数字
5.列表合计
不过这里有个缺点，因为对M语言不是很熟悉，分割后第一个数是为空的，第二个是尺码，第三个是尺码后的数字，第四个是尺码，以此类推，奇数除了第一个其余都是尺码后的数字。合计的话是可以合计，但是数据类型并不是数字，还需要将数据类型变为整数，
合计后数据类型非整数
后来是能凑合着用，不过再后来想，这个清洗步骤需要经常用。就把ETL过程转移到Python中，并用Python写入MySQL中。所以又改写了一次。
# 下单数量
order_number = [int(x) for x in row[8].value.replace('\n', ':').split(':')[1::2]]
order_number = sum(order_number)
这里用了openpyxl读取Excel内的数据，所以row[8].value就是读取到的下单数量(格式是文本)。
这里的思路是：
1.将换行\n替换为:
2.用:进行切分
3.从索引1开始，隔一个取值，
4.将取出的值变为int类型，
5.求和
其实PowerQuery也是可以用这样的思路的，只是当时没想到先用:替换回车再来做切分。
总结：
PowerQuery是很强大，配合M语言是能做很多复杂的数据清洗。但刚刚上手，开始的思路被限制了。
真心安利下PowerQuery！！！
我对PowerQuery的定义是——小型ETL工具。
数据清洗是绕不开的一个步骤，对于不会编程语言的数据分析师。自己动手做数据清洗的话，PowerQuery是个非常不错的工具。有可视化的工具界面，几乎囊括所有的清洗功能，实际中几乎不需要用M语言写代码。需要用到M语言的这个清洗步骤是少数，而且较难的。这时候直接用Python吧。
我平时的状态是，当其他部门需要数据的时候。先直接从数据库中调原始数据，用PowerQuery进行表的合并，分组，筛选等等常见的数据清洗步骤。极少会碰到PowerQuery做不到的奇怪的需求。碰到了就用Python写。
为什么不直接用SQL语句或Python写呢？因为这些需求只是“一次性”的。每次都用SQL语句和Python写起来加调整很费时间，而数据在PowerQuery进行清洗，点击需要的清洗功能，每个步骤完成后还有清洗结果可看，不对还能撤消。
心动了吗少年。
网友评论