Find your local Power BI connection string

Quick Command Reference: netstat with most common [::1]:PortNumber.

I always enjoy working in Power BI, but sometimes when working on a data model I want to export the data for a static, line by line analysis. With any visual in Power BI there is an export option to dump the data driving the visual, but this is capped at a certain number of rows.
Export-Data

Within the desktop client, this just as a CSV, with 30,000 rows as the max. If you wanted to review more, then you would need to connect to the model with another tool, or publish to the service where you can export a modern Excel file with 150,000 rows.

If you wanted to connect with Excel, and you're using Excel 2016, you can use the Get Data experience to connect to the file, using the Database > SQL Server Analysis Services connector.

Excel-2016-Connection

This might be a little confusing at first, but Power BI is actually running a version of SQL Server Analysis Services in Tabular mode. This is what you're working in when you've connected to the data sources and begin writing DAX (Data Analysis Expressions).

When you go to connect, you'll see a screen prompting you for the name of the server you're connecting to, an option for the database or model, and an option to paste in the exact DAX or MDX (Multidimensional Expressions) query if you have one prepared. The linked documentation for MDX is from Microsoft, but it is an industry standard language for OLAP (Online Analytical Processing tools).

Connection1

Now the community has found a lot of different ways to find the Power BI Connection. Whenever I have the option, I'll make use of Dax Studio from the folks at sqlbi. The documentation includes a page for the Status bar which will display the connection name we're looking for. localhost-port This says we're looking at Local Host, or our local machine, and the port we're specifically connecting to is 61717. Now unfortunately, this is randomized every time you open Power BI, and so you'll have to determine what the port is yourself.

When DAX Studio isn't an option, the easiest way I have found to determine what port my Power BI model is running on is the use of the Netstat command in the command line. Open the command line, and here I often use the Windows key then start typing "CMD" for it to show up. You can also run this in PowerShell.

You'll see a bunch of IP Addresses show up, including some in the local address that look like [::1]:PortNumber. The [::1] is a way to address your local machine as a server. It may also be referenced as localhost or 127.0.0.1.

Netstat-1

In every time I've had to do this, the local address repeated multiple times, in this case 61717, is the server port you'll want to connect to. If you have the ability to run the command line as administer, you can run Netstat -b. This command allows you to see the process, in this case we're looking for msmdsrv.exe and what port it is using.
Admin-Netstat

If you wanted to do some validation and weren't able to run Netstat -b which does require administrative privileges on your device, you can run Netstat -o which will include a PID (Process ID).
netstat-o

With this PID, you can go into Task Manager > More Details > Details Tab, and if you don't already see a column called PID, you can right click on the existing columns, choose Select columns to bring it up.

Task-Manager

We can see that the PID from Netstat -b and the Task Manager for msmdsrv.exe lines up, which tells us this the right process to connect to.

Going back to our connection to Excel to complete our connection, we'll put localhost:61717 into the Server field, and click Ok.

In the Navigator screen, you'll find a hierarchy of values to bring into the data you want to explore. This is the Server as the top level, followed by the GUID (Globally Unique Identifier) as the database name, then a folder for the Model, the Cube, an OLAP concept, also named Model. Within this cube is the model you've built in Power BI, with folders for the measures and a Dimension icon, another OLAP concept, for the tables.

Navigator-1

Select the fields and measures that you want to bring in for the final table, then click Load to bring it into Excel, or Edit for any additional manipulations you may want to do through Power Query, or the M Language, or Get & Transform as it's known in Excel.

Now you're ready to continue your analysis over the raw data in your tables. I often use this to double check situations where I want to ensure that the DAX I'm writing doesn't just look right based on my expectation, but that I have secondary validation that it's correct.

Author image
Hi, I'm Aaron Grossman, a Business Intelligence developer documenting what I've learned as I continue to grow my career. I can be reached at me@aaronjgrossman.com.