Power Query.docx
《Power Query.docx》由会员分享,可在线阅读,更多相关《Power Query.docx(27页珍藏版)》请在冰豆网上搜索。
PowerQuery
PowerQuery为Excel的一个插件,本文档为基本操作介绍
一、创建
一、创建PowerQuery
1、从文件
数据选项卡—获取数据—自文件—从工作簿—选中目标文件导入—(选中多项:
同时加载多个表)(加载:
仅创建链接、加载到:
4种选项、转换数据:
进入工具)。
上载存在4种类型,表格、透视表、透视图、仅创建链接
2、从当前表格,数据选项卡—自表格/区域,原数据区域创建为表格;
3、从数据选项卡—启动PQ编辑器——主页——新建查询——新建源
二、后续进入
数据上载后,会进入Excel页面,默认情况下会打开查询&链接窗口,如未打开,请在数据选项卡打开查询&链接窗口:
1、双击查询,可打开PQ编辑器
2、右击—编辑,可打开PQ编辑器
3、查询&链接窗口中,右击“复制、删除、编辑、粘贴、重命名、引用、新建组、移动组”功能与PQ中查询窗口功能一致,且会影响PQ中数据;
4、查询&链接窗口中,右击“加载到”功能与PQ中“上载至”功能一致,且会弹出4种类型;
二、主页窗口
三、关闭并上载
1、“关闭并上载”,默认为创建表格,如已经选择过“关闭并上载至***”,则默认为已选择上载选项;
2、“关闭并上载至***”,一经选择,不可在PQ中更改,但可在Excel中的查询&链接窗口,右击上载至更改。
(此处可选择创建新表格)
四、查询
1、刷新预览,更改查询后,刷新查询数据;
2、属性,其实只有一个名称可编辑,右击查询数据选属性也可打开,查询设置窗口所有属性也可打开。
3、高级编辑器,应该为M语言编辑器,右击查询数据选高级编辑器也可打开;
4、管理(删除、复制、引用),右击查询数据也实现。
五、管理列
1、选择列
A、选择列,保留选中的列,删除其他的列。
B、转到列,将选中状态转到目标列。
2、删除列
A、删除列,删除当前选中的列。
B、删除其他列,保留选中的列(多选时按Ctrl),删除其他的列。
六、减少行
1、保留行(保留最前面几行、保留最后几行、保留行的范围、保留重复项、保留错误)
2、删除行(删除最前面几行、删除最后几行、删除间隔行【可以隔几行删除几行】、删除重复项、删除空行、删除错误)
七、排序
升序、降序,点击哪列后哪列排序,不影响其他顺序,类似于Excel自定义排序。
八、拆分列
1、按分隔符(按什么分割符号、分隔符取哪个位置、是拆分成行还是拆分成列、拆分成几行、是否有特殊字符,引号““””字符是否起作用)
2、按字符数(按几个字符拆分、是拆分成一列还是拆分成重复列、是拆分成行还是拆分成列、拆分成的列数)
3、按位置拆分(从哪个位置开始,提取几个字符,是拆分成行还是拆分成列)
4、按照从小写到大写的转换,小写到大写之间分拆列
5、按照从大写到小写的转换,大写到小写之间分拆列
6、按照数字到非数字的转换,数值到非数值之间分拆列
7、按照非数字到数字的转换,非数值到数值之间分拆列
九、分组依据
对数据进行按某些字段进行分组聚合(聚合是对数据统计方式的统称),聚合可以的求和、求平均值、最大值、最小值等。
类似excel中的数据透视表功能。
“基本”只能操作一个分组字段及分析数据字段;
“高级”并不是增加新功能,只是可以执行多个分组字段及多个数据分析字段;
1、数据分析功能包括:
求和、求平均值、最大值、最小值;
2、计数功能包括:
对行进行计数(对所有行进行计数)、非重复行计数(仅统计不重复的行数);
3、所有行功能:
按分组,做出一列表;
十、数据类型
1、数字:
小数、货币、整数、百分比。
注意:
小数转换整数时进行四舍五入。
2、日期时间:
日期/时间、日期、时间、日期/时间/时区、持续时间
3、文本:
文本格式
4、逻辑值:
True/False,0转换成False,其他数字转换True
5、二进制:
转换成二进制
6、List:
列表类型,数据存放于{}中,可以通过输入“{1..5}”输入一列数据12345
7、Record:
行记录类型,数据存放[]中,可以通过输入输入“_”将整行数据输入
8、table(表):
表类型,由行(Record)和列(list)组成
9、null:
空值,注意:
null值计算错误,先替换掉(空值四则运算为空值)
小数标记为1.2
货币标记为$
整数标记为123
百分比标记为%
文本标记为ABC
日期标记为
时间标记为
日期时间标记为
日期时间时区标记为
逻辑值标记为
二进制标记为
持续时间标记为
List、Record、Table标记均为ABC123,
List有两种操作,
(1)提取到新行,结果如下表,将原有的一行变成了多行:
List有两种操作,
(2)提取值,结果如下表,提取后原有的一列数据会用一个符号链接起来,形成一个新的数据:
Record有一种操作,扩展到新行及提取致均为在本行添加几列;
Table类似于List,有两种操作,
(1)展开:
扩展到新行、提取值均为为所有的结果均扩展开,如下
(2)聚合:
可以将列实现求和、平均值、中值、最小值、最大值功能
修改数据类型的方式:
选中列,点击数据类型中相应的数据类型;
点击列标题中的数据类型标记
十一、将第一行用做标题、将标题作为第一行
十二、替换值
开始选项卡下替换值与转换选项卡下替换值,转换选项卡下包含替换错误;
选中单元格匹配时,必须整个单位格为需要替换的值时才可以进行替换;
特殊字符如下:
十三、合并查询(超级VLOOKUP)
1、合并查询不创建新的查询;
2、将查询合并为新查询,创建新的查询;
3、创建时需要选择两个查询,并且标记出关联字段,(可按Ctrl同时选中两个字段,实现VLOOKUP不能实现多条件匹配的功能,同时可以匹配到多列),链接种类包括6种。
匹配时注意唯一性,否则会出现多对多情况。
合并后的结果是一个表,可以展开及聚合等。
十四、追加查询
追加查询:
(1)在原表追加查询,
(2)创建新查询
将多个结构相同的工作表进行合并,以便进行后期数据整理和分析。
要求合并的工作表名称保持一致。
如果要合并的数据列名不同,合并时名称不同会合并为空,则统计容易出错,实际每个列名会单独显示一列。
十五、管理参数
十六、数据源设置
十七、新建查询
与Excel选项卡数据中类似;
三、转换窗口
十八、转置
行列变换,再转一次又重新进行还原,转置时需要将标题行移到首行;
十九、反转行
行的顺序颠倒过来;
二十、对行进行计数
算出表中共有多少行,不包含标题;
二十一、检测数据类型(未发现作用)
二十二、重命名
修改列名称;
二十三、替换值
与主页一致,但多了一个替换错误值;
二十四、填充
向下填充,向上填充至空白单元格
二十五、透视列
类似于数据透视表,但功能不如数据透视表强大;
想用哪列当行,操作时选哪列
聚合值函数包括:
求和、计数、最小值、最大值、中值、平均值、求和、不要聚合,其中不要聚合(如剩余信息具有一一对应,如不一一对应可能出现错误)会将所有信息以数据透视的形式显示出来;
二十六、逆透视列
与透视列相反,将列的数据转换成以行显示,可以解决EXCEl不能还原数据透视表的功能;
逆透视列走势图如下,如果为一行一列数据透视表,逆透视列后,会形成行乘以列数量的行;
二十七、转换为列表
尚未学习具体用途;
二十八、调整格式
小写:
所有字母均转换为小写字母;
大写:
所有字母均转换为大写字母;
每个字词首字母大写:
每个单词首字母大写;
修整:
删除单元格中内容开头及结尾的空格;
清除:
删除所选列中的非打印字符,非打印字符包括换行符号、回车符等;
添加前缀、添加后缀:
添加前后缀。
二十八、合并列
按Ctrl选择需要合并的列,选择需要的分隔符;
二十九、统计信息
只能选择一列,返回当前选中列的和、最小值、最大值、中值、平均值、标准偏差、值计数(个数)、非重复值个数;
三十、标准
三十一、标准
选中列的科学计数
三十二、选中列的三角函数
三十三、返回指示列的奇偶性、符号的正负数
三十四、舍入(取整、四舍五入)
三十五、日期
分析功能可以将不规则的日期转换为规则的日期;
三十六、时间
与日期类似
三十七、持续时间
持续时间是一种单独的数据格式,通常用两个日期时间格式做差取得
四、添加列窗口
三十八、示例中的列
以选中列为基准,单击新增出来的列中的第一格之后,会出现各种案例,选中其中一个后,会以选中的列中的内容创建
三十九、示例中的列
以选中列为基准,单击新增出来的列中的第一格之后,会出现各种案例,选中其中一个后,会以选中的列中的内容创建
四十、添加列中的各种列
条件列,根据设定的条件,添加一列
索引列:
添加序号列,可以从0开始,也可以从1开始,或者自定义开始及间隔列
重复列:
复制选中的列
五、视图
十八、布局
1、查询设置
(1)属性窗口,编辑名称使用。
(2)查询设置,记录每一步操作
每一步操作都有一个M语言,每一步操作都有一个名称
A、编辑设置,对编辑重新设置;
B、重命名,对操作重新命名;
C、删除,删除本操作;
D、删除至末尾,删除本操作及之后所有操作;
E、插入步骤后,本操作之后插入新操作,一般用来写M语言;
F、前移,本操作前移;
G、后移,本操作后移;
H、提取之前的步骤,将之前的操作重新归集成一个新的数据源,需要重命名新的数据源名称;
I、查看本机查询
J、属性,主要用来查看及修改操作名称;
2、编辑栏
编写操作的M语言;