使用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类函数对多维数据集转换，以后慢慢研究吧。