利用Excel连接Power BI，实现PPT报告自动输出

讲真话被头条封号

​文/HALI 就职于汽车行业战略部门专注汽车市场信息情报收集和分析
因为工作需要，每月周期性的更新数据和撰写PPT 报告成为繁重的劳动。结果是很多时间花费在数据处理上，真正的分析工作，往往只能草草收场。不能坐以待毙，就要想想有没有更好的办法。
最早，我想到并采取的是Excel图表到PPT的链接。后来随着各种BI工具的普及，尝试学习并使用了Power BI。它实在强大，据说使用了SQL Server的数据引擎Vertipaq，数据处理速度飞一样。另外，可以处理的数据可以做到海量，加上是专业数据库，比较之下，Excel秒成渣。
于是尝试将工作平台转移到PowerBI。但是，最终的测试发现，微软不支持用户能自动与PPT嵌入图表（不知道为什么，但是看来微软想直接在PowerBI内部制作类似报告，与PPT不同，PowerBI的报告将更倾向于数据）。
虽然有第三方工具或插件可以嵌入PowerBI图表到PPT（如何在PPT中动态交互PowerBI报告？)，但是需要联网等等，很麻烦。各位做报告的同学一定有经验，你绝对不希望PPT报告有任何技术问题，不然董事会上各位大佬干瞪眼等着刷新数据，呵呵。后果惨过没有报告。这就是报告一族面对的政治生态。简单说就是绝不允许任何问题。
平心而论，目前PowerBI的报告能力，颜色和文字能力，图表的定制能力，目前不及Excel和PPT。给分析人员看是非常够用了，快捷又方便。但是给管理层做报告，呵呵。领导看到PowerBI的报告，意见是质量一般。我也是做过几次才发现，PowerBI报告不能用其他字体，不能调整单个数据label，等等。有其他方法，太费劲。
这就尴尬了，PowerBI白学了。这么好的数据处理能力真的可惜了。曾经有一段时间尝试学习Excel PowerPivot，但是发现差异挺大，而且不能使用PowerBI的便捷图表方式和分组什么的真的是损失，貌似不能兼得了，怎么办呢？
搜google吧，功夫不负有心人，还真让我找到了解决方法-OLAP（OnlineAnalytical Processing）。应采总的邀约，把相关的内容汇总一下，在这里分享给大家。希望对需要经常更新PPT报告的人有帮助。

传统方式：Excel 数据图表和PPT的链接及自动数据更新
我的工作涉及数据分析图表和定期报告（PPT）。之前采用的方式是Excel维护数据，然后使用多个pivot table数据支持，制作数据图表。然后Excel内部的图表用黏贴链接的方式，黏贴进PPT里面。每个月数据更新时，把新增数据手工黏贴到数据源表，然后refresh all pivot table。然后打开PPT，提示是否更新数据，点yes。在无数次的屏幕抽动（抽筋一样闪动）之后，结束了图表更新。
这样做的好处是以透视表的方式固定了每月固定的工作流程，一定程度上实现了图标数据更新的自动化，节省大量时间。
但如果可以享受PowerBI的高效，又可以享受Excel和PPT的强大定制功能，还可以固定下来所有的工作步骤，然后一键刷新是不是就完美了？
PowerBI实际上可以理解为一个小规模的SQL Server，精简了很多东东，但是SSAS服务保留了。如果你运行PowerBI，通过任务管理器，你可以看到SSAS的服务一起运行了，SSAS服务就是留给兼容的外部程序访问数据库的，很幸运Excel里面提供了访问SSAS的组件。
这就让我们可以从PowerBI外部访问，并利用Excel的图表功能成为可能。
Excel链接PowerBI的方法有三种,下面一一介绍。

方法一：Excel手动连接PowerBI SSAS服务

打开pbix文件，然后打开DAX Studio（关于DAX Studio的使用请参考：DAX Studio：你迟早会用到的几个功能)，复制pbix文件的ssas服务端口：



然后，打开Excel，新建一个文件。点击data>from other sources>fromanalysis services：



把pbix的分析服务端口黏贴到里面：



然后下一步，下一步，结束。注意，这一步我们建立了一个链接文件，它的存放位置在这里：链接文件是以端口号为开头的，容易区分。



然后Excel会提示你打算把用什么方式调用外部ssas服务的数据。当然PivotTable Report了：



注意看下右侧的可选数据表和所有列，看看是不是都在了？



这里有个技巧。你会发现一些数字列不能被拖入value。怎么办呢？查阅了微软的官方文档，方法是在PowerBI里面给每个数字列做一个度量值。然后你会看到这些度量值（带西格玛∑符号）如下图：然后他们就可以被拖入value合并计算了。



然后根据需要做各种透视表吧。
这里建议大家再单独建立一个新的Excel文件，该文件专门用来制作最终的图表（管理层喜欢的那种各种定制图表，包含贵公司的各种文化和领导喜好）。
下一步是把OLAP文件里的数据链接黏贴到最终图表文件里面。（其实业务顺序可能是根据业务需要先做最终图表Excel文件，然后根据图表文件的数据需求再做OLAP pivot数据文件），最后把最终图表黏贴链接到PPT。
这里提醒一句，一定要在同时打开PowerBI和OLAP文件的情况下才能修改PowerBI文件里面数据表和列的名称。否则，OLAP文件找不到变更后的列和表名称。最好留一个专门做报告的PowerBI文件，不要随便修改。我做过实验，调整列的位置或增加列不会影响OLAP文件，所以可以随后增加分组等工作，不需要一开始都分好。
最后，还有个问题，每月数据更新。当pbix文档关闭并重新打开后数据端口会发生变化，怎么办呢？

