How to Edit Power BI Live Connection Without Destroying Everything

In this tutorial, Nick from Pragmatic Works walks you through how to enhance your Power BI reports by editing a live connection to a semantic model without losing any existing data. This guide is particularly useful if you want to add new data sources or make adjustments to your reports without disrupting the underlying model.

Understanding Semantic Models and Live Connections

A semantic model in Power BI is often considered the single source of truth, containing all the essential data and relationships that your reports rely on. When you connect to a semantic model using a live connection, you can’t make changes to the model, such as adding new calculated columns or modifying relationships. However, there are ways to extend this functionality without compromising the integrity of your existing data.

Step 1: Connect to Your Semantic Model

Nick starts by connecting to a semantic model hosted in the cloud:

• Go to Get Data in Power BI Desktop.
• Select Semantic Models and choose the model you want to connect to.
• Once connected, you’ll have a live connection to the model, but you’ll notice that your options for editing are limited.

Step 2: Convert Live Connection to Direct Query

To add more data sources or make changes, you need to convert the live connection to a direct query:

• Go to Get Data again and choose another data source, such as a CSV file.
• Power BI will prompt you to switch to a direct query connection. This action will add a local model to your file, allowing more flexibility in editing.
• Click Add Local Model to confirm the change.

Step 3: Add New Data Sources

With the model now in direct query mode, you can add new data sources to your report:

• Select the data source you want to import (e.g., a CSV file).
• The new data source will appear in the data view, and you’ll be able to create relationships between the new data and the existing semantic model.
• Build relationships by dragging and dropping fields between tables.

Step 4: Create Calculated Columns and Measures

Now that the connection has been converted to direct query, you can add calculated columns and measures directly to the model:

• Right-click on any table and choose New Column or New Measure.
• Create custom calculations, such as flags or indicators, to enhance your reports.
• Add these new fields to your visuals and see the immediate impact.

Publishing and Refreshing

Once you’ve made your changes, you can publish the report to the Power BI Service:

• Set up scheduled refreshes for any imported data sources.
• Maintain direct query connections for live data, ensuring your reports are always up-to-date.

Enabling Direct Query for Semantic Models

If you encounter issues converting your semantic model to direct query, ensure that your Power BI settings allow for this functionality:

• Go to the Power BI admin settings and enable Allow Direct Query Connections to Power BI Semantic Models.
• If you don’t have admin rights, request this change from your administrator.

By following these steps, you can extend the capabilities of your Power BI reports, combining the power of your existing semantic model with new data sources and custom calculations. This approach allows for a seamless blend of live and imported data, making your reports more dynamic and informative.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.