TL;DR Another place to find your Power BI queries is in the data sources of the embedded tabular model. They are stored in a zip file which is encoded as a base64 string in the Mashup field of the connection string.
While we are waiting for the Microsoft team to release new features that would allow exporting Power Query queries outside Power BI ecosystem, we might explore all of the other possibilities available to us. So far, I’ve counted three ways of getting a hold of our precious M queries:
As I’ve been poking around this black box called Power BI, I have discovered another place where M queries are stored — the data source connection string of the embedded tabular model. The steps that I took to get there are:
- Find the port number on which the $Embedded$ Tabular model is running on
- Connect to that Tabular instance and get the Connection Strings of the Tabular Schema Data Sources
- Parse the Connection Strings for Mashup field
- Convert the base64 Mashup field into a binary file
- Unzip the binary file and display Formulas/Section1.m file
Here is me trying to do everything in one go via PowerShell.
When it comes to scripting, the power of the Shell is far greater than that of the Query, but in this case, all of the prerequisites are there to replicate the above steps in pure M.
Finding $Embedded$ Tabular port number
I’ve tried to solve this problem before via R scripts; however, as Mim pointed out to me, there is a more straightforward Power Query way of doing this. Because Power BI stores its auto-generated port number for the tabular model in a text file (msmdsrv.port.txt) under AnalysisServicesWorkspaces, the task is reduced to reading the contents of that file.
| ()=> | |
| let | |
| //Assume current user is the one with latest [Date accessed] folder in C:\Users | |
| CurrentUser = Table.FirstN( | |
| Table.Sort(Folder.Contents("C:\Users"), | |
| {{"Date accessed", Order.Descending}}), | |
| 1)[Name]{0}, | |
| //Read the contents of file msmdsrv.port.txt from subfolder of AnalysisServicesWorkspaces | |
| Port = Lines.FromBinary( | |
| Table.SelectRows( | |
| Folder.Files("C:\Users\"& | |
| CurrentUser& | |
| "\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces"), | |
| each ([Name] = "msmdsrv.port.txt"))[Content]{0},null,null,1200){0} | |
| in | |
| Port |
By the way, my approach uses a short circuit logic, assuming that the NTFS Access Date records for C:/Users are up to date, apparently they might take up to an hour to update. So if you find this script misbehaving have a look at Imke’s blog post for the original script.
Get Tabular Schema Data Sources
You can read about the latest undocumented Tabular DMVs from Meagan’s blog, or fire up SQL Server Profiler, check the boxes for End Command events, and script out the tabular model from SSMS. You’ll discover a plethora of DMV being displayed in the profiler’s trace. For example, DMV hiding all of the connection strings is TMSCHEMA_DATA_SOURCES.
| (port as text) => | |
| let | |
| //Get $Embedded$ database name | |
| catalog = AnalysisServices.Databases("localhost:"&port)[Name]{0}, | |
| //Run DMV query to get data source details | |
| dataSources = AnalysisServices.Database("localhost:"&port,catalog, | |
| [Query="select * from $system.TMSCHEMA_DATA_SOURCES"]) | |
| in | |
| dataSources[ConnectionString]{0} |
Extract Mashup field into binary
I’ve encountered some difficulties parsing the connection string with a Text.Split function, so I’ve used a Splitter function instead.
| (connectionString as text) => | |
| let | |
| //Define a semicolon splitter | |
| split = Splitter.SplitTextByDelimiter(";"), | |
| //Split the connection string by semicaolon and assume that Mashup is the last element | |
| mashup = Text.Replace(List.Last(split(connectionString)),"Mashup=","") | |
| in | |
| //Convert base64 string to binary | |
| Binary.FromText(mashup) |
Unzip Formulas/Section1.m from Mashup binary
I’ve seen at least two functions for unzipping files in Power Query written by KenR and Mark White. Also, I’ve described before how to call R unz function from an R.Execute wrapper. This is the result of combining the two.
| (binaryZip,fileName) => | |
| let | |
| //shorthand | |
| UInt32 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian), | |
| UInt16 = BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian), | |
| //ZIP file header fixed size structure | |
| Header = BinaryFormat.Record([ | |
| MiscHeader = BinaryFormat.Binary(14), | |
| CompressedSize = UInt32, | |
| UncompressedSize = UInt32, | |
| FileNameLen = UInt16, | |
| ExtraFieldLen = UInt16]), | |
| //ZIP file header dynamic size structure | |
| FileData = (h)=> BinaryFormat.Record([ | |
| FileName = BinaryFormat.Text(h[FileNameLen]), | |
| ExtraField = BinaryFormat.Text(h[ExtraFieldLen]), | |
| UncompressedData = BinaryFormat.Transform( | |
| BinaryFormat.Binary(h[CompressedSize]), | |
| (x) => try | |
| Binary.Buffer(Binary.Decompress(x, Compression.Deflate)) | |
| otherwise null)]), | |
| //Parsing the binary in search for PKZIP header signature | |
| ZipIterator = BinaryFormat.Choice(UInt32, (signature) => if signature <> 0x04034B50 | |
| then BinaryFormat.Record([FileName=null]) | |
| else BinaryFormat.Choice(Header,(z)=>FileData(z))), | |
| ZipFormat = BinaryFormat.List(ZipIterator), | |
| out = List.Select(ZipFormat(binaryZip), each _[FileName]=fileName) | |
| in | |
| out{0}[UncompressedData] |
Wrapping all of the functions above and adjusting them to support multiple instances of Power BI while applying some minification techniques, we get the final M script.
| let | |
| //Declaring compact function | |
| MSMDSRVport = () => let cu=Table.FirstN(Table.Sort(Folder.Contents("C:\Users"),{{"Date accessed",Order.Descending}}),1)[Name]{0} in List.Transform(Table.SelectRows(Folder.Files("C:\Users\"&cu&"\AppData\Local\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces"),each [Name]="msmdsrv.port.txt")[Content],each Lines.FromBinary(_,null,null,1200){0}), | |
| GetConnectionString = (port) => let c=AnalysisServices.Databases("localhost:"&port)[Name]{0} in AnalysisServices.Database("localhost:"&port,c,[Query="select * from $system.TMSCHEMA_DATA_SOURCES"])[ConnectionString], | |
| ConnectionString2Bin = (cs) => let sp=Splitter.SplitTextByDelimiter(";") in Binary.FromText(Text.Replace(Text.Replace(List.Last(sp(cs)),"Mashup=",""),"mashup=","")), | |
| Unz = (bZ,fN) => let UInt32=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger32,ByteOrder.LittleEndian),UInt16=BinaryFormat.ByteOrder(BinaryFormat.UnsignedInteger16,ByteOrder.LittleEndian),H=BinaryFormat.Record([MH=BinaryFormat.Binary(14),CS=UInt32,US=UInt32,FNL=UInt16,EFL=UInt16]),FD=(h)=>BinaryFormat.Record([FN=BinaryFormat.Text(h[FNL]),EF=BinaryFormat.Text(h[EFL]),UD=BinaryFormat.Transform(BinaryFormat.Binary(h[CS]),(x)=>try Binary.Buffer(Binary.Decompress(x,Compression.Deflate)) otherwise null)]),ZI=BinaryFormat.Choice(UInt32,(_)=>if _<>0x04034B50 then BinaryFormat.Record([FN=null]) else BinaryFormat.Choice(H,(z)=>FD(z))),ZF=BinaryFormat.List(ZI),out=List.Select(ZF(bZ),each _[FN]=fN) in out{0}[UD], | |
| //Get all the ports for $Embedded$ Tabular instances | |
| Port = Table.FromList(MSMDSRVport(),null,{"port"}), | |
| //Add a column with embedded connection strings | |
| AddConString = Table.ExpandListColumn(Table.AddColumn(Port,"ConnectionString",each GetConnectionString([port])), "ConnectionString"), | |
| //Parse the ConnectionString column to reveal the Mashup string in binary format | |
| AddMashupBin = Table.AddColumn(AddConString,"Mashup",each ConnectionString2Bin([ConnectionString])), | |
| //Unzip from the binary Mashups the Formulas/Section1.m files | |
| ParseMScripts = Table.TransformColumns(AddMashupBin,{"Mashup", each Unz(_,"Formulas/Section1.m")}), | |
| //Present the M scripts as lines | |
| out = Table.AddColumn(ParseMScripts,"MLines", each Lines.FromBinary([Mashup])) | |
| in | |
| out |
Some of the use cases that are still left for me to explore with this approach:
- pushing M scripts to GitHub, thus replicating the query catalog functionality
- exporting the Power Queries to *.odc files and consequently importing M scripts from Power BI desktop into Excel
PowerShell version
For the curious minds, here is the PowerShell code I used in Power Query reconnaissance.
| #Finding the portnumber on which the $Embedded$ tabular model is running on | |
| $embedded = "$env:LOCALAPPDATA\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces" | |
| $ports = Get-ChildItem $embedded -rec | where {$_.Name -eq "msmdsrv.port.txt"} | |
| $port = Get-Content $ports.FullName -Encoding Unicode | |
| #Getting the data sources from the $Embedded$ tabular model | |
| [xml] $db = Invoke-ASCmd -Server:localhost:$port -Query:"SELECT * from `$SYSTEM.TMSCHEMA_DATA_SOURCES" | |
| $db.return.root.row | |
| $cs = $db.return.root.row.ConnectionString | |
| #Cleaning up the connection string | |
| $b64 = $cs.Split(";")[-1].Trim("Mashup=").Trim("""") | |
| $bytes = [Convert]::FromBase64String($b64) | |
| $temp = "c:\temp" | |
| #Output to a binary ZIP file | |
| [IO.File]::WriteAllBytes("$temp\a.zip", $bytes) | |
| #Unzip | |
| Add-Type -AssemblyName System.IO.Compression.FileSystem | |
| [System.IO.Compression.ZipFile]::ExtractToDirectory("$temp\a.zip","$temp\a") | |
| #TADA! | |
| ($m = Get-Content $temp\a\Formulas\Section1.m) |
Please note that this approach can be used for extracting Power Queries for already loaded data and only in Power BI desktop.
Query Power 
Hello,
It seems very interresting to me but I cannot succeed to implement it in Power BI query editor.
Can you provide a sample PBIX file ?
Thanks in advance and thanks for this solution !
Didier
LikeLiked by 1 person