【原】将Power Query查询和Excel分离，脱离Excel，自动从Power Query访问数据模型

ExcelEasy 2023-09-04 发布于北京

展开全文

今天介绍的还是从Power Query访问Power BI Desktop数据模型。今天这篇主要是两个目的：

主要介绍一种将Power Query从Excel分离地访问Power BI数据模型的方法。
顺便解决之前的各种从Excel连接Power BI Desktop数据模型的场景中，需要使用那个Excel模板的问题。

什么是将Power Query从Excel分离呢？就是在不打开Excel的情况下，从数据模型返回DAX查询结果。

当然，并不是不需要Excel了，查询结果还是要返回到Excel中。

这个问题的主要场景在于我们在Excel中做了一个Power Query查询，返回结果后，Excel文件就变大了。如果结果表太大，每次打开这个文件就会很慢。

能够将查询从Excel中分离出来呢？

Excel是支持这种方式的。不过因为每次都需要更新端口和数据库，所以需要额外的操作。

工作准备：分析

准备工作还是要在Excel中进行。

回忆一下我们之前用Excel直连Power BI Desktop数据模型的方法。（详细操作见这篇文章）。

通过Power Query查询获得端口号
通过Power Query查询获得数据库名称
在Power Query中新建一个空查询，并且在其中使用下面的代码

在模板文件中，我们可以使用查询返回数据库端口和名称，

然后我们修改上图中访问数据模型的查询，将端口号和数据库名称关联到Excel的超级表中。

现在，我们修改一下这个做法。

在上面的查询中将端口号和数据库名称都作为Power Query查询的一部分，形成一个大的查询。

实现

新建一个Excel文件。

创建空查询，

在Power Query编辑器中，点击高级编辑器，

在其中输入下面的代码：

let  Source = Folder.Files("C:\Users\Administrator\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces"),  //db    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "msmdsrv.port.txt")),    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Content"}),    #"Combined Binaries" = Binary.Combine(#"Removed Other Columns"[Content]),    #"Imported Text" = Table.FromColumns({Lines.FromBinary(#"Combined Binaries",null,null,1252)}),    #"Renamed Columns" = Table.RenameColumns(#"Imported Text",{{"Column1", "Port Number"}}),    #"Cleaned Text" = Table.TransformColumns(#"Renamed Columns",{},Text.Clean),    Custom1 = Source,    #"Filtered Rows1" = Table.SelectRows(Custom1, each Text.Contains([Name], ".db.")),    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows1",{"Name"}),    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns1","Name",Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, false),{"Name.1", "Name.2"}),    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Name.1", type text}, {"Name.2", type text}}),    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Name.2"}),    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Name.1", "Value"}}),    #"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Parameter", each "DB"),    #"DBTbl" = Table.ReorderColumns(#"Added Custom",{"Parameter", "Value"}),  //------------------
  //port    #"Filtered Rows 1" = Table.SelectRows(Source, each ([Name] = "msmdsrv.port.txt")),    #"Removed Other Columns 1" = Table.SelectColumns(#"Filtered Rows 1",{"Content"}),    #"Combined Binaries 1" = Binary.Combine(#"Removed Other Columns 1"[Content]),    #"Imported Text 1" = Table.FromColumns({Lines.FromBinary(#"Combined Binaries 1",null,null,1252)}),    #"Renamed Columns 1" = Table.RenameColumns(#"Imported Text 1",{{"Column1", "Value"}}),    #"Added Custom 1" = Table.AddColumn(#"Renamed Columns 1", "Parameter", each "Port"),    #"Reordered Columns 1" = Table.ReorderColumns(#"Added Custom 1",{"Parameter", "Value"}),    #"PortTbl" = Table.TransformColumns(#"Reordered Columns 1",{{"Value", Text.Clean}}),  //--------------------------------  port = Text.From(PortTbl[Value]{0}),  db=Text.From(DBTbl[Value]{0}),  源 = 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    重命名的列