Search code examples
excelpowerbipowerbi-desktopdaxstudio

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

I have been able to establish a connection between power bi desktop (v2.138.1452.0 64-bit) and excel (v2501 of microsoft 365 insider beta channel) using dax studio (v3.2.1) under windows 11 64-bit as shown in the attached screenshot. The query I created on the excel side depends on an Analysis Services Database connection. I launch dax studio either from within power bi desktop or excel and it works.

enter image description here

However, the number within the database name (such as the 50589 of localhost:50589) and the database ID (such as 1a88654c-2a123-1234-b4b0-91617abcde2f) for the server connected by dax studio changes whenever I launch a new dax studio session after restarting excel or power bi. Then, I have to go and edit the source for the query on the excel side with a new database number and ID. I have tried saving one of the dax studio sessions as a .dax file and opening it after launching dax studio either standalone (after opening power bi desktop) or from within power bi or excel, but it does not help.

Is it possible to have a constant database name and ID for the connection established by dax studio in between power bi and excel, at least whenever the same power bi file is concerned? If not, what could be a practical alternative to my updating the connection details each and every time on the excel side while continuing to use the same excel power query and have it refreshed conveniently whenever needed?

P.S. This may not be a typical problem for most users of both power bi and excel, but it is for me because microsoft appears to require a work email for the native connection between power bi and excel to work and I don't work anywhere. The same issue prevents me from trying a potential solution via a microsoft app called “On-Premises Data Gateway”.

Solution

  • I have solved this problem by doing all of the following:

    1. Start Power BI Desktop using the command line with a command that looks like the following in summary:

    pbidesktop /diagnosticsport:1680 MyPBIFile.pbix

    The version I have used is a bit longer because the exe file for pbidesktop is not reachable from anywhere by default in Windows (11 in my case):

    "C:\Program Files\WindowsApps\Microsoft.MicrosoftPowerBIDesktop_2.138.1452.0_x64__8wekyb3d8bbwe\bin\pbidesktop.exe" /diagnosticsport:1680 "V:\MyPBIFile.pbix"

    1. In Excel, modify the connection string found under Data > Queries & Connections > Connections > choose connection > Properties > Definition by removing the text

    Initial Catalog=xxxxxxxx-xxxx-xxxxxxxx-xxxxxxxxxxxx;

    The connection's definition looks like that in the following screenshot after the removal:

    enter image description here

    1. Change the relevant part of the code underlying the query in Excel in M formula language from for example

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

    to

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

    I have found out all of these with the help of Owen Auger at Microsoft's Power BI forum. He has suggested a second solution, but I haven't tried it yet. For more details, see my question in that forum and Owen's answers.

    At the end, however, I gave up on using Power BI and went back to Excel completely because Power BI Desktop's Power Query engine turned out to be notably less efficient than Excel’s in loading a large set of CSV files and transforming the tables in them the way I need.