Last active
September 5, 2020 11:44
-
-
Save Hugoberry/25c5ae7ff973e9c4b89b05d199403e30 to your computer and use it in GitHub Desktop.
Displays all of the Power Query M scripts from the opened instances of Power BI. (ignore the ts extension, this is an M script:)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment