基于Excel2013的PowerQuery入门

2018年6月19日笔记

所有要进行操作的文件下载链接: https://pan.baidu.com/s/10VtUZw8G-Ly-r4VypntjiA 密码: y5qu 下载成功后，整个文件夹如下图所示。

文件夹图示.png

0.Power Query与其他PowerBI系列组件的关系

获取数据——>分析数据——>呈现数据 PowerQuery获取和整理——>PowerPivot建模和分析——>PowerView交互式报表||PowerMap地图可视化——>PowerBI在线版可视化仪表板发布和分享

关系图.png

简而言之：获取——>分析——>呈现——>发布

1.数据导入Power Query并进行追加查询

新建一个空的excel文件，在导航栏的POWER QUERY中选择从文件——>从Excel

导入按钮图示.png

导入一店数据1.png

导入一店数据2.png

点击加载，一店.xlsx这个文件的数据会被导入到工作薄查询中。

工作簿查询.png

用相同的方法加载下载文件中的二店.xlsx文件，结果图示如下。

image.png

双击上图中的一店，出现下图所示界面。选择删除最前面几行。

删除前三行1.png

删除前三行2.png

打开左边查询栏1.png

点击上图所示的按钮，出现下图所示界面。

打开左边查询栏2.png

追加查询1.png

追加查询2.png

关闭并上载1.png

点击上面的按钮，回到Excel界面， 出现下图所示的工作簿查询。

关闭并上载2.png

从上图可以看出一店从6377行增加到10739行。

2.数据的行列管理及筛选

删除行

打开下载文件中的02-数据的行列管理及筛选.xlsx，出现如下图所示。

删除空行1.png

如下图所示，选定要加载的区域，即A3:C25区域

删除空行2.png

删除空行3.png

点击上图箭头所示处，出现下图所示。

删除空行4.png

删除空行5.png

成功删除空行.png

删除间隔行

删除间隔行1.png

设置数值如下图所示，然后点击确定。

删除间隔行2.png

成功删除间隔行.png

关闭并上载

关闭并上载1.png

设置数值如下图所示，点击下图中的加载。

关闭并上载2.png

成功关闭并上载.png

填充行

如下图所示，打开第二个表

加载到查询编辑器1.png

加载到PowerQuery中如下图所示

加载到查询编辑器2.png

将第一行作为标题.png

成功将第一行作为标题.png

删除间隔行1.png

删除间隔行2.png

成功删除最后一行.png

填充按钮位置.png

成功填充.png

选择导航栏中的开始中的关闭并加载至，出现下图所示，填入现有工作表的你想填入的位置。

加载设置.png

成功填充并将成果加载至原有表.png

3.数据格式的转换

打开下载文件中的03-数据格式的转换.xlsx，如下图所示。

打开文件图示.png

加载数据至PowerQuery中.png

修整1.png

成功修整.png

拆分列1.png

拆分列2.png

成功拆分列.png

修整1.png

成功修整.png

合并的期间选择多列的时候，要先选择姓名.1那一列，再选择姓名.2

合并列1.png

合并列2.png

成功合并.png

拆分列1.png

拆分列2.png

成功拆分.png

修整.png

成功修整.png

合并列1.png

合并列2.png

成功合并.png

首字母大写按钮位置.png

成功设置首字母大写.png

转成文本1.png

替换1.png

替换2.png

成功替换.png

可以将下图与结果表进行对照

成功加载.png

4.数据的拆分合并提取

打开下载文件中的04-数据的拆分合并提取.xlsx，如下图所示。

打开文件图示.png

加载至查询编辑器.png

修改数据类型为文本.png

成功修改数据类型.png

添加重复列.png

按字符数拆分列1.png

按字符数拆分列2.png

列重命名.png

成功列重命名.png

提取1.png

提取2.png

选定新产生的一列转换数据类型为整数

转化1.png

转化2.png

转化3.png

转换4.png

转换5.png

转换6.png

转换7.png

成功转换.png

其他的各项步骤原理相同，省略。

5.删除重复项

在下载文件中打开05-删除重复项.xlsx，如下图所示。

删除重复项1.png

加载数据到PowerQuery中.png

客户首次购买分析

选定下单日期这一列，进行升序排序。

下单日期升序排序.png

选定客户名称这一列，进行删除重复项

对客户名称删除重复项.png

首次购买分析结果.png

客户最大订单分析

选定金额这一列，进行降序排序

金额降序排序.png

选定客户名称这一列，进行删除重复项

image.png

客户最大订单分析结果.png

多次购买客户分析

选定客户名称这一列，进行保留重复项

保留重复项按钮位置.png

只有1次购买记录的客户会被删除，多次购买记录的客户会被保留。 例如一个客户有3次购买记录，保留重复项后该客户被保留3次购买记录。

保留结果.png

6.删除错误

打开下载文件中的06-删除错误.xlsx，如下图所示。

打开文件图示.png

加载数据至查询编辑器中.png

选定日期这一列，将数据类型改为整数。

image.png

删除错误行.png

成功删除.png

选择导航栏开始中的关闭并上载至，并按照下图所示设置。

关闭并上载至原有表.png

成功删除错误行.png

7.转置和反转

打开下载文件中的07-转置和反转.xlsx，如下图所示。

打开文件图示.png

加载数据到PowerQuery中.png

成功加载结果.png

将标题作为第一行.png

转置按钮位置.png

转置后结果.png

将第一行作为标题.png

取消自动更改类型.png

关闭并上载至原有表格.png

上载设置.png

转置结果.png

如果上载位置有偏差，自己可以移动表格位置调整至上图所示效果

8.透视和逆透视

打开下载文件中的08-透视和逆透视.xlsx，如下图所示

打开文件图示.png

不要选中第一列，选中后面的列，然后点击下图所示的逆透视列。

逆透视1.png

成功逆透视结果.png

选择关闭并上载至，在窗口中设置值如下图所示。

上载1.png

上载2.png

出现上图所示2月，3月的错误，要设置___单元格格式。

上载3.png

成功上载结果.png

透视——不要聚合

选择不要聚合这个表，加载至PowerQuery中第一步操作如下。

透视1.png

值列为是否完成销售额，点开高级选项，聚合值函数选择不要聚合，最后点击确定。

透视2.png

成功透视结果.png

加载至原有表.png

成功加载至原有表结果.png

9.分组依据

打开下载文件中的09-分组依据.xlsx，如下图所示。

打开文件图示.png

分组依据1.png

分组依据2.png

成功分组结果.png

10.添加列

打开下载文件中的10-添加列.xlsx，如下图所示。

打开文件图示.png

进行分组操作.png

逆序排序.png

添加索引列.png

自定义列按钮位置.png

自定义列设置.png

成功添加自定义列.png