使用Excel连接Power BI模型

在目前可选的数据展现方案中,Power BI效果更加酷炫,逼格更高,而Excel则更加灵活且普及度更高,很难取舍,所以很多人都会选择两者各做一个版本。
但多版本必然会带来一些麻烦,比如要对模型作一次改动,需要两边都改一下,而且每天刷新数据都要刷新两次。
如果你经常在Excel中使用Power Pivot,会发现其比较不稳定,经常容易崩溃甚至出现打不开的情况。且据说PBID的SSAS版本比PP的更高,计算性能更强。
所以如果你有这种双版本需求,建议把建模、计算什么的都交给PBID,而Excel只作为查询端直接抽取模型中的数据。

从Excel获取Power BI模型中的数据有以下两种方式:

1、Microsoft Power BI Publisher for Excel插件


在PBID中点击发布,下载安装插件,点击连接到数据,可直接在透视表中获取到模型中的字段及度量值。
使用此方法必须保持联网状态,且可能因为网络问题产生延迟。

2、从SSAS创建连接

点击数据-自其它来源-来自Analysis Services。

要求填写服务器名称,此时你的电脑就是一台本地服务器,地址就是localhost:端口号,而这个端口号我们借助Dax Studio可以在右下角看到。

直接把图中的localhost:5950填入到服务器名称中,点击下一步-完成,即可直接创建透视表并获取到模型中的所有字段及度量值。
但注意使用此方法时,PBID必须保持打开状态,否则会出现连接错误,且每次关闭重新打开后端口号会变,需要手动修改端口号。

看到这里好像都和我们的Power Query没什么关系,但是如果你没有安装或者不想装Dax Studio时要如何获取端口号?
每当我们打开一个PBID窗口,系统会自动在C:\Users\你的用户名\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces目录下创建一个文件夹(不同系统可能路径略有不同)。
而文件夹的Data目录下有个msmdsrv.port.txt文件,里面放的就是端口号了。

所以我们可以使用Power Query来获取这个txt文件里的内容从而获取到端口号,代码如下:

let
    folder = Folder.Files("C:\Users\请改成你自己的用户名\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces"),
    port = "localhost:"&Text.Clean(Text.FromBinary(Table.SelectRows(folder, each ([Extension] = ".txt")){0}[Content]))
in
    port


代码中筛选*.txt后只选择了第一行,所以如果同时打开多个*.pbix文件只取最后打开的一个端口号。

当然PQ的作用当然不仅仅只是用来获取端口号,在M语言里有专门的cube类函数可以对模型中的多维数据集进行处理,再举个常见的应用:
上面的方法都是获取模型中的字段及度量值来创建透视表,而如果要获取数据表,或者说是把PBID里的源数据导入到Excel中,可以接着上面的代码往下写:

let
    folder = Folder.Files("C:\Users\请改成你自己的用户名\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces"),
    port = "localhost:"&Text.Clean(Text.FromBinary(Table.SelectRows(folder, each ([Extension] = ".txt")){0}[Content])),
    GUID = AnalysisServices.Databases(port){0}[Name],
    query = "evaluate '表名'",
    result = AnalysisServices.Database(port,GUID,[Query=query])
in
    result

其中第四行的query,里面可以是MDX或DAX查询语句,案例中我只写了个最简单的表名,表示获取整张表的数据,可根据自己需求修改。
如果修改query的查询语句,还可以获取到比如DMV等很多其他数据,同时可以使用cube类函数对多维数据集转换,以后慢慢研究吧。

7 Replies to “使用Excel连接Power BI模型”

  1. “但注意使用此方法时,PBID必须保持打开状态,否则会出现连接错误,且每次关闭重新打开后端口号会变,需要手动修改端口号。” 请问老师,每次更新要怎么手动修改端口号呢? 我试着改了计算机中“我的数据源”中的链接文件的端口号,但是没用,刷新excel的数据透视表后报错“出现以下系统错误:由于目标计算机积极拒绝,无法连接”。

      1. 就是每次都要重新创建一个链接喽?然后将原先建立的一些透视图表指向这个新的链接?

      2. 经过几次尝试,已经解决:在原有连接中进行端口号更新就行,同时跟新连接命令中的端口号。

    1. 当前无方案,你可以尝试突破下。如果实现,就能作为迷你版的Power BI Report Server,应用非常广泛!

  2. Excel催化剂这个插件,excel可以连接powerbi的数据和度量值。可以查询dax参数。还可以导出字典,了解数据模型里的结构。建议试试看。

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注