所在分类:  Amazon 所属圈子: Amazon Amazon销售和运营

如何使用Power BI建立属于自己的数据分析图表库-Power Query&Power BI

发帖20次 被置顶11次 被推荐9次 质量分2星 回帖互动219次 历史交流热度9.99% 历史交流深度0%
这个帖子的初衷是因为之前我用Excel的数据透视图做了一个出单时间的图表看出单时间高峰期,发了个贴有老哥在下面说如果我会power query的话就不会这么麻烦了。当时我还以为这老哥脾气咋这么冲,那我不是不会吗。结果现在一用,Excel是啥玩意,power query真香!哈哈哈

--------------------------------分割线--------------------------------
 
下面先展示一下做完了之后图表是啥样子的,做完了其实很像ERP
1. 首先是筛选区,这里我选择了年度,季度,月度,SKU这些维度。

https://assert.wearesellers.com/questions/20220511/41dbe0c4e71e9ff6de0a0714db9f6d0d.jpg
 
2. 第二部分就是图表区了,这里你可以选择任何你想添加的数据,我添加了销量,销售额,流量还有转化率这些数据

https://assert.wearesellers.com/questions/20220511/927eb24d196abfe024869217ad38ecb7.jpg
 
我还做了一个出单地区分布地图,州的饼图,出单时间高峰期的折线图

https://assert.wearesellers.com/questions/20220511/aa9491b2b16f1c0fc030ef6a2c30f4c4.jpg
 
https://assert.wearesellers.com/questions/20220513/92e3a4481985fa15f4e353fef3a7e022.jpg  
大概的就是这样,然后下面就讲讲我是这么做这个表的。
 
--------------------------------分割线--------------------------------
 
首先需要有一台Windows电脑,Mac电脑是不行的,然后去微软官方下一个power BI,好像是需要什么账号的,这个某宝买一个就行了。然后还有一点,如果你下载文件是用的Mac电脑,然后用Windows电脑处理数据的话,你下过来的文件Power BI会打不开,因为访达文件夹里有一个文件不是表格形式的且被系统隐藏了,这时候再在Windows电脑上新建一个文件夹,然后把原来的文件夹里的东西都复制过来就行了,那个被隐藏的文件就不见了。
 
1. 看你需要分析哪些数据,就去后台下载对应的表格,我这里要分析流量,销量,销售额等随日期的变化,那就去后台下这个,有这个的数据就是只有业务报告里的了
下载路径;数据报告-业务报告-销售页面销售和流量(按子商品)
tips: 因为后台应该是没有每日的,又有流量转化率销量等数据的,且还按日按ASIN的数据。所以这里也是最麻烦的一步,需要下每日的数据,我下了11月份-4月份的数据,共计下载了180多次表格。
下载了之后放在一个文件夹里,把文件名改成日期,方便之后用power query清洗数据的时候可以分列出一列日期出来,可参考如下截图
https://assert.wearesellers.com/questions/20220511/9f8d780804796cd49a46a7a843c1a6ec.jpg
 
 
2. 由于我想分析出单时间,出单前十地区,出单的地图,是否使用优惠券等,那我就还要下一个有这些数据的表格
后台是有这个表的,下载路径:数据报告--库存销售报告--销量-所有订单--选择日期(这个日期只能选择30天的,也就是一个月一个月的)--下载

下完了之后新建一个文件夹放进去,跟上面那个业务报告的文件要分开,大概就是这样↓

https://assert.wearesellers.com/questions/20220511/435389034756c529d22ef67f9b384189.jpg
为了好区分是哪个月的你也可以命名成2021-11这样子
 
3. 下面就是使用power query处理清洗数据了!
打开你的好帮手Power BI Desk Top,左上角获取数据--更多--从文件夹--选择你之前存的文件夹--“业务报告”

https://assert.wearesellers.com/questions/20220511/a84f08245e4d18069d06e091c9867a08.jpg
 
