How to Add Data to a Semantic Model in Power BI

A Power BI report is only as good as the data behind it, and that data often changes. Maybe your company started running ads on a new platform, or you have a new spreadsheet with regional sales targets to track. The process of adding this new information into an existing report can feel a bit daunting. This guide will walk you through exactly how to add data to a semantic model in Power BI, the most critical step in keeping your reports relevant and powerful.

We'll cover the right way to incorporate new data sources using the Power Query Editor, from simply connecting a new file to merging and appending tables. Finally, we’ll go over what to do after the data is in your model to make sure it works seamlessly with the visuals you’ve already built.

What is a Semantic Model? A Quick Refresher

Before jumping into the “how,” let’s quickly clarify what a Power BI semantic model is. If you've used Power BI for a while, you probably know this by its old name: a dataset.

Think of it like this: your Power BI report - the collection of charts, graphs, and tables - is the "face" of your analysis. The semantic model is the "brain." It's the integrated layer of data that lives behind your visuals and contains a few key components:

• The Data Connections: This is how Power BI plugs into your data sources, whether they are Excel files, SQL databases, or web services.
• The Data Tables: These are the actual tables of data that have been cleaned, transformed, and loaded into the model.
• Relationships: The model defines how different tables relate to each other (e.g., how your Sales table connects to your Customers table).
• Calculations (DAX): Any custom calculations you’ve written using Data Analysis Expressions (DAX), like measures (e.g., Total Revenue = SUM(Sales[Revenue])) or calculated columns, are stored here.

When you "add data to a semantic model," you're enriching this brain with new information, allowing you to create more comprehensive and insightful reports.

Why Would You Need to Add More Data?

Your business doesn't stand still, so your reports shouldn't either. There are plenty of reasons why you might need to update an existing semantic model with new data. Here are a few common scenarios:

• Adding a new data stream: You’ve started a new marketing campaign on TikTok and now need to add TikTok Ads data alongside your existing Facebook and Google Ads performance metrics.
• Incorporating supplementary information: Your sales report is great, but it’s missing context. You need to add an Excel sheet containing regional sales quotas or a lookup table with product category details.
• Combining historical and new data: You have separate CSV files for sales data from each month of the year. Instead of building a new report every month, you can append each new file to the existing sales data in your model.
• Enhancing existing tables: You have an employee data table with an EmployeeID but need to bring in their department and manager information from a separate HR system table. You can merge these two tables based on the common ID.

A Step-by-Step Guide to Adding Data in Power BI

The best and most reliable place to add and modify data sources in Power BI is the Power Query Editor. This is Power BI's built-in tool for Extracting, Transforming, and Loading (ETL) data. It lets you connect to hundreds of different sources, clean up messy data, and shape it exactly how you need it before it ever touches your semantic model.

Method 1: Adding a Completely New Data Source

This is the most common use case. Let's imagine you have a report analyzing your online store's sales and you want to add a simple Excel spreadsheet that categorizes your products.

Step 1: Open the Power Query Editor

First, open your Power BI file (.pbix). On the Home tab of the ribbon at the top, find the "Queries" section and click the Transform Data button. This will launch the Power Query Editor in a new window.

Step 2: Connect to the New Data Source

In the Power Query Editor, click the New Source button on the Home tab. A dropdown menu will appear with common data sources (Excel workbook, SQL Server, etc.). If you don't see yours, click "More..." to open a window with a searchable list of all available connectors.

For our example, we'll choose Excel workbook. Navigate to your file, select it, and click Open.

Step 3: Select and Preview Your Data

A "Navigator" window will pop up, showing you all the sheets and tables within your Excel spreadsheet. Click on the name of the table or sheet you want to import. Power Query will show you a preview on the right so you can make sure you're grabbing the right information.

Once you've selected your data table, click OK.

Step 4: Shape and Clean Your New Data

