let // Smart way to retrieve PortID and Database name from Analysis Services instance that PowerBI has created locally on your machine // posted in the comments here: http://www.thebiccountant.com/2016/04/09/hackpowerbi/ where there are other alternatives as well. // You might need to adjust the path "C:\Users" to your local settings. // If you have more than one instance open it is not sure which instance will be fetched, so make sure there's only one pbix-file open when analysing PBI_Model = let CurrentUser = Table.FirstN(Table.Sort(Folder.Contents("C:\Users"), {{"Date accessed", Order.Descending}}), 1)[Name]{0}, PortID = 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}, Database = AnalysisServices.Database("localhost: "&PortID, "", [Query="select * from $SYSTEM.DISCOVER_PROPERTIES"]){0}[Value] in [Database = Database, PortID=PortID], // Function to retrieve DMV takes the name of the DMV as its parameter fnDMV = (DMV) => let QUERY = if Query_ = null then "*" else Query_, Import_SSAS = AnalysisServices.Database("localhost: "&PBI_Model[PortID], PBI_Model[Database],[Query="select * from $SYSTEM."&DMV]) in Import_SSAS, // DMVs who fetch the size data. Details see here: http://tinylizard.com/script-update-what-is-eating-up-my-memory-in-power-pivot/ Scott = fnDMV("DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS WHERE used_size > 0"), Kasper = fnDMV("DISCOVER_STORAGE_TABLE_COLUMNS WHERE dictionary_size > 0"), // Merge views and cleanup #"Merged Queries" = Table.NestedJoin(Scott,{"TABLE_ID", "COLUMN_ID"},Kasper,{"TABLE_ID", "COLUMN_ID"},"NewColumn",JoinKind.LeftOuter), #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"DATATYPE", "DICTIONARY_SIZE"}, {"DATATYPE", "DICTIONARY_SIZE"}), #"Removed Other Columns" = Table.SelectColumns(#"Expanded NewColumn",{"DIMENSION_NAME", "COLUMN_ID", "RECORDS_COUNT", "USED_SIZE", "VERTIPAQ_STATE", "DATATYPE", "DICTIONARY_SIZE"}) in #"Removed Other Columns"