数据筛选---保留---删除---去重---缺失值/错误值处理---文本处理---时间日期处理
数据整合---追加查询(上下合并)(左右合并)
统计---分组汇总---透视列、逆透视---查看数据统计信息和数据异常
列分发、列质量---添加列--常规---自定义列
M函数:是微软为Power Query设计的公式语言,简称M函数。
M函数相比于Excel函数,数据处理能力更强、数据计算方式更丰富。
M函数常用函数:
迭代函数:是在统计函数后加一个X,Sumx(表,表达式),迭代是表的逐行扫描(迭代),其内容是计算表达式的值,扫描完成后再统计
List.Accumlate (指定的list列表as list,初始值as any,迭代器 as function) as any
List.Select(list as list,selection as function) as list
Text.PositionOFAny(text as text,characters as list,optional occurrence as nullable number) as any
值函数:
Related函数:多端找一端
Relatedtable函数: 一端找多端
ALL函数:清楚筛选
Allexpect函数:除...之外
ALLSELECTED(表名[列名])
总数= SUM('表'[字段名])
平均数= AVERAGE('表'[字段名])
最大值= Max(‘表’[字段名])最小值= Min('表'[字段名])记录数= Countrows('表’)
人数= DISTINCTCOUNT('表'[姓名])
CountA函数∶计算列中单元格不为空的数目
Countblank函数:计算列中单元格为空白数量
Product函数:计算列中单元格乘积
Format(数值或日期,格式):将值转换为指定数字格式文本,通常与DateTime格式一起使用
TREATAS(table_expression,LL L.…]]}):把什么当作什么,就是把一参当作二参的筛选器,通过一参筛选二参。
SELECTEDVALUE (<列名>,[<备用结果>]):如果参数列在当前筛选上下文中只有一个非重复值,则返回该值。否则,返回备用结果。
表函数:
Values(表[列])
表1 = VALUES(“表"[姓名])# 把姓名不重复的列提取出来,形成一个单列的表
表2 = VALUES(“表”)# 返回表的所有可见行,就是复制一张表
DISTINCT(表名[字段名])返回:去重后,唯一值的列
DISTINCT(表名)返回:只包含非重复行的表
DISTINCT(返回表的表达式 如过滤后的表)
FILTER(表,FILTEREXPRESSION)返回的是一张表
参数1-表:要筛选的表,还可以是生成表的表达式
参数2-FILTEREXPRESSION指筛选条件
CALCULATETABLE('表’,筛选条件)返回的是一张表
CALENDAR(开始日期,结束日期)返回的是一张只有一个date列的表
ROW(〈名称〉,〈表达式〉,[<名称〉,〈表达式>],…)返回一个单行表,其中包含 DAX表达式定义的新列
UNION(〈表〉,〈表〉,[表〉,… ]) 将多个表追加到一起,列按照在各自表中的顺序进行组合(不按照列名)
SUMMARIZE():函数可以实现对既定表格的分组,生成分组信息表(不重复多层分组信息)同时还可以基于分组信息进行汇总计算,它返回的是一张表;
语法:SUMMARIZE(表格,[分组列1],[分组列2],…,“新列名”,[列表达式])
Power Query:常用数据清洗与转换
在Excel里面选择要操作的数据,右键选择【从表格/区域获取数据】
进入 PowerQuery界面:
复制列:
成绩表判断“数学”成绩<60 为”不及格”,[60,90)为及格>=90为优秀,新列名为数学等级:
成绩表为学生编号(索引列):
对产品表的两个编号列实现拆分:
就会在Excel里面显示出:
对产品表的两个编号列实现拆分:
用函数提取品牌、型号:
就会拆分成:
对产品表从产品编号_1拆除的品牌的型号进行合并:
缺失值填充—替换:
将m替换为男,f替换为女:
向上填充:
文本提取: