How to Remove Live Connection in Power BI

A Power BI Live Connection to a published dataset or Analysis Services model is great for consistency, but it can feel like working with your hands tied. If you've ever wanted to add another data source, create a custom table, or use the full power of the Power Query Editor, you've likely hit the "Live Connection" wall. This guide will walk you through exactly how to remove that live connection and convert your report to a more flexible Import model.

Why Remove a Live Connection in the First Place?

Working with a Live Connection means you're building a report on top of a pre-existing, often "golden," data model. This is excellent for ensuring everyone in an organization uses the same metrics and definitions. However, it comes with significant limitations that often lead users to seek a more flexible approach.

You might want to remove a live connection to:

• Combine data from multiple sources: The biggest limitation. With a live connection, you can't pull in a simple Excel file, a CSV from a SharePoint site, or data from another database to enrich your report. Your analysis is confined to the single, live dataset.
• Use the Power Query Editor: A live connection grays out the "Transform data" button. This means you can't clean, reshape, or transform the data. You have no access to the powerful tools within Power Query for tasks like unpivoting columns, splitting text, or creating conditional columns.
• Create calculated tables or new columns with DAX: While you can create report-level measures when connected to a live model, your ability to manipulate the model itself is restricted. You can't create calculated tables or calculated columns, which are essential for more advanced data modeling scenarios.
• Improve performance (sometimes): If the underlying Analysis Services model is slow or under heavy load, your report's performance will suffer as every interaction sends a new query to the source. Converting to an Import model means the data is stored in a highly compressed format within Power BI, often leading to snappier visuals and slicers.

Switching from a live connection to an Import model effectively gives you the "keys to the car," unlocking the full suite of data modeling and preparation tools that Power BI Desktop offers.

Import vs. DirectQuery vs. Live Connection: A Quick Primer

Before changing your connection type, it's helpful to understand the three main ways Power BI gets its data. This context will make the following steps much clearer.

• Import: This is the most common and powerful mode. Power BI loads a compressed copy of the source data into your .pbix file. You get blistering fast performance and the full, unrestricted use of Power Query and DAX. The trade-off is that the data is only as fresh as your last refresh, and there are limits to the size of the dataset you can import.
• DirectQuery: Data remains in its original source. Power BI only stores metadata (table names, column names) and sends a query to the source for every visualization. This is ideal for huge datasets (terabytes of data) that can't be imported or when near real-time data is necessary. It comes with significant modeling limitations compared to Import.
• Live Connection: This isn't a connection to a raw data source but to a published Power BI dataset or an Analysis Services model. You're essentially building a report on top of an already existing "golden" dataset, complete with relationships and measures. This is the most restrictive mode, you can't do any data modeling in the .pbix file.

Step-by-Step Guide: How to Remove a Live Connection

In the past, going from a live connection to an Import model was arduous. You had to essentially restart, reconfigure your model, and possibly even re-type all your visuals. Luckily, Power BI now provides a straightforward workaround to help take over.

Check Your Current Status

Open your Power BI report. In Report View, navigate to the "Data" (table icon) on the left pane and the "Transform data" button in the Home Ribbon tab. If it's grayed out, you're working in a live connection. You'll also note a message at the bottom of the ribbon that reads: "Connected live to Power BI dataset in [...]" confirming your live connection status.

Convert Live to DirectQuery

You can't just switch from a live connection directly to Import. First, you have to convert to the local model. Power BI makes this easy with the "Make changes to this model" feature.

• Step 1: In the Home Ribbon, click on the "Make changes to this model" button. This is typically next to the "Transform data" and "Refresh" buttons.
• Step 2: A dialog box will appear warning you that this is a one-time operation. Clicking "Add a local model" will proceed.
• Step 3: Power BI will now start copying tables, relationships, and measures from the remote dataset into your current .pbix file.

Convert from DirectQuery to Import: The Final Step

This step is the final piece of the puzzle:

• 1. In the "Model" view, select all your tables by holding Shift and clicking to select one and then the last. Don't forget any tables. You can also use the "drag and select" box over them.
• 2. On the "Properties" pane, under "Advanced," find the "Storage mode" dropdown, and change it from DirectQuery to Import.
• 3. A warning will likely appear about implications of switching to Import. It's essentially alerting you to the fact that you may not be able to go back to DirectQuery without reloading any data changes since then. Click OK.

When you click "OK," Power BI will start recompressing any table data you have into your .pbix file. Once it’s done, you'll have a disconnected full model ready to report.

• 4. The "Transform data" button will now be enabled to allow queries.
• 5. You will be able to "Add data" using "Get data" from the Home Ribbon to download new sources!

Alternative Method: Starting from Scratch (If Needed)

If for whatever reason you’re using an older version of Power BI or if the "Make changes to this model" feature is unavailable (e.g. your admin turned it off), you may have to follow the traditional method by restarting a full scratch model:

• 1. Open your existing Live Connection report.
• 2. Open a blank instance of Power BI Desktop.
• 3. On the new report, connect to the original data source. This is the most crucial part - do not connect to the Power BI dataset. Instead, connect to whatever dataset the live connection was originating from. For example, if the live connection was to Azure SSAS demo, connect directly to the SQL SSAS database using that same server names and table schemes.
• 4. Choose "Import" mode. You'll be prompted to choose between Import and DirectQuery, select Import.
• 5. Now that you've got the whole raw data in the new report, recreate your calculated tables and rearrange as needed using DAX. This means setting up relationships between your tables and recreating any calculated measures using DAX.
• 6. Copy and paste the visuals from your "Live connection" report into the new "Import" report. Power BI is impressive about this and will often automatically re-link with matching fields, but some measures might require manual work.

Although more manual, this method is foolproof and leaves you with the original live reporting intact and an "Import version."

What To Do After You've Removed the Connection

Once you have successfully switched from a live connection to an Import model, your work is nearly done. Here are a few critical steps to make the most of it:

• Schedule Refresh: Unlike a Live Connection, which is always live, an Import model needs to be refreshed periodically to stay up-to-date. After publishing your report to the Power BI Service, go to the dataset settings and configure a refresh schedule.
• Optimize: Use the Power Query Editor to clean, shape, and transform your data. This means removing unnecessary columns, filtering unwanted rows, and restructuring datasets with fewer columns. These small modifications ensure that your data models perform better and your reports will operate smoothly.
• Add New Data Sources: This is the biggest advantage. Now that your report is in "Import mode," you can finally bring in that CSV file from your Salesforce team, an Excel sheet with grades data, and another dataset format like SQL Server.
• Create Advanced Measures: Liberate the full power of DAX. You now can create index columns, elaborate complex measures, and perform calculations that were simply impossible in a Live Connection.

Final Thoughts

Changing from a live connection to an Import model in Power BI can unlock the full potential of the tool. This allows you to combine data sources, enrich formats, and format data in Power Query and write more complex measures. The key is to first convert to a DirectQuery model using the "Make changes to this model" feature, then switch the storage mode to Import. Explore more on Graphed to use the advanced features and maximize the strategic changes for your layouts and templates.