4. 这个时候就打开了一个预览页面,点下面第一个组合的小三角形,选择组合并并转换数据--确定

https://assert.wearesellers.com/questions/20220511/8740185c87040da62819e54141dccbe1.jpg
 
5. 第五步处理数据,这里就介绍几个操作,不一个个介绍怎么处理的了,按照自己的思路处理一下就好了

5.1 这样生产预览的表格中第一列问你这个文档的文件名,比如我的是“BusinessReport-2021-11-01.csv”,这个是我们最开始命名的那个表格名,这里就对应的数据日期。
选中这一列-主页--拆分列--按分隔符--看一下分隔符是不是“-”,拆分位置选最左侧的分隔符--确定
分列完了就是下面这样一行变两行

https://assert.wearesellers.com/questions/20220511/442e0327468fb6bfbdc751760b25cdc3.jpg
 
5.2 但是source***2这个还是不能改成日期,要再分列一次,把csv的文件格式去掉,这次的分隔符“.”然后确定

然后就变成了下面这样,注意单元格左上角的那个表格一样的标志,这个是这一列的数据格式,表就是日期的格式,可以点开看一下。

https://assert.wearesellers.com/questions/20220511/cf02e74f647709f8a11633219f82b896.jpg
 
5.3 处理完了之后,双击单元格Source***重命名为日期,这一列就处理完了,因为要处理的数据比较多,那我就不一个个说了,不然帖子会很长很长,这里就讲几个简单的操作,分列就是以上的步骤,回撤ctrl+Z在这里面不行,要回上一步的话就直接在右侧的步骤里面点那个X

https://assert.wearesellers.com/questions/20220511/fe10e45aa5f385a2917d43a0097a4b64.jpg
 
 
5.4 替换;跟Excel一样,这里以Asin为例,选择子Asin这一列,把他替换成SKU。单击Asin单元格--在下面的文本框复制Asin--选中列-点击主页--找到转换--替换值,查找值选刚刚复制的,替换为填你的SKU或者产品名字,比如B****替换为充电器1号
 
5.5 删除;不需要的列就删除。比如商品名称,这个制图是不需要的,也太长了,就删掉他。路径是主页--管理列--删除列,或者直接右键,然后点击删除。
 
5.6 这个表要处理的话有一个地方要注意一下,在“已订购的商品销售额”这里,这一列的数据不是数值的格式,他左上角是显示的ABC,这个的话要改一下格式,不改待会做折线图是会报错的。
也是先分列,然后按照“$”分列--确定

https://assert.wearesellers.com/questions/20220511/d4dfd2b4748eeb850cf73d03fdf07d0d.jpg
 
 
6. 以上就差不多是业务报告简单处理的所有步骤了,很简单吧!
处理完了之后,点击左上角关闭并应用,上载至Power BI

https://assert.wearesellers.com/questions/20220511/ed32cfb2876cf06d434a83ab116d6511.jpg
 
7. “因为我要分析出单地区,城市,和时间。那我就需要再下另一个表,有这个数据的路径是”
下载路径:数据报告--库存销售报告--销量-所有订单--选择日期(这个日期只能选择30天的,也就是一个月一个月的)--下载
 
这个之前下过了,所以直接用power query清洗数据就好了
所以就是之前的步骤再来一遍,点击主页--获取数据--更多--从文件夹--选择你放出单时间,地区数据的文件夹--合并并转化--如果没显示预览不可以点确定就是没选择表格,选中左边的sheet 1然后单击确定
 
然后这里清洗数据就只讲讲不一样的东西了啊,就也不一步步都说了。
 
7.1 订单号不需要,就删除,第一列是之前命名文件的名字,也是月份,想留着就可以留着不留也没事。
先看第一个购买时间purchase date这一列,这个purchase date就是客户下单的时间。

