【原】Excel数据自由：得到一个干净的超级表 - 用Power Query访问数据模型

ExcelEasy 2023-08-17 发布于北京

展开全文

继续介绍Excel数据自由。

上次说到了可以用DAX直接将PBI数据模型中的数据返回到Excel超级表中（见这篇文章），但是这么做有一个问题：

返回的超级表中，标题行总是有方括号。

要去掉这个方括号，需要使用Power Query连接数据模型。

Power Query连接数据模型

首先，在数据选项卡中，点击获取数据，

选择其他源，空白查询，

进入Power Query编辑器，点击主页选项卡中的高级编辑器，

将其中的代码修改如下：

let    源 = OleDb.Query(              "Provider=MSOLAP.5;Data Source=localhost:50007;               Initial Catalog=ed183485-1c77-49a5-8aeb-0e64d701be75" ,              "EVALUATEvar regionCountry = filter(VALUES(Geography[RegionCountryName]),Geography[RegionCountryName]<> BLANK())var statByRegionTbl =   SELECTCOLUMNS(    regionCountry,    ""地区或国家"",    [RegionCountryName],    ""产品数量"",    CALCULATE(DISTINCTCOUNT(ProductSubcategory[ProductSubcategory])),    ""销量"",    CALCULATE(SUM(Sales[SalesQuantity])),    ""销售额"",    CALCULATE(SUM(Sales[SalesAmount]))  )return  statByRegionTbl"    )in    源

如下图：

其中1为连接的端口号，2为连接的数据库名称，这二者如何得到，请参见我们以前发的这篇文章，3是我们要使用的DAX查询，参见上次发的这篇文章。

点击完成，

修改列名，

将每个列名都去掉[]，

返回Excel中，

这个表就是一个干净的结果了，具有我们上次介绍的相同的优点，同时标题行没有[]。而且也是自动化的

当然，还有其他问题，我们下面解决。

如何自动化取得每次的端口号和数据库名称

要这么做，要求Power BI Desktop已经打开。

我们知道，每次重新打开PBI Desktop，端口号和数据库都会改变。那么应该如何自动化这个过程，将新的端口号和数据库名称传到Power Query中呢？

这里就要用到我们上次介绍的这个模板了。

双击这个模板，打开一个新的Excel文件，将其另存为xlsm文件。

然后双击这个按钮，

得到当前打开的PBI数据模型的连接，

按照前面一节介绍的方法创建查询，得到查询结果，

将代码修改为：

点击完成，会出现如下的提示，

点击继续，

勾选这个选项，点击保存。

得到结果表。返回Excel即可。

这个表就将端口和数据库名传递到Power Query中。

代码如下：

let  port = Excel.CurrentWorkbook(){[Name="Port"]}[Content]{0}[Column1],  db=Excel.CurrentWorkbook(){[Name="DB"]}[Content]{0}[Column1],  源 = OleDb.Query(              "Provider=MSOLAP.5;Data Source=localhost:"& port &";               Initial Catalog=" &db ,                  "EVALUATEvar regionCountry = filter(VALUES(Geography[RegionCountryName]),Geography[RegionCountryName]<> BLANK())var statByRegionTbl =   SELECTCOLUMNS(    regionCountry,    ""地区或国家"",    [RegionCountryName],    ""产品数量"",    CALCULATE(DISTINCTCOUNT(ProductSubcategory[ProductSubcategory])),    ""销量"",    CALCULATE(SUM(Sales[SalesQuantity])),    ""销售额"",    CALCULATE(SUM(Sales[SalesAmount]))  )return  statByRegionTbl"    ),    重命名的列 = Table.RenameColumns(源,{{"[地区或国家]", "地区或国家"}, {"[产品数量]", "产品数量"}, {"[销量]", "销量"}, {"[销售额]", "销售额"}})in    重命名的列