Power Query
SAP剑客
一日不读书,胸臆无佳想。一月不读书,耳目失精爽。
展开
-
【汇总篇】如何利用Excel高效地处理数据
现将2017年的专栏“Power Query”汇总成PDF格式,便于学习管理。链接:https://pan.baidu.com/s/14U2RZ9meSuHphDdyphEu0Q提取码:8iwc复制这段内容后打开百度网盘手机App,操作更方便哦...原创 2019-05-17 21:16:07 · 2753 阅读 · 2 评论 -
29、Power Query-分支语句的进阶
本节重点:Power Query-分支语句的进阶例子:同样是之前的数据源,现在要根据多列进行判断,要求求出每门功课都及格的人数。比如上图中的“休闲海”小朋友,他的三门功课都是">=60"的,即显示“合格”,不需要进行补考。分析:我们发现之前用的“条件列”的向导模式已经满足不了我们的要求了,寻思着只能用代码的方式去解决了。具体操作:我们选择“添加列”——“添加自定义列”。原创 2017-01-12 14:55:34 · 13363 阅读 · 0 评论 -
41、Power Query-Text.Combine函数进阶2
本节继续讲解Power Query-Text.Combine函数进阶。下面看一个更加有趣的例子。比如上图,有多列,我们需要求出唯一值,标准有两个,分别是以左边为基准和以右边为基准。比如以左边为基准,这个“袋子”,在列“核心词”里面已经存在,则在列“热搜分词”里面则应该剔除。貌似一列去重比较简单(“删除重复项”即可),根据这么多列求唯一的话有难度!我们不妨先进到Power原创 2017-01-17 13:27:08 · 6078 阅读 · 2 评论 -
28、Power Query-分支语句的妙用
本节重点:认识Power Query里面的分支语句,并且介绍简单的运用。例子:下图是一个班级学生的考试成绩,比如我们约定下面的规则,统计优良中差的人数。优 >=250良 >=220中 >=180差 分析:看这样的需求,若我们用之前学过的方法去处理单元格,显得非常繁琐,其实PQ提供给我们了更简便的条件分支判断语句。位置:1、“添加列”——“条件列”。原创 2017-01-12 14:16:09 · 8214 阅读 · 0 评论 -
27、Power Query-日期与时间数据处理实例(图书室借书统计)
本节要点:Power Query-日期与时间数据处理实例(图书室借书统计)承接上一节的需求,我们引入切片器的功能,对图书室的借书以及归还情况进行查询和筛选,效果图如下:对“借书状态”和“归还状态”进行筛选,亦可同时筛选。要点:计算截止到目前已借阅的天数,同当时的“借书天数”进行相减,从而区分“刚到期”、“未到期”、“已到期”。具体实现:这里我们就需要用到之前课程讲到的“原创 2017-01-12 12:59:54 · 17223 阅读 · 0 评论 -
26、Power Query-日期与时间数据处理实例(统计出某年各月各周的借书人数)
本节重点:Power Query-日期与时间数据处理实例(统计出某年各月各周的借书人数)需求如下:上图所示,根据左侧的数据源,统计出2016年各月各周的借书人数(效果如右侧所示)。分析:很明显,这个需求是对日期的一个操作,其实仅需要用到左侧的第一列即可。处理思路:直接用数据透视表处理有难度,最终还是采用PQ进行加工,得到“第几周”以及“周开始日”“周结束日”。主要运用知原创 2017-01-12 10:49:23 · 14722 阅读 · 2 评论 -
40、Power Query-Text.Combine函数进阶
本节主题:通过上一节的例子进阶处理,加深Text.Combine的印象。回顾一下上一节的数据源。这次我们需要的结果没有上一节那么简单了,要根据月份业绩列分别列示在一行,目标效果如下:首先我们还是根据上一节的方法先根据“姓名”分组一下“月份”以及“业绩”。惨了,结果显示的是两列Error!Take it easy,这不是我们所关注的,我们需要更改它原创 2017-01-16 15:39:09 · 8296 阅读 · 3 评论 -
25、Power Query-日期与时间数据处理
本节知识点:Power Query-日期与时间数据处理日期与时间数据处理功能位置(同样在“转换”和“添加列”菜单中都存在):原创 2017-01-11 15:54:51 · 39342 阅读 · 2 评论 -
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 · 13578 阅读 · 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 · 14081 阅读 · 1 评论 -
37、Power Query-不使用IF嵌套进行匹配
本节要点:Power Query-不使用IF嵌套进行匹配(突破IF语句嵌套写法)标题可能很笼统,话不多说,直接看案例。左表是学生成绩表,右边是成绩范围对应的等级,目的是匹配到学生成绩的等级。这次我们不使用IF语句进行判断,结合之前所学,讲解一个新的方法。首先依旧是将左右两个表添加进PQ界面。首先处理“范围表”。将“范围”一列按照“-”进行拆分。结原创 2017-01-16 10:31:35 · 19922 阅读 · 3 评论 -
24、Power Query-数学运算的应用(统计男女人数)
本节重点:Power Query-数学运算的应用(统计男女人数)数学运算功能位置:这一块应用起来比较简单,用处不是特别大,这里仅作简单说明。例子:下图展示的人员表是姓名+身份证号码,现在要统计出男女的人数。思路:身份证号码的第17位则表示性别(奇数为男,偶数为女)。所以很明显第一步我们需要取到第17位的数字。我们习惯用”转换“菜单下的功能,而不是”添加列“下原创 2017-01-11 13:41:03 · 9289 阅读 · 1 评论 -
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 · 7982 阅读 · 1 评论 -
30、Power Query-搞清Power Query中的表结构
本节重点:搞清Power Query中的表结构下面举例说明这个PQ的表结构:我们随便选中一个单元格,右击,选择“深化”。显示了这个单元格的值,然后我们可以观察这个“编辑栏”,“= 更改的类型{9}[总分]”。这个什么意思呢,“[总分]”我们好理解,是上图中的“总分”一列,那么“{9}”呢,这里不卖关子了,其实就是索引号为“9”的行。这里我们需要正视一个概念:Po原创 2017-01-12 21:23:50 · 12954 阅读 · 2 评论 -
Power Query 应用领域有哪些?
任何可以用Excel处理数据的领域,都可以使用Powerquery渗入,很多功能在Excel端很麻烦在PowerQuery端可视化或者一个简单的公式就可以实现。首先为什么要学习自助式BI,我们看看高飞老师在PowerBI极客中给出大家的答案:自助式BI,全面提升你的数据分析能力 - 知乎专栏前言:PowerBI简单介绍:Power BI 是基于云的商业数据分析和共享工具,它能将复杂转载 2018-01-22 08:45:27 · 14434 阅读 · 0 评论 -
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 · 15447 阅读 · 7 评论 -
49、Power Query-合并查询温故知新
这一节我们来重温一下合并查询,介绍一个新的引入方式,废话不多说,直接看需求。看上表,现需要统计各个等级的学生数量,我们利用Power Query这种方式的优势在于新增修改学生成绩条目以及等级情况条目的时候可以动态刷新。执行效果如下:下面我们介绍一下如何动态的获取这两张表(用选择——“从表”的方式也是可以的):1、学生成绩表2、等级对应表我们不妨使用函数动态的原创 2017-02-15 10:06:19 · 8456 阅读 · 7 评论 -
48、Power Query-大量复杂数据的整理汇总
工作中,生活中不免会遇到需要整理大量复杂数据的情况,单纯的靠手动去搜索统计固然可以,但是效率极其低下。如下图,列举了1990年~2015年NBA美职篮全明星的参赛名单,现在需要统计出每个人的参赛次数。实现效果如下:下面直接来简单谈一下它的实现思路,首先我们整理掉一些不需要的数据,例如“NBA2008年全明星阵容”相关的字眼。我们利用第一列的“文本筛选器”剔除掉开头不原创 2017-02-13 16:53:00 · 32400 阅读 · 4 评论 -
47、Power Query-处理典型的中国式二维表格转一维
本节要点:典型的中国式二维表格转一维的处理。例子:看如下图的数据源表为典型的二维表,需要将其转置成为一维表显示。直接加载到Power Query中进行处理。合并列1和列2。接着进行“转置”操作,姓名和部门跑到列的方向,然后将第一列向下进行填充。将我们合并的首行提升为标题行。选中前两列,逆透视其他列。再将第二列选中进原创 2017-01-21 11:21:00 · 8880 阅读 · 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 · 4673 阅读 · 0 评论 -
50、Power Query-Text.Contains的学习
比如现在有这样一个需求,需要筛选某列规格里面的一些内容。比如我们要筛选出包含“110”以及“123”以及“CF”的规格,当然用条件格式也是可以的。若是两个包含,我们可以直接筛选得到,如下图操作。没错,就是文本筛选器里面的“包含”。那么最后一个条件该如何引入呢?我们不妨直接看它生成的公式,是不是豁然开朗?= Table.SelectRo原创 2017-02-28 16:51:40 · 14028 阅读 · 2 评论 -
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 · 11279 阅读 · 3 评论 -
44、Power Query-具体查看函数的方法
方法一、根据帮助来查询。1、在查询页面点击右上角的帮助按钮;2.在弹出的网页中,找到“PowerQuery公式类别”,然后再在弹出的网页中查找自己需要的函数;不过最近Microsoft好像大姨妈,一点就报错:然后只能返回,在界面中点击“请在 此处 中查找本文的英文版本以便参考。”进入英文版本查询。这个方法就是我们之前课程中介绍的方法:https://ms原创 2017-01-18 13:12:35 · 14686 阅读 · 0 评论 -
43、Power Query-函数综合应用
本节重点:结合之前所学的两个函数Text.Remove( )和Text.Combine( )完成以下功能。上图数据源是某公司某月各部门的人员工资,呈现在一个单元格内,比较凌乱。需求是:根据部门汇总工资,并显示人员在一个单元格内,以“/”分隔。具体实现如下:首先根据分隔符“-”进行拆分列。然后根据拆分出来的第二列进行“姓名”以及“工资”的分离。这里利用原创 2017-01-18 10:15:15 · 7796 阅读 · 4 评论 -
32、Power Query-利用自定义函数获取指定页数数据
本节要点:Power Query-利用自定义函数获取指定页数数据我们前面不是有一节已经讲过如何获取网页的数据了么?http://blog.csdn.net/zhongguomao/article/details/53908101细心的朋友或许已经发现,之前获取的网页数据比较不灵活,仅仅是当前页面的数据,若是有好多页的数据,之前的方法貌似不好使。今天我们就简单的带大家认识原创 2017-01-13 10:37:05 · 14469 阅读 · 2 评论 -
36、Power Query-多条件合并查询
本节要点:Power Query-多条件合并查询之前我们学过两表合并,多表追加,但要同时满足多个条件的表表合并,我们如何实现呢?我们先看下面一个例子:左表是产品对应等级的销售数量,而右表示产品对应等级的单价,现在我们需要进行合并汇总出如下结果:汇总每个产品每个等级的平均价,销售总量,以及销售总额。思路:两表合并,利用透视表完成汇总。首先创建左右两表的连接。原创 2017-01-16 09:36:58 · 19865 阅读 · 2 评论 -
23、Power Query-XML与JSON数据获取
首先扫盲:JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式。它基于JavaScript的一个子集;XML(Extensible Markup Language)即可扩展标记语言,Xml是Internet环境中跨平台的,依赖于内容的技术,是当前处理结构化文档信息的有力工具。两者的共同优点是都是文本表示的数据格式,可以跨平台、跨系统交换数据。原创 2017-01-11 10:31:20 · 8141 阅读 · 1 评论 -
22、Power Query-文本字符的精确提取
首先展示一下“提取”功能点的位置,在“转换”菜单和“添加列”菜单中都有这个功能,唯一的区别在于:“转换”菜单:在选中的当前列进行直接处理;“添加列”菜单:在选中列进行处理之后新增一列显示;这边的“提取”功能里面有如下四个功能:1、长度:相当于Excel里面的Len()函数。2、首字符:相当于Excel里面的Left()函数。3、结尾字符:相当于Excel里面原创 2017-01-10 16:58:05 · 26817 阅读 · 5 评论 -
7、Power Query-合并查询
Power Query合并查询——比VLOOKUP函数更强更智能比如我们有两个工作表,一个“销售记录”一个“单价表”,我们要来汇总所有产品的销售总额和平均额,如下图:这个时候我们就可以用到一个强大的功能,比Vlookup好用多了,Vlookup每次增加还要拖动,烦死了,用了这个,一劳永逸。合并查询这里就需要两个表进行链接了,让他们从此有了一个对应,现在我们还选不了,原创 2016-12-29 14:45:52 · 16046 阅读 · 8 评论 -
6、Power Query-SQL与PQ技术的强强联合
数据来源之ODBC——SQL与PQ技术的强强联合比如有的功能SQL比较简单,又比如有的功能用Power Query更加简单,我们可以用他们联合的方式增强工作效率。本节要求:求出姓名为两个字的人,并且工资大于等于4000元人名币的,给他们做一个排名。效果图:数据来源:D:\Power Query\Power Query 6这次我们不用直接导入工作薄了,这样无法原创 2016-12-29 09:51:41 · 14553 阅读 · 4 评论 -
5、Power Query-抓取网页数据做漂亮的图表
数据来源之网页数据——抓取网页数据做漂亮的图表要求:根据百度百科网站词条http://baike.baidu.com/item/NBA中的历届总冠军表目做出动态图表。这次就直接拷贝这个Web地址咯。加载这个页面上所有的数据,我们找到我们想要的,点击“编辑”。看样子是不需要怎么编辑了,直接关闭并上载至连接。这次特别开心,可以用到透视图的功能了,很好原创 2016-12-28 15:08:17 · 17706 阅读 · 5 评论 -
4、Power Query-智能汇总工作簿下的指定或所有工作表数据
数据来源之工作簿——智能提取汇总指定文件夹下的工作簿数据要求:根据指定目录的文件夹内的所有工作薄进行汇总分析。工资下每个年份每个部门1-12月的工资在此维护,现在的要求是要看看每个年份,每个月一共发了多少工资。原创 2016-12-28 11:52:42 · 20040 阅读 · 12 评论 -
3、Power Query-智能汇总工作簿下的指定或所有工作表数据
数据来源之工作表(智能汇总工作簿下的所有工作表数据)本节要求:统计出数据源工作表里面各个类别,各年的总数量。比如下图的,是在D盘Power Query文件夹下面的一个数据源文件:其实会用sql语句的童鞋会觉得特别简单,这里我们用新方法来实现。选择“Power Query”下面的“从文件”——“从Excel”我们要选择多个表的时候可以点击“选择多项”,然后原创 2016-12-24 12:04:02 · 25488 阅读 · 18 评论 -
2、Power Query-动态汇总单元格区域数据
数据来源之单元格区域数据(动态汇总单元格区域数据)这次主要讲解power query的基本操作界面。任务:求出各个行业第一、二季度的总数据。例子非常简单,聪明的朋友可能会认为用透视图更加简单,这里我们只是举例。首先获取数据(从表格)数据——从表格 表包含标题,有点像“插入——表格”,其实是一个意思。这个时候马上切换到了一个区域,这个区域就是我们以后主要原创 2016-12-23 21:42:17 · 25195 阅读 · 15 评论 -
1、大道至简的数据处理工具-(Microsoft Power Query入门)
大道至简的数据处理工具-Microsoft Power Query告别复杂的excel函数,excel VBA编程,让一切回归简单与职能。什么样的人群适合这样的一个工具:1、出纳、会计、统计、仓管、数据分析等与数据打交道的的岗位。2、不想学处理数据的函数、VBA编程等复杂的功能,但工作又有需要的人。究竟什么是Microsoft Power QueryA、获取数据:B、清原创 2016-12-23 15:23:43 · 77294 阅读 · 4 评论 -
16、Power Query-批量生成工资条的妙用
本节所用知识点:Power Query-表头和首行互转的妙用。实用案例:公司员工工资条的批量制作。原始数据如下图所示:然而我们人事部打印的要求需要一行标题,一行明细,一行空格,以此类推,如下图所示:其实用Excel的普通功能也可以实现,但今天我们就用一个新方法来实现,具体思路:比如某公司有1000个员工,那我们需要准备1000个表头,1000条明细记录已经存在,原创 2017-01-05 22:40:26 · 6644 阅读 · 3 评论 -
15、Power Query-行列管理实例应用
Power Query-行列管理实例应用数据源如下图,是学生三门课程的考试成绩,其中有一次通过的,有考过多次的:要求:1、找出每个课程考试三次以上的(包含三次);2、找出三个课程同时一次考试通过的;3、找出每个课程一次考试通过的;貌似以现在我们的excel功能很难做到,下面我们就用PQ的方式跟大家讲讲具体的实现。我们用之前学过的数据导入方式;"从excel文件导原创 2017-01-03 21:59:40 · 11465 阅读 · 3 评论 -
14、Power Query-行列管理
Power Query-行列管理要求:根据上图中的班级、学生、成绩,提取每个班级里面分数最高的以及最低的学生,按如下要求显示:为了讲解这个行列的应用,这里采用这种方法进行处理,具体思路是:首先找到每个班级的首名,其次找到每个班级的尾名,最后进行追加合并。首先获取每个班级的第一名,先按班级进行升序排序,然后按分数进行降序排序,这样的话,我们只要提取每个班级的第原创 2017-01-03 14:27:52 · 7421 阅读 · 5 评论 -
13、Power Query-逆透视列的实例(下)
逆透视列——列转行实现二转一维要求:原始数据是单人单行单月的工资明细。我们需要整理成下图的效果。废话不多说,直接选表进入到PQ操作界面。我们发现,除去基本信息“姓名”、“部门”、“月份”外,其他都是以列的形式二维表呈现的。我们将这些列选择起来,直接从列的方向转向行。然后我们要取属性一列的后两个字,因为按规律可以发现,它表明了工资的原创 2017-01-03 11:18:04 · 14116 阅读 · 1 评论 -
17、Power Query-户口信息统计
本节知识点:填充的妙用——户口信息统计案例:比如有大量的户口信息,几千几万条,我们要快速的按照户主统计出一户人家有多少男丁,多少女丁,如下图所示。效果如下:这里我们需要用到判断语句,可以拿之前学过的知识点“ODBC方式导入”直接用。再次温习连接字符串。2007以上版连接字符串:"driver={Microsoft Excel原创 2017-01-07 13:29:12 · 4506 阅读 · 4 评论