Your new data is now loaded into the Power Query Editor as a new query (you'll see its name appear in the Queries pane on the left). Now is your chance to clean it up before it goes into your model. This is a critical step that ensures your data is reliable.

Here are a few common cleaning steps you might perform:

• Remove irrelevant columns: Right-click the header of any column you don't need and select Remove.
• Check data types: Make sure numbers are formatted as numbers and dates are formatted as dates. Power Query does a good job of guessing, but you can manually change a column's type by clicking the icon (e.g., "1.2," "ABC," or a calendar) in the column header.
• Promote headers: If your first row contains the column names, go to the Home tab and click Use First Row as Headers.

Each change you make is recorded as a step in the "Applied Steps" pane on the right. You can click on any previous step to see what the data looked like at that stage, or click the "X" to delete a step you made by mistake.

Step 5: Load the Data into Your Model

Once you're happy with how the new data looks, click the Close & Apply button in the top-left corner. Power Query will close, and Power BI will load the new table into your semantic model.

Success! You've officially added a new data table. But you're not quite done yet. We'll cover the next steps in a moment.

Method 2: Appending Data to an Existing Table

Appending means stacking tables with identical or similar columns on top of each other. This is perfect for when you have, for example, monthly sales exported into separate CSV files.

1. First, make sure both tables (the original and the new one) are in Power Query as separate queries. Follow Method 1 to import the new file first.
2. Select the query you want to append data to (e.g., your primary All Sales query).
3. On the Home tab in Power Query, click the dropdown for Append Queries.
4. A new window will appear. If you're combining just two tables, select the table you want to append from the dropdown menu and click OK. If you have three or more, select that option and add them to the list.
5. Click Close & Apply. All the rows from the new query will now be added to the bottom of the original one.

Method 3: Merging Data into an Existing Table

Merging is the equivalent of a VLOOKUP or INDEX/MATCH in Excel. It lets you add columns from one table to another based on a common key column (like an ID, an email address, or a date).

1. Make sure both tables are loaded into Power Query.
2. Select the query you want to add columns to.
3. On the Home tab, click Merge Queries.
4. The "Merge" window will appear. The top part shows your selected table. In the dropdown below it, choose the second table you want to pull data from.
5. Now, select the matching column in both tables. For example, you might click the ProductID column in your Sales table and the ProductID column in your Product Details table. Power Query will show you how many of the rows match.
6. For "Join Kind," the default is often Left Outer, which is usually what you want. This keeps all rows from your original table and brings in matches from the second.
7. Click OK. You'll see a new column has been added with "[Table]" as its contents. Click the two-way arrow icon in the column header, uncheck the columns you don't need, and click OK again. The new columns will now appear in your table.
8. Finally, click Close & Apply.

After a Data Load: Essential Next Steps

Just loading data isn't enough. To make it truly useful, you need to integrate it with the rest of your model.

1. Create Relationships

This is arguably the most important post-load step. Power BI needs to know how your new table relates to your existing ones. In our earlier example, we added a ProductCategories table. Now we need to tell Power BI that it's related to our Sales table through a ProductID column.

• Go to the Model view in Power BI Desktop (the third icon on the far left that looks like a network of tables).
• Find your new table. It will likely be floating on its own.
• Click and hold the linking column in one table (e.g., ProductID in the Sales table) and drag the cursor over to the same column in your new table (ProductID in ProductCategories).
• Release the mouse button. Power BI will draw a line between the two tables, indicating a relationship has been formed. You can double-click this line to edit the relationship's properties if needed.

Without this relationship, filtering your report by a product category would have no effect on your sales figures.

2. Create or Update Your DAX Measures

With new data comes new analytical opportunities. Can you create any new helpful measures? If you added sales targets, you can now write a DAX measure to calculate % of Target Achieved. If you added product categories, you can now analyze sales performance for each category. Don't forget to review existing measures to see if they need to be updated to account for the new data.

3. Check Your Report and Refresh

Head to the Report view and start using your new data! Drag fields from the new table into your charts and visuals to see if they behave as expected. And of course, remember to hit an occasional Refresh on the Home tab to ensure your entire semantic model pulls in the latest data from all its sources.

Final Thoughts

Loading new data into an existing Power BI semantic model is a core skill for anyone who manages reports. By using the Power Query Editor to connect, transform, and load your sources, and then setting up the right relationships in the Model view, you can keep your reports accurate, comprehensive, and valuable for decision-making.

While Power BI is incredibly powerful for complex business modeling, the process of manually connecting sources, shaping data in Power Query, and building relationships can be time-consuming, especially for getting quick answers about marketing or sales performance. We created Graphed because we wanted to skip that manual work entirely. It connects directly to your popular apps like Google Analytics, Shopify, and Salesforce in seconds, letting you create dashboards and get insights simply by asking questions in plain English, giving you back hours of your week without ever having to open a Power Query editor.