Power Query
作用:数据处理
对象:表结构数据
特点:
- 提取、整合多数据源数据
- 数据量限制(无行数限制,单机参考量:2G)
- 数据处理分析功能,操作方便
- M函数自定义创建数据处理和计算规则
- 流程可重复利用,提高效率
工具:excel 2013版需另外加载,excel 2016以上已加入--获取和转换。
操作界面--查询编辑器:
功能组
编辑栏
查询列表(近似于表)
数据预览区
查询设置区
导入数据方式:
- 仅创建连接:只导入query,不用excel处理数据时用,文件内存小。
- 创建连接和表:导入query同时创建excel表格。
文本文件:TXT--分隔符为tab键,即制表符;CSV--分隔符为逗号。
加载:默认加载到表。
加载到:表或仅创建连接,使用到数据模型。
查看仅创建连接导入的数据:双击工作簿查询的表名,进入查询编辑器的界面。
修改加载方式:右击工作簿查询中的表名,右键-加载到。
关闭并上载/关闭并上载至:与加载一致。
合并数据
表结构数据的关系:字段-数据表-数据库
标题行的值:字段名
记录:字段的值
横向合并:关键字段(公共字段)--无重复且一一对应最好,合并操作--合并查询,选择展示字段。
主表:
附表:为主表提供信息。
连接种类
- 左外部:保留左表的所有行
- 右外部:保留右表的所有行
- 内部:保留左右表匹配的行
- 全外连接:保留左右表所有行
- 左反:只保留左表中能与右表匹配的行
- 右反:只保留右表中能与左表匹配的行
纵向合并:合并行
- 有相同字段的行追加到下面
- 有不同的字段追横向加到最后
- 不匹配的字段标记空值
excel加载表结构数据:如果第一行各字段的数据类型与下面全部一致,自动添加一个标题行。
Power Query 基本功能
感叹号错误信息--找不到文件:文件路径错误--应用的步骤,源,修改编辑栏上的M函数公式中的文件路径。
开始选项卡
转换-数据类型
转换选项卡
填充功能:
- 向下填充:空值邻近的值向下填充空值位置
- 向上填充:空值邻近的值向上填充空值位置
编辑列-信息:判断奇偶
添加列选项卡
透视列:选择值列,高级选项选汇总规则。
逆透视列:把透视表展开
添加自定义列:
索引列:
转换与添加列重复的功能:转换选项卡下的功能在当前列操作,添加列选项卡的功能新建列后操作。
视图选项卡
M函数
用于自定义处理数据的方法,编程语言。
1、修改M函数:看懂M函数并且能重组使用。
2、写M函数:自己写需要的M函数来实现数据处理和计算功能。
编辑位置:编辑栏、添加自定义列、高级编辑器。
查询M函数:新建空查询,编辑栏输入 =#shared 后回车,到表中后可筛选。
M函数名称规律:数据类型.方法
M函数的特点:
- 参数以逗号分隔
- 第一个参数基本都是上一步操作的步骤名称
- 文本要加引号,数值不用引号
- 索引以0开始
- 步骤名称不能重复
高级编辑器
let in 结构
过程写在 let :步骤名称=函数名称(上一步名称,参数……),……
结果写在 in :步骤名称