用DAX studio查询新端口。然后打开一个新Excel尝试链接ssas端口。然后关闭Excel文件。下一步是关键，打开我们保留了很多透视表的OLAP Excel文档，选择pivot tools>connection properties：




选择definition选项卡，选择connection file，Browse然后选择新端口号开头的链接文件。



然后refreshall，屏幕抽动无数次，结束。
其他的更新步骤不多说了，Excel就是全部刷新，PPT在打开时更新数据就ok了。至于pbix文件的数据更新，请各位参考采总的星球文章，推荐同一文件夹里的新增更新方式。
因为Excel是调用PowerBI的服务，因此这些在Excel里面图表报告的数据调用，其实都是在PowerBI里面完成的，超级高效。

至此，我们即可以享受PowerBI的高效，又可以享受Excel和PPT的强大定制功能，还可以固定下来所有的工作步骤，以后一键刷新就好。当然有些图表还是需要适当调整样式。
以上是最经典的OLAP链接方式。但是，这种数据更新方式真的有点麻烦。有没有更快捷的方法呢？google告诉我有的。
方法二：利用宏文件自动连接PowerBI ssas服务
Google上还找到一个澳大利亚的大神写的一个专门的Excel VB宏，来实现一键自动链接PowerBI文件。也就是说你不再需要链接文件，不需要DAX Studio查询端口，不需要每次都建立链接文件，一键连好。这个哥们儿叫Matt Allington。你可以在「PowerBI星球」后台回复“Excel连接PowerBI”获取这个Excel宏文件。
我这里把VBA的代码放一下：
SubUpdateUserPath()   
'developed by Matt Allington from http://Exceleratorbi.com.au   user = Environ("LOCALAPPDATA")   UserPath = user & "\Microsoft\Power BIDesktop\AnalysisServicesWorkspaces"   Sheets("Connection").Range("B2") = UserPathEnd SubSubRefreshSSASConnection()' developed byMatt Allington from http://Exceleratorbi.com.auDim myTable AsListObjectUpdateUserPathRange("SSAS_Data").ListObject.QueryTable.RefreshBackgroundQuery:=FalsePort =Range("Port")Db =Range("DB")If Len(Port) =5 Then   With ActiveWorkbook.Connections("PowerBID").OLEDBConnection       .CommandText = Array("Model")       .CommandType = xlCmdCube       .Connection = Array( _       "OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;PersistSecurity Info=True;Initial Catalog=" & Db & ";Data " _       , _       "Source=localhost:" & Port & ";MDXCompatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Update IsolationLevel=2" _       )       .RefreshOnFileOpen = False       .SavePassword = False       .SourceConnectionFile = ""       .MaxDrillthroughRecords = 1000       .ServerCredentialsMethod = xlCredentialsMethodIntegrated       .AlwaysUseConnectionFile = False       .RetrieveInOfficeUILang = True   End With   With ActiveWorkbook.Connections("PowerBID")       .Name = "PowerBID"       .Description = ""   End With   ActiveWorkbook.Connections("PowerBID").Refresh
Else   MsgBox "You must have exactly 1 instance of Power BI Desktop open",vbCritical
End If
End Sub
大家注意黑体和下划线的部分可能需要调整。（第一个部分指定了PowerBI的SSAS端口文件位置，可能因为安装的位置不同而不同，只装一个PowerBI软件，且是一路yes的不需要调整。
第二个部分，最大打开drill数可以调到最大10,000）
除了上述两种OLAP链接方式，还有别的办法吗？在星球贴出本文后，一个迪拜的星友      分享了另外一个实现方法。
方法三：利用Power BI Publisher
Power BIPublisher是微软自己做的一个Excel插件。看来微软也觉得让各位数据分析员每次更新数据都去重新建立链接文件太费力了。因此写了一个插件方便大家。

据她说：“可以从Excel界面登陆连接PowerBI的报告和数据。然后加载成透视表，刷新就跟Excel刷新一样也可以设置每次打开自动刷新。我们财务系统的所有数据都推到了dataflow里这样就不用从ERP里下载了。”

我去下载了测试。发现使用该插件的前提是PowerBI必须是Pro账户。所以，不给钱，官方是不会给你这个方便的。不过购买Pro的同学可以下载该插件，享受这一便利。
<hr/> 本文至此结束。所有操作，亲测可靠，各位有需要的亲可以尝试。
答应了采总写一篇文章，拖了一段时间，今天交差，请各位斧正，如果各位有新的方法和心得，也请分享，希望对大家的工作有所帮助，谢谢。
-精彩推荐-

喜欢了别忘了点赞哦~

杨二

之前试过Excel连接powerbi，但是所有字段全部变成文本字段，只有新建的度量值能当做数字字段用，不懂答主有没有这个问题

新会陈皮茶业

看不懂

陈明颀

适合在PBI里建模，然后用Excel 来展示 比较方便灵活，毕竟有时bi报告不能展示所有分析纬度

一刻爱跳的心

今天试试看[握手]

余艳

power bi引入源的时候，会“智能”地自动变更字段类型，你将自动变更的这一步删除就行

房车老杨

目测作者戴姆勒员工，想认识

怡朵宸

请问你是怎么实现Excel获取VBA数据的？我现在在这个问题上卡住了

寂水无澜

与power query获取数据 在exce里面建模做超级透视的差别是什么？

叫我小石就好

看见vba头疼