基于Excel2013的PowerQuery入门

  1. 云栖社区>
  2. 博客>
  3. 正文

基于Excel2013的PowerQuery入门

潇洒坤 2018-06-21 20:04:00 浏览1808
展开阅读全文

2018年6月19日笔记

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

img_919cff018c829a5abc25b489611fe719.png
文件夹图示.png

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

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


img_444732308c9af9a4b14c519396171e55.png
关系图.png

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

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

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

img_ba244c5f53ef8fc26659aa86efb9714e.png
导入按钮图示.png

img_182235120a300a79d868b1dfed5332ed.png
导入一店数据1.png

img_8b6842e8f66eb476c99cc34c8a07a6be.png
导入一店数据2.png

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

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

双击上图中的一店,出现下图所示界面。选择删除最前面几行
img_546ba62da1dd0f9be1b709a84b78240c.png
删除前三行1.png

img_5fd7ee60b61a6b5f568340b507e853c6.png
删除前三行2.png

img_86a47bc09aaa6bbd865323735a5de0cb.png
打开左边查询栏1.png

点击上图所示的按钮,出现下图所示界面。
img_a7342adce254011b0676eef928f7812d.png
打开左边查询栏2.png

img_42b7ba8d15c602cd4e733419bd87e543.png
追加查询1.png

img_8e24bc15d1216be686a386de5da3cb62.png
追加查询2.png

img_75da3892da23f38a7687c4b56070c9f7.png
关闭并上载1.png

点击上面的按钮,回到Excel界面, 出现下图所示的工作簿查询
img_3f78010c05cad10e8f3158889fa1f049.png
关闭并上载2.png

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

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

删除行

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

img_d8abd9b306ffd5461ac0384bc8911ce3.png
删除空行1.png

如下图所示,选定要加载的区域,即A3:C25区域
img_f0c1ff7e4b647d1beaa201e93a78d8dc.png
删除空行2.png

img_c7129c2cede663e8a7d80de9307aa2fd.png
删除空行3.png

点击上图箭头所示处,出现下图所示。
img_55d696ca2cb7470b3344709b2fe7497f.png
删除空行4.png

img_a8eae4b6d2ab57b439c1f292b78c49d0.png
删除空行5.png

img_1d4df14929460abd85f73469e72ac0cd.png
成功删除空行.png

删除间隔行

img_bf32ff6fe8eb5b4c3e13642b3f4a85de.png
删除间隔行1.png

设置数值如下图所示,然后点击确定
img_03ad25671cea1654bd119b940d496c5e.png
删除间隔行2.png

img_e2fec2d922a0f5d6f9bc247d5bc2b35a.png
成功删除间隔行.png

关闭并上载

img_64a20c8f61a311ebe9e72c5bd81ceb85.png
关闭并上载1.png

设置数值如下图所示,点击下图中的加载
img_a3b44a7599d341074c7f6f3cc37999c2.png
关闭并上载2.png

img_989ee4a365c1dd85d64c4503d4dfdd1e.png
成功关闭并上载.png

填充行

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

img_c45f4c39a99abaabcc3aa58adf43291b.png
加载到查询编辑器1.png

加载到PowerQuery中如下图所示
img_2111c9b16b091c41da670e46d1fb177c.png
加载到查询编辑器2.png

img_cbfc5540f7d4c0d3eb6a4bf4e3fd43b8.png
将第一行作为标题.png

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

img_03ad25671cea1654bd119b940d496c5e.png
删除间隔行1.png

img_a6d9088778b13f0e3af5c87a7298c87d.png
删除间隔行2.png

img_e7767fb78f30cf24dd0582fbb31d3a17.png
成功删除最后一行.png

img_dc33aed1ba2175880e0bba23bca039c1.png
填充按钮位置.png

img_8ed5f87ae18bd378089ca4ea675b8303.png
成功填充.png

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

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

3.数据格式的转换

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

img_ab23a94701584607580fe55f207d0014.png
打开文件图示.png

img_21d85f785e736c667741d54f483e39f4.png
加载数据至PowerQuery中.png

img_a8662defd3a45d9d7f24a84e8eb2c21a.png
修整1.png

img_047effa03bbbd3a33bf02e27e788f211.png
成功修整.png

img_d45f324b76169c1a708920cc08e062c3.png
拆分列1.png

