How to keep DAX Studio connection credentials between Power BI and Excel unchanged?

Hi again apaksoy 

No problem, and thanks for clarifying things 🙂

My example assumed a live Analysis Services connection to a local model in Power BI Desktop. However, you are actually importing using Power Query's Analysis Services connector.

Method 1:

Assuming you have ensured that the port number is fixed to a known value, change the "Database ID" step of your query from:

#"Database ID" = Source{[Name="8ddb886a-cd8a-4299-80c4-5b5d6eef422a"]}[Data],

to

#"Database ID" = Source{0}[Data],

This relies on the fact that a local model appears as a server with a single Analysis Services database, which we can identify by index (zero) rather than name. 

Method 2:

You can use methods similar to those described here to locate the port number by examining files saved on the local drive that are generated by Power BI Desktop. Each instance of Power BI Desktop creates a folder structure which contains files including msmdsrv.port.txt which contains the port number as text.

This method is a bit riskier as it relies on identifying the correct path of the file and selecting the correct folder.

Here is an example I got working on my machine (XLSX attached also).

• GetPowerBIPort returns the port number.
• PowerBIModel contains the initial steps to connect to the model.

// ============================================
// GetPowerBIPort
// ============================================
// The below code assumes that the ports are contained in files
// C:\Users\%user%\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces\
let
    IgnoreFolder = {"Default","Default User","Public","All Users"},
    UsersSubfolderPath = "\Microsoft\Power BI Desktop Store App\AnalysisServicesWorkspaces",
    UserFolders = Table.SelectRows (Folder.Contents("C:\Users"), each [Attributes][Kind]="Folder" and not List.Contains(IgnoreFolder,[Name]) ),
    // Assume current user is the one with latest [Date accessed] folder in C:\Users
    CurrentUserFolder = Table.MaxN(
      UserFolders,
        each [Date accessed],
        1
    ),
    CurrentUser = CurrentUserFolder[Name]{0},                        
//Read the contents of file msmdsrv.port.txt from subfolder of AnalysisServicesWorkspaces
    Folders = 
    Table.SelectRows(
    Folder.Contents("C:\Users\" & CurrentUser & UsersSubfolderPath),
    each Text.StartsWith([Name],"AnalysisServicesWorkspace")),
    AddFiles = Table.AddColumn(Folders, "Port", each Table.SelectRows([Content]{[Name="Data"]}[Content], each [Name]="msmdsrv.port.txt")),
    FilesBinary = Table.ExpandTableColumn(AddFiles, "Port", {"Content", "Date accessed"}, {"Port.Content", "Port.Date accessed"}),
    #"Removed Other Columns" = Table.SelectColumns(FilesBinary,{"Port.Content", "Port.Date accessed"}),
    LatestFile = Table.Max(#"Removed Other Columns",each [Port.Date accessed] ),
    PortNumber = Lines.FromBinary(LatestFile[Port.Content],null,null,1200){0}
in
    PortNumber

// ============================================
// PowerBIModel
// ============================================
let
    CurrentPort = GetPowerBIPort,
    Source = AnalysisServices.Databases("localhost:" & CurrentPort, [TypedMeasureColumns=true, Implementation="2.0"]),
    Database = Source{0}[Data],
    Model1 = Database{[Id="Model"]}[Data],
    Model2 = Model1{[Id="Model"]}[Data]
in
    Model2