一、概念介绍:
1.Power Query(查询——数据处理):
一个插件,相当于Excel中的数据选项卡下的数据查询,可以弥补 Excel 的不足,处理数据的能力边界大大提升。PQ用到的语言是M语言,学习 PP 就是学习 Power BI 中的数据处理模块
2.Power Pivot(透视——数据建模):
相当于Excel中的数据透视表,但是PP功能更强大。PP 用到的语言是 DAX,学习 PP 就是学习 Power BI 的数据建模
*注意:Power View 和 Power Map:PV ——数据可视化;PM ——数据地图(内嵌到PowerBI 且功能更加强大)
二、Power Query详细介绍:
1.用途:将多个工作簿的数据合并到一个表上。
2.优势:数据量无限制、自动化,处理过程全记录,每次数据源更新后刷新即可,无需重复劳动
3.过程:
1)数据获取:
数据的获取上不仅支持微软自己的数据格式,比如 Excel、SQL Server、Access 等;还支持
SAP、Oracle、MySQL、DB2 等几乎能见到的所有类型的数据格式。不仅能从本地获取数据,还能从网页抓取数据。
2)数据清洗:
步骤1:提升标题
方法1):点击「转换」的将第一行作为标题,即可完成
方法2):点击将第一行作为标题旁边的下拉按钮,还有个将标题作为第一行,实际上就是拉低标题
步骤2:更改数据类型
方法1):在 转换-数据类型 里设置
方法2):在标题栏里右键设置
步骤3:删除错误/空值(数据导入后,有可能出现错误(Error)或者空值(null))
在标题栏里右键-删除/替换错误
在标题栏右下角箭头点击-去掉勾选null
步骤4:删除重复项
选中需要删除的列,右键选择「删除重复项」即可
步骤5:填充
转换-填充-向下填充
步骤6:合并列
转换-合并列
步骤7:拆分
可以选着按字符数,也可以选择按分隔符,如果列中包含多个分隔符,还可以选择按哪个位置的分隔符来拆分
步骤8:分组
转换-分组依据-填上相应参数
步骤9:提取
可以按照长度、首字符、尾字符、范围等来提取
步骤10:行列转置
先将标题最为第一行-转置-再将第一行升为标题
步骤11:行列操作
步骤12:逆透视列
逆透视列-逆透视其他列
步骤13:透视列
一维表变成二维表,聚合方式选择“不要聚合”
- 数据丰富:
在原有数据的基础上增加一些辅助数据,比如加入新列、新行,或者从其他表中添加进来更多维度的数据
步骤1:添加列
重复列:把选中的列复制一列,以便对该列的数据进行处理而不损坏原有列的数据
索引列:为每行增加个序号,记录每一行所在的位置,可以从 0 或者 1 开始
条件列:添加一列根据指定条件从其他列计算的数据,打开窗口看看,其实就是 if 函数
得出一个新列,包含一线、二线等
自定义列:用 M 函数生成新的一列
步骤2:追加查询(纵向合并)
在现有记录的基础上,在下边添加新的行数据,是一种纵向合并,比如有两个表格相同,需要合并为一个表,点击“追加查询”即可
步骤3:合并查询(横向合并)
相当于VLOOKUP 功能,就是匹配其他表格中的数据
例如:从基础信息表中找到每个省市对应的省市和电话区号,点击“合并查询”,选择两个表需要匹配的字段,这两个表都是[城市]列,下方联结种类选择左外部:第一个表的所有行,第二个表的匹配行,就得到了下面这张表:
然后点击展开,勾选我们需要的字段,合并查询就完成了,增加了每个城市对应的省份和区号