img_547f3c61aabb4ab41fd9e73ee4fb0f08.png
拆分列2.png

img_b61566bee3a5305c9792184986e1e77d.png
成功拆分列.png

img_eb0779dafbfc10d073b6f7a918d9a965.png
修整1.png

img_18d906096366c92396a5ac925f14f93f.png
成功修整.png

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

img_a0147c3c481d97eb4ec7fd442f5200c3.png
合并列2.png

img_8de20fd1c127840799faaaa588004fda.png
成功合并.png

img_6e86b427ae4d5299527bebc0000b3ac5.png
拆分列1.png

img_59d20f8f54fe8aec6d2d151d1d4f40fb.png
拆分列2.png

img_0bd86a54749729e9d843c15bf8e5fa91.png
成功拆分.png

img_3b5e03e3a7fc079cb5307a66a1b985a9.png
修整.png

img_eb736ff081f70a389b407eb76b5eb5bc.png
成功修整.png

img_a93c65ed21f732d6bdf2006db7053404.png
合并列1.png

img_1237d0c5166f73239daa0f47f9c2a88d.png
合并列2.png

img_ce62d3f7577a87281eda54bcb342f3a3.png
成功合并.png

img_b5d0fcb95888439413cda74e0f4fc71e.png
首字母大写按钮位置.png

img_586b965061e4d17508cd8a07732356a3.png
成功设置首字母大写.png

img_20d7857367f1bdd088eca6b77d922528.png
转成文本1.png

img_5dacdfed9d645b66dd84853d7dfaae5e.png
替换1.png

img_f2741e744192b3d588ee80a4be67b2be.png
替换2.png

img_fef50b5b00f00cbdba569831853ea747.png
成功替换.png

可以将下图与结果表进行对照
img_4acae8f5065d0ae9476ec87cf7dc8d1e.png
成功加载.png

4.数据的拆分合并提取

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

img_2c497692e3f90407d8eab20a71b453c8.png
打开文件图示.png

img_fc6aedf091fe28c90e37ae989c199efd.png
加载至查询编辑器.png

img_edcfcb32b1b5bd528213e556d009fe2f.png
修改数据类型为文本.png

img_f9c7eae26b88d27c04d1ac13d5accea6.png
成功修改数据类型.png

img_c499b7f5d14c9fe299dc35212a858023.png
添加重复列.png

img_f5889812fdcd790780214b58d259754c.png
按字符数拆分列1.png

img_9a2b96c0468a7219b59861507c6a892b.png
按字符数拆分列2.png

img_fed5c9cd24489c638f120ca58a455249.png
列重命名.png

img_497838a784f813892612570bf89010bf.png
成功列重命名.png

img_102a1055816be68be0806fd14e2dae86.png
提取1.png

img_d440cd631aefc3187118e177bf8d8c15.png
提取2.png

选定新产生的一列转换数据类型为整数
img_d61697606460ad0d3cbe21cf06296f7e.png
转化1.png

img_a2d49f61cbd9af05e7910e6b35c75853.png
转化2.png

img_a2d49f61cbd9af05e7910e6b35c75853.png
转化3.png

img_03df54e8e3ffff90ba4fac2078a5fd08.png
转换4.png

img_0dac68a9650ec65ee2bbb089d083a868.png
转换5.png

img_637a156034c8001c66d52998ef770992.png
转换6.png

img_f919ebf5d562165cbf9914b33c20e3fb.png
转换7.png

img_81daed2fa92a69bdace67ed693cab979.png
成功转换.png

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

5.删除重复项

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

img_bc01fe16ca075a248a953102fce8834f.png
删除重复项1.png

img_4bfcdf48272912381a0d2f3dd16cc9fb.png
加载数据到PowerQuery中.png

客户首次购买分析

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

img_9b27764c4f0aff820192bc8da5348cb8.png
下单日期升序排序.png

选定客户名称这一列,进行删除重复项
img_c5f9d009097ab36f6d150f40584d46b2.png
对客户名称删除重复项.png

img_c29adcfd0bf6a8b4941b65ea1e3ca506.png
首次购买分析结果.png

客户最大订单分析

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

img_652d4baacd8365f125bcc7f432e90404.png
金额降序排序.png

选定客户名称这一列,进行删除重复项
img_f433ab84a60c2e947d8e3ee36bb55d0b.png
image.png

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

多次购买客户分析

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

img_e9d2d67edd24089d3462c499600b221b.png
保留重复项按钮位置.png

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

6.删除错误

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

img_6738e18f6b92f028dd72254d3a834d42.png
打开文件图示.png

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

选定日期这一列,将数据类型改为整数
img_ec5984c501f6f5f9d5b3f50e25a0ad5e.png
image.png

img_1c41b67d1427a379a066e3bd71ea2428.png
删除错误行.png

img_cb4f299ec9143652fcc52343dca6540a.png
成功删除.png

选择导航栏开始中的关闭并上载至,并按照下图所示设置。
img_a09ae52d70ccb76b760729636105c7d1.png
关闭并上载至原有表.png

img_e5c4dc56af73bbf93ae054d7809191a8.png
成功删除错误行.png

7.转置和反转

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

img_9c1ff8ad472c06efdbe9cc44861bd0e1.png
打开文件图示.png

img_c77fe87568a822198e9c7c54f4857e10.png
加载数据到PowerQuery中.png

img_9d6efc191a594323b3c562cd1079a7e7.png
成功加载结果.png

img_549f61c38d9bd64b3f429cf07e6a5c65.png
将标题作为第一行.png

img_dfa60df12b2c2bfc5856fb0be9632c2a.png
转置按钮位置.png

img_49c3da1a5c8d8d504caeceb30af6279a.png
转置后结果.png

img_53f3bedf0231e5974165fde4c2a68df7.png
将第一行作为标题.png

img_6c1718a3bf645e0e9842b22024ba54ee.png
取消自动更改类型.png

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

img_2a5e6505814a3d6d2cbd3ec0c89cb60d.png
上载设置.png

img_adbe7d9c7b80f221e88ce32d2104c744.png
转置结果.png

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

8.透视和逆透视

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

img_ce94920942cd4d00be8ffe7699ee4506.png
打开文件图示.png

不要选中第一列,选中后面的列,然后点击下图所示的逆透视列
img_46a4193783cdd688eb47100a8094980f.png
逆透视1.png

img_aff708fa1e20448b8e5e791f5558c6cc.png
成功逆透视结果.png

选择关闭并上载至,在窗口中设置值如下图所示。
img_25b834a40d47f4ff01f63790514e7635.png
上载1.png

img_7f597908b2812e74e8a9b98a9dc34b34.png
上载2.png

出现上图所示2月3月的错误,要设置___单元格格式。
img_579e311ae6befea6ba5dc4646e6fcf6c.png
上载3.png

img_455125097ee527b240afc2ed5dd7d7b2.png
成功上载结果.png

透视——不要聚合

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

img_b9fc04bcc5e9a4cb966d184424f977db.png
透视1.png

值列是否完成销售额,点开高级选项,聚合值函数选择不要聚合,最后点击确定
img_1eaa8a04e77c72389e5d0768e7b9d089.png
透视2.png

img_12dc492293f3f713c7ffdf55ded430ec.png
成功透视结果.png

img_c7ec5c1d2f70f2c4f87dd294fc7dd134.png
加载至原有表.png

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

9.分组依据

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

img_34f89cffb3ce1b24c859e7dc4ac5a539.png
打开文件图示.png

img_f52aa797a385edd01fff23c3ef3a536e.png
分组依据1.png

img_ad92a9ebd8899fa40c376b277ac7dc58.png
分组依据2.png

img_29936944d3bc5a8c2bc538fee9639f20.png
成功分组结果.png

10.添加列

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

img_7baecb7e0874c7aae76081381dfca9e7.png
打开文件图示.png

img_fb929f6dcf23953dfdc3e214223c891e.png
进行分组操作.png

img_736e0a21e0d8aaecd7ede5071a40e0cd.png
逆序排序.png

img_ccfb4d67e80311c09c3d9844a253a9ef.png
添加索引列.png

img_1a287d073b1497aeb0fea0e8a05f29e4.png
自定义列按钮位置.png

img_ad28ed14a76cbe8a0780f7fd458ea66a.png
自定义列设置.png

img_6a278eb290f720dd780f23afa0249786.png
成功添加自定义列.png

网友评论

登录后评论
0/500
评论
潇洒坤
+ 关注