Power Query 从实例应用到精通
由于各个公司处理大量数据需要Office被广泛应用,但如何高效便捷的处理庞大的数据集,此时我们引用PQ的概念,课件实例请按各自需求模拟,没有标准的概念(需安装Office2010以上版本)。
SAP剑客
一日不读书,胸臆无佳想。一月不读书,耳目失精爽。
展开
-
50、Power Query-Text.Contains的学习
比如现在有这样一个需求,需要筛选某列规格里面的一些内容。比如我们要筛选出包含“110”以及“123”以及“CF”的规格,当然用条件格式也是可以的。若是两个包含,我们可以直接筛选得到,如下图操作。没错,就是文本筛选器里面的“包含”。那么最后一个条件该如何引入呢?我们不妨直接看它生成的公式,是不是豁然开朗?= Table.SelectRo原创 2017-02-28 16:51:40 · 14241 阅读 · 2 评论 -
49、Power Query-合并查询温故知新
这一节我们来重温一下合并查询,介绍一个新的引入方式,废话不多说,直接看需求。看上表,现需要统计各个等级的学生数量,我们利用Power Query这种方式的优势在于新增修改学生成绩条目以及等级情况条目的时候可以动态刷新。执行效果如下:下面我们介绍一下如何动态的获取这两张表(用选择——“从表”的方式也是可以的):1、学生成绩表2、等级对应表我们不妨使用函数动态的原创 2017-02-15 10:06:19 · 8491 阅读 · 7 评论 -
48、Power Query-大量复杂数据的整理汇总
工作中,生活中不免会遇到需要整理大量复杂数据的情况,单纯的靠手动去搜索统计固然可以,但是效率极其低下。如下图,列举了1990年~2015年NBA美职篮全明星的参赛名单,现在需要统计出每个人的参赛次数。实现效果如下:下面直接来简单谈一下它的实现思路,首先我们整理掉一些不需要的数据,例如“NBA2008年全明星阵容”相关的字眼。我们利用第一列的“文本筛选器”剔除掉开头不原创 2017-02-13 16:53:00 · 32619 阅读 · 4 评论 -
47、Power Query-处理典型的中国式二维表格转一维
本节要点:典型的中国式二维表格转一维的处理。例子:看如下图的数据源表为典型的二维表,需要将其转置成为一维表显示。直接加载到Power Query中进行处理。合并列1和列2。接着进行“转置”操作,姓名和部门跑到列的方向,然后将第一列向下进行填充。将我们合并的首行提升为标题行。选中前两列,逆透视其他列。再将第二列选中进原创 2017-01-21 11:21:00 · 8970 阅读 · 1 评论 -
46、Power Query-Table.FillDown函数简介
本节跟大家分享一个经验,函数Table.FillDown的使用。格式:Table.FillDown(table as table, columns as list) as tableReturns a table from the table specified where the value of a previous cell is propagated to原创 2017-01-20 10:12:15 · 4701 阅读 · 0 评论 -
45、Power Query-缺少功能区选项卡
有时可能需要安装 Power Query 时不会显示POWER QUERY选项卡后。这是因为 Microsoft Power Query for Excel COM 加载项加载失败。大多数情况下的解决方案是非常简单︰ 只需重新加载 Microsoft Power Query for Excel COM 加载项。《重新加载 Microsoft Power Query for Excel原创 2017-01-19 13:31:38 · 11502 阅读 · 3 评论 -
44、Power Query-具体查看函数的方法
方法一、根据帮助来查询。1、在查询页面点击右上角的帮助按钮;2.在弹出的网页中,找到“PowerQuery公式类别”,然后再在弹出的网页中查找自己需要的函数;不过最近Microsoft好像大姨妈,一点就报错:然后只能返回,在界面中点击“请在 此处 中查找本文的英文版本以便参考。”进入英文版本查询。这个方法就是我们之前课程中介绍的方法:https://ms原创 2017-01-18 13:12:35 · 14744 阅读 · 0 评论 -
43、Power Query-函数综合应用
本节重点:结合之前所学的两个函数Text.Remove( )和Text.Combine( )完成以下功能。上图数据源是某公司某月各部门的人员工资,呈现在一个单元格内,比较凌乱。需求是:根据部门汇总工资,并显示人员在一个单元格内,以“/”分隔。具体实现如下:首先根据分隔符“-”进行拆分列。然后根据拆分出来的第二列进行“姓名”以及“工资”的分离。这里利用原创 2017-01-18 10:15:15 · 7836 阅读 · 4 评论 -
42、Power Query-Text.Remove函数应用
本节我们学习函数:Text.RemoveRemoves all occurrences of a character or list of characters from a text value. The removeChars parameter can be a character value or a list of character values.原创 2017-01-17 15:28:03 · 8065 阅读 · 1 评论 -
41、Power Query-Text.Combine函数进阶2
本节继续讲解Power Query-Text.Combine函数进阶。下面看一个更加有趣的例子。比如上图,有多列,我们需要求出唯一值,标准有两个,分别是以左边为基准和以右边为基准。比如以左边为基准,这个“袋子”,在列“核心词”里面已经存在,则在列“热搜分词”里面则应该剔除。貌似一列去重比较简单(“删除重复项”即可),根据这么多列求唯一的话有难度!我们不妨先进到Power原创 2017-01-17 13:27:08 · 6107 阅读 · 2 评论 -
40、Power Query-Text.Combine函数进阶
本节主题:通过上一节的例子进阶处理,加深Text.Combine的印象。回顾一下上一节的数据源。这次我们需要的结果没有上一节那么简单了,要根据月份业绩列分别列示在一行,目标效果如下:首先我们还是根据上一节的方法先根据“姓名”分组一下“月份”以及“业绩”。惨了,结果显示的是两列Error!Take it easy,这不是我们所关注的,我们需要更改它原创 2017-01-16 15:39:09 · 8335 阅读 · 3 评论 -
39、Power Query-Text.Combine函数应用
本节开始我们学习函数的应用:Text.Combine函数应用Returns a text value that is the result of joining all text values with each value separated by a separator.用法: Text.Combine(text as list,原创 2017-01-16 15:00:35 · 13716 阅读 · 3 评论 -
38、Power Query-背后的贤内助"M"语言
Power Query的本质——M语言。Power Query 在Excel2010和2013中是以插件形式存在的,Excel2016中已经内置这一功能了,其可见微软对其的重视。值得欣慰的是,PQ不仅为我们快速导入不同数据源数据提供便利,也为我们提供了一系列新的函数,PQ中的公式简称为M公式,与PowerPivot中的DAX表达式是两兄弟。什么是M语言:用一种既定的函数公式来记录在原创 2017-01-16 10:56:59 · 14156 阅读 · 1 评论 -
37、Power Query-不使用IF嵌套进行匹配
本节要点:Power Query-不使用IF嵌套进行匹配(突破IF语句嵌套写法)标题可能很笼统,话不多说,直接看案例。左表是学生成绩表,右边是成绩范围对应的等级,目的是匹配到学生成绩的等级。这次我们不使用IF语句进行判断,结合之前所学,讲解一个新的方法。首先依旧是将左右两个表添加进PQ界面。首先处理“范围表”。将“范围”一列按照“-”进行拆分。结原创 2017-01-16 10:31:35 · 20035 阅读 · 3 评论 -
36、Power Query-多条件合并查询
本节要点:Power Query-多条件合并查询之前我们学过两表合并,多表追加,但要同时满足多个条件的表表合并,我们如何实现呢?我们先看下面一个例子:左表是产品对应等级的销售数量,而右表示产品对应等级的单价,现在我们需要进行合并汇总出如下结果:汇总每个产品每个等级的平均价,销售总量,以及销售总额。思路:两表合并,利用透视表完成汇总。首先创建左右两表的连接。原创 2017-01-16 09:36:58 · 20183 阅读 · 2 评论 -
35、Power Query-分组中国式排名
本节要点:根据上一节的“中国式排名”加大难度做“分组中国式排名”。先看我们的需求:我们要根据班级对学生做成绩的排名,实现的效果图如下:还是根据上一节的思路来完成,首先我们进入到Power Query界面,将原始数据复制成三份。首先我们处理“索引表”,删除不需要的“姓名”一列,选中剩余的两列(一定要选中两列),做删除重复项的操作。接下来我们给上面的原创 2017-01-15 15:04:37 · 8854 阅读 · 6 评论 -
34、Power Query-中国式排名
本节要点:Power Query-中国式排名何谓“中国式排名”呢?在排名计算方法中,中国人的习惯是,无论有几个并列第2名,之后的排名仍应该是第3名,即并列排名不占用名次。例如:对数列{3,2,2,1}从大到小进行排名,按国际通行的惯例是{第一名,第二名,第二名,第四名},比如奥运会某场比赛出现并列银牌,则不发铜牌,而按中国式排名,结果是{第一名,第二名,第二名,第三名}。原创 2017-01-13 21:43:58 · 6750 阅读 · 4 评论 -
33、Power Query-统计员工完成业绩的记录
本节通过一个实例来讲解之前的所学的行、列、表的构造。需求如下图所示,要求:统计实际业绩大于等于计划业绩的数据记录(完成业绩)。比如第一个员工“休闲海”,他就是这样的好员工,实际业绩大于等于计划业绩,则会统计出来。分析:我们使用上图的方法,将“行1到倒数第二行”的数据同“第二行至最后一行”的数据相比,若小于等于,则为True。另外一个条件是 “行1到倒数第二行”的原创 2017-01-13 14:10:58 · 5532 阅读 · 0 评论 -
32、Power Query-利用自定义函数获取指定页数数据
本节要点:Power Query-利用自定义函数获取指定页数数据我们前面不是有一节已经讲过如何获取网页的数据了么?http://blog.csdn.net/zhongguomao/article/details/53908101细心的朋友或许已经发现,之前获取的网页数据比较不灵活,仅仅是当前页面的数据,若是有好多页的数据,之前的方法貌似不好使。今天我们就简单的带大家认识原创 2017-01-13 10:37:05 · 14540 阅读 · 2 评论 -
31、Power Query-行、列、表的构造
本节重点:Power Query-行、列、表的构造之前我们讲过了已有数据的构造,这次我们使用空查询来简单讲解一下行(record)、列(list)、表(table)的创建。一、行(record)如何来构造一行数据呢?在编辑栏中用“[]”符号进行书写,例如:“[a=1,b=2,c=3]”表明有这样一行记录,“a","b","c"三列的记录分别”1“,”2“,”3“。我们对原创 2017-01-13 10:11:10 · 15572 阅读 · 7 评论 -
30、Power Query-搞清Power Query中的表结构
本节重点:搞清Power Query中的表结构下面举例说明这个PQ的表结构:我们随便选中一个单元格,右击,选择“深化”。显示了这个单元格的值,然后我们可以观察这个“编辑栏”,“= 更改的类型{9}[总分]”。这个什么意思呢,“[总分]”我们好理解,是上图中的“总分”一列,那么“{9}”呢,这里不卖关子了,其实就是索引号为“9”的行。这里我们需要正视一个概念:Po原创 2017-01-12 21:23:50 · 13052 阅读 · 2 评论 -
29、Power Query-分支语句的进阶
本节重点:Power Query-分支语句的进阶例子:同样是之前的数据源,现在要根据多列进行判断,要求求出每门功课都及格的人数。比如上图中的“休闲海”小朋友,他的三门功课都是">=60"的,即显示“合格”,不需要进行补考。分析:我们发现之前用的“条件列”的向导模式已经满足不了我们的要求了,寻思着只能用代码的方式去解决了。具体操作:我们选择“添加列”——“添加自定义列”。原创 2017-01-12 14:55:34 · 13445 阅读 · 0 评论 -
28、Power Query-分支语句的妙用
本节重点:认识Power Query里面的分支语句,并且介绍简单的运用。例子:下图是一个班级学生的考试成绩,比如我们约定下面的规则,统计优良中差的人数。优 >=250良 >=220中 >=180差 分析:看这样的需求,若我们用之前学过的方法去处理单元格,显得非常繁琐,其实PQ提供给我们了更简便的条件分支判断语句。位置:1、“添加列”——“条件列”。原创 2017-01-12 14:16:09 · 8249 阅读 · 0 评论 -
27、Power Query-日期与时间数据处理实例(图书室借书统计)
本节要点:Power Query-日期与时间数据处理实例(图书室借书统计)承接上一节的需求,我们引入切片器的功能,对图书室的借书以及归还情况进行查询和筛选,效果图如下:对“借书状态”和“归还状态”进行筛选,亦可同时筛选。要点:计算截止到目前已借阅的天数,同当时的“借书天数”进行相减,从而区分“刚到期”、“未到期”、“已到期”。具体实现:这里我们就需要用到之前课程讲到的“原创 2017-01-12 12:59:54 · 17359 阅读 · 0 评论 -
26、Power Query-日期与时间数据处理实例(统计出某年各月各周的借书人数)
本节重点:Power Query-日期与时间数据处理实例(统计出某年各月各周的借书人数)需求如下:上图所示,根据左侧的数据源,统计出2016年各月各周的借书人数(效果如右侧所示)。分析:很明显,这个需求是对日期的一个操作,其实仅需要用到左侧的第一列即可。处理思路:直接用数据透视表处理有难度,最终还是采用PQ进行加工,得到“第几周”以及“周开始日”“周结束日”。主要运用知原创 2017-01-12 10:49:23 · 14865 阅读 · 2 评论 -
25、Power Query-日期与时间数据处理
本节知识点:Power Query-日期与时间数据处理日期与时间数据处理功能位置(同样在“转换”和“添加列”菜单中都存在):原创 2017-01-11 15:54:51 · 39587 阅读 · 2 评论 -
24、Power Query-数学运算的应用(统计男女人数)
本节重点:Power Query-数学运算的应用(统计男女人数)数学运算功能位置:这一块应用起来比较简单,用处不是特别大,这里仅作简单说明。例子:下图展示的人员表是姓名+身份证号码,现在要统计出男女的人数。思路:身份证号码的第17位则表示性别(奇数为男,偶数为女)。所以很明显第一步我们需要取到第17位的数字。我们习惯用”转换“菜单下的功能,而不是”添加列“下原创 2017-01-11 13:41:03 · 9357 阅读 · 1 评论 -
23、Power Query-XML与JSON数据获取
首先扫盲:JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式。它基于JavaScript的一个子集;XML(Extensible Markup Language)即可扩展标记语言,Xml是Internet环境中跨平台的,依赖于内容的技术,是当前处理结构化文档信息的有力工具。两者的共同优点是都是文本表示的数据格式,可以跨平台、跨系统交换数据。原创 2017-01-11 10:31:20 · 8205 阅读 · 1 评论 -
22、Power Query-文本字符的精确提取
首先展示一下“提取”功能点的位置,在“转换”菜单和“添加列”菜单中都有这个功能,唯一的区别在于:“转换”菜单:在选中的当前列进行直接处理;“添加列”菜单:在选中列进行处理之后新增一列显示;这边的“提取”功能里面有如下四个功能:1、长度:相当于Excel里面的Len()函数。2、首字符:相当于Excel里面的Left()函数。3、结尾字符:相当于Excel里面原创 2017-01-10 16:58:05 · 27056 阅读 · 5 评论 -
21、Power Query-列文本格式处理
申明:功能是简单的功能,当然用基础的功能也同样可以实现,但是这是一个讲究效率的时代,多一个处理手段,未免不是好事。例1:根据左边的考试成绩格式转变为右边的显示方式,并且可以在新增修改删除时候动态更新。其实用了Power Query就非常简单,首先进行左边的替换。后面的处理就用到了我们今天的知识点:“增加后缀”。妥妥的达到了我们的效果。原创 2017-01-10 15:36:05 · 10054 阅读 · 1 评论 -
20、Power Query-数据合并、拆分
本节重点:熟练使用Power Query中的合并以及拆分功能。例子:下面是学生的考试成绩表,要求将其合并到一列中,用“/”分隔,没有考试成绩的不显示。效果图:这里我们将用到PQ中的“合并”功能,具体思路是:将数据源分为三个表进行操作。表1、原始数据源保持不变;表2、将分数用科目名称填充;表3、将分数用分隔符“/”填充;最后进行三表按照“姓名”进行合并,最原创 2017-01-10 10:53:29 · 24441 阅读 · 9 评论 -
19、Power Query-快速分析各产品的销售情况
本节知识点:Power Query反转行的妙用(路径:“转换”——“反转行”)何谓反转行,即将行内容反过来,有别于“排序”。例如 :下面直接通过一个实用的例子来说明:《快速分析各产品的销售情况》比如上图中首列“销售日期”记录了销售的日期及时间,按先后顺序排列,“钢材”一列记录了销售的品种,“吨数”一列记录了销售的数量。(例子仅供参考,如何雷同,纯属巧合,切勿原创 2017-01-09 21:01:13 · 5933 阅读 · 3 评论 -
18、Power Query-SQL筛选
我们在Power Query里面进行筛选是相当灵活的,但是功能确是有限制,无法做到模糊查询。如下图所示,分别是在PQ中对文本、日期、数字进行筛选,功能如字面意思所示。原创 2017-01-09 15:59:29 · 10841 阅读 · 2 评论 -
17、Power Query-户口信息统计
本节知识点:填充的妙用——户口信息统计案例:比如有大量的户口信息,几千几万条,我们要快速的按照户主统计出一户人家有多少男丁,多少女丁,如下图所示。效果如下:这里我们需要用到判断语句,可以拿之前学过的知识点“ODBC方式导入”直接用。再次温习连接字符串。2007以上版连接字符串:"driver={Microsoft Excel原创 2017-01-07 13:29:12 · 4545 阅读 · 4 评论 -
16、Power Query-批量生成工资条的妙用
本节所用知识点:Power Query-表头和首行互转的妙用。实用案例:公司员工工资条的批量制作。原始数据如下图所示:然而我们人事部打印的要求需要一行标题,一行明细,一行空格,以此类推,如下图所示:其实用Excel的普通功能也可以实现,但今天我们就用一个新方法来实现,具体思路:比如某公司有1000个员工,那我们需要准备1000个表头,1000条明细记录已经存在,原创 2017-01-05 22:40:26 · 6697 阅读 · 3 评论 -
15、Power Query-行列管理实例应用
Power Query-行列管理实例应用数据源如下图,是学生三门课程的考试成绩,其中有一次通过的,有考过多次的:要求:1、找出每个课程考试三次以上的(包含三次);2、找出三个课程同时一次考试通过的;3、找出每个课程一次考试通过的;貌似以现在我们的excel功能很难做到,下面我们就用PQ的方式跟大家讲讲具体的实现。我们用之前学过的数据导入方式;"从excel文件导原创 2017-01-03 21:59:40 · 11500 阅读 · 3 评论 -
14、Power Query-行列管理
Power Query-行列管理要求:根据上图中的班级、学生、成绩,提取每个班级里面分数最高的以及最低的学生,按如下要求显示:为了讲解这个行列的应用,这里采用这种方法进行处理,具体思路是:首先找到每个班级的首名,其次找到每个班级的尾名,最后进行追加合并。首先获取每个班级的第一名,先按班级进行升序排序,然后按分数进行降序排序,这样的话,我们只要提取每个班级的第原创 2017-01-03 14:27:52 · 7444 阅读 · 5 评论 -
13、Power Query-逆透视列的实例(下)
逆透视列——列转行实现二转一维要求:原始数据是单人单行单月的工资明细。我们需要整理成下图的效果。废话不多说,直接选表进入到PQ操作界面。我们发现,除去基本信息“姓名”、“部门”、“月份”外,其他都是以列的形式二维表呈现的。我们将这些列选择起来,直接从列的方向转向行。然后我们要取属性一列的后两个字,因为按规律可以发现,它表明了工资的原创 2017-01-03 11:18:04 · 14154 阅读 · 1 评论 -
13、Power Query-逆透视列的解析(上)
逆透视列——列转行实现二转一维要求:将左边的二维表转换为右边的一维表,便于我们去进行分析处理。下面我们就用PQ的方式去进行转换。原创 2016-12-31 16:57:13 · 15537 阅读 · 0 评论 -
1、大道至简的数据处理工具-(Microsoft Power Query入门)
大道至简的数据处理工具-Microsoft Power Query告别复杂的excel函数,excel VBA编程,让一切回归简单与职能。什么样的人群适合这样的一个工具:1、出纳、会计、统计、仓管、数据分析等与数据打交道的的岗位。2、不想学处理数据的函数、VBA编程等复杂的功能,但工作又有需要的人。究竟什么是Microsoft Power QueryA、获取数据:B、清原创 2016-12-23 15:23:43 · 77748 阅读 · 4 评论