注意:这个表数据列转化了之后格式变了,其实也可以看到这里的数据跟我们原表格的数据不一样,原来下下来的中间带了一个T,然后转换完了之后时间都变了。原来的时间这里,跟后台的也不一样,不过不要慌,问题不大。原理是 原来下来的表格中的时间是UTC时间,而后台是PDT或者PST时间,然后Power BI会帮你转换成现在的时间,现在的这个是北京时间~ 
 
原来的我:哦!我说我还以为我哪里搞错了,看了半个小时还算好几遍!

https://assert.wearesellers.com/questions/20220511/3e92f454217e3e90e5022ae678641e4b.jpg https://assert.wearesellers.com/questions/20220511/4e0cae4f5193f660ab5d12a083acaf16.jpg
 
7.2 为了保证数据的可靠性,这里我们需要筛选一下,order status这里,把取消的订单筛选掉,只留下shipped的就好了。然后用完这个“工具人”之后就把这列删掉,因为用不上他。

tips: 如果要算取消的和发货的比例,做一个饼图,那就不要筛选也不要删除了啊!
 
7.3 SKU这一列要处理的话也是替换就行,替换成你的产品名字。
 
7.4 因为我要汇总看企业订单和下单的州,用来做饼图,那饼图需要的就是除了数据(比如州,城市)还需要一个指(数量),所以就要再创建一个辅助列用来汇总计算。
 
画外音:那这里就有人会问了,原表中不是有一列quantity吗?为啥不用这一列呢?(没人想到也没关系哈哈哈哈,我当时想的)
那我就回答一下!这里呢,是因为quantity中是订单中客户购买的数量,有些人买一个,有些人买多个的。而我们需要分析的应该把客户购买这个动作作为分析对象,也就是一个订单只计算一次。所以如果用订单中购买的数量作为值我认为是不合理的。
 
所以回来,创建辅助列这个,我属实是没找到怎么在这个表格中插入我想要的那个数据列,所以我就用了个绕弯的办法。
 
先选中purchase-order-number这一列--添加列-条件列--新建名为数量--列选择purchase-order-number这一列--运算符选择等于--值复制单元格中的null,在输出这里填1,下面的Else也填1
https://assert.wearesellers.com/questions/20220513/3b16d7a06518271d5d1371623b22cadd.jpg  

这样根据if公式的原理,那如果这一列中值为null的就会输出1,如果不是,也会输出1。这样就创建成功辅助列了
(这一块大佬们应该会怎么添加辅助列,我这是个笨办法)
 
然后新的列就会出来,不过注意这一列左上角的格式为ABC 123这种,这个是无法参与计算的,所以要把它改成整数。单击这个ABC 123改成整数就行了。完成之后大概就是这样

https://assert.wearesellers.com/questions/20220511/c9745eaf58eb93602c58ee7b2fbd2f1e.jpg
 
其他的步骤的话,像替换,拆分,删除啥的就上面都有了,重复那些步骤处理数据。
 
8. 到这的话,恭喜各位,已经完成第一大部分了!!汇总所有表格!!把几百个表汇总到一个表上来了,是不是对自己的能力很自豪!是不是觉得自己在原来的6k上又可以再多要2K了!!不用怀疑,是的!你真的很棒!!
 
完成这个的话,然后再来第二大步骤,用**语言插入表格(我不知道这个是啥语言,反正大概就是一个编程语言??)
插入一个时间日期表,然后把所有表格的关系用日期关联起来。
 
选择左边栏(依次为报表,数据,模型),选择第二个“报表”(这里没找到可以看步骤9,第2个图标)-主页--计算--新建表---把下面的复制进去--回车(这里的年份是从2021到2025的,所以如果你需要其他的日期或者你算得是2021前面的数据,那么就把下面的2021改成2019或者你要的那个年份)

日期表 =
ADDCOLUMNS (
CALENDAR (DATE(2021,1,1), DATE(2025,12,31)),
"年度", YEAR ( [Date] ),
"季度", "Q" & FORMAT ( [Date], "Q" ),
"月份", FORMAT ( [Date], "MM" ),
"日",FORMAT ( [Date], "DD" ),
"年度季度", FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" ),
"年度月份", FORMAT ( [Date], "YYYY/MM" ),
"星期几", WEEKDAY ( [Date],2 )
)
 
(这里就不要复制了啊,复制上面的)这时候右边的字段,就能看到你上载成功的三个表格
https://assert.wearesellers.com/questions/20220511/adfe36bf945ca20935f17f19b9bf2849.jpg
 
9. 基本的三个表格就完成了,完成了之后需要通过一个数据把所有的表格关联起来,这里我选择的日期

找到Power BI的左侧,这一列有三个图标,依次为可视化的图表(也就是你待会做图的地方),第二个为你所选取表格的预览,第三个就是表格关系了!也就是我们待会关联要用的到地方。

https://assert.wearesellers.com/questions/20220511/3bd97cbddbb165b4f6ff65ff6bc83ba1.jpg
 
选择图标3,创建表格的关系,把日期表中的date拖至业务报告中的日期--再把日期中的date拖至出单时间中的购买日期。这样就创建好关系了
 
10. 到这一步其实前面的步骤就都做完了,剩下就只剩下插入切片器和表格了--第三大步

点击图标1,可视化这里,看power BI右侧,有3个模块。分别是对1)筛选器的处理,比如隐藏不需要的筛选器 2)选择可视化图表和处理可视化的图表的设置,3)字段就是刚刚的表了(如果需要返回处理表格,重新回到power query里,就只要右击表格选择编辑查询,然后就可以重新再次处理数据了)

https://assert.wearesellers.com/questions/20220511/02924926286de0ad8c5c879aed0736fe.jpg
  
11. 以下呢就讲讲我用到的一些简单图表,第一个插入切片器。
切片器中就是这个小小的东西

https://assert.wearesellers.com/questions/20220511/bd2b700467824f86dd28e77f0bfffffb.jpg
 
11.1 这里以年度为例,选中“日期表”中的年度,然后就会出来一个带“滑动变阻器”的年份选取器

https://assert.wearesellers.com/questions/20220511/e81800f5f0cddbd4d0c4733641c1358d.jpg
 
11.2 这里切片器中的2025不是我想要的年份,那我想隐藏或者不选这个,在哪里设置呢?
这个时候就要用到我们的筛选器了,点击筛选器中第一个,年度旁边的下拉条--筛选类型选择基本筛选--把2025和不想要的年份去掉

https://assert.wearesellers.com/questions/20220511/190131ce8719709ebfcd000a8800e0ba.jpg
 
这样原来的切片器中就没有2023和之后的年份了,或者这里可以在原有的日期表的那个代码里面改,就也不会有了
以这个为例,像表头,切片器中的文字设置,或者其他的图表中的文字设置,就在可视化里面设置(先选中你要修改的那个图表,然后点击可视化视觉对象格式修改)这里就留给大家慢慢琢磨了,手打字打麻了,这一篇至少得有一万字了吧!!
 
12. 剩下的就是对你进行的数据,选择图,然后拉入数据,然后排版,修改图中的格式内容了,改完了就是我最开始展示的那种图了。
 
-----------------分割线-----------------
 
结语:这里就是简单的引导一下,如果有兴趣的话可以试试,做完了感觉信心倍增,怎么我才这么点工资哈哈哈!!
 
因为打字太多了,我也是略微的重做了一次表,然后把步骤贴在了帖子中,所以如果中间有任何错误或者做到哪里卡住了,麻烦大家在帖子下面评论,我会修改重新编辑。有问题的话也可以在下面评论,然后如果有问题我没及时看见回复也麻烦各位大佬知友帮忙解答一下。祝各位来年108个孩子~提前祝各位新年快乐!
已邀请:
在一个领星,G+作为广告爸爸的论坛里
我们居然在讨论如何用 脚本 RPA工具  POWEBI 等工具组合来替代他们的方案。。。。
 
亦可赛艇

要回复问题请先登录注册

x 点击咨询