How To Add New Data To Existing Data In Power BI
Adding new data to existing datasets in Power BI is a fundamental skill for enterprise analytics, whether you are appending new rows from monthly reports, merging supplementary columns from reference tables, or connecting entirely new data sources to enrich your data model. Power BI provides multiple methods for combining data, each suited to different scenarios, from the Power Query Editor's Append and Merge operations to incremental refresh for automated, ongoing data additions.
Appending Queries: Adding New Rows
The Append operation in Power Query combines two or more tables vertically by stacking rows from one table below another. This is the correct approach when your new data has the same column structure as your existing data and you need to add additional records.
- Append Two Tables - In Power BI Desktop, go to Home > Append Queries. Select the primary table and the table to append. Power Query matches columns by name and data type, adding all rows from the second table to the first.
- Append Three or More Tables - Select "Append Queries as New" to create a new combined table, then choose "Three or more tables" to append multiple sources simultaneously. Ideal for consolidating monthly or regional datasets.
- Handling Mismatched Columns - When tables have different column structures, Power Query creates null values for missing columns. Review the appended result and handle nulls appropriately using Replace Values, Fill Down, or conditional column transformations.
- Folder-Based Append - For recurring data files (monthly CSVs, quarterly Excel reports), use the Folder connector to automatically append all files in a SharePoint library or local folder. New files added to the folder are automatically included on refresh.
- Performance Tip - When appending large tables, apply filters before the append step to reduce the data volume. Filter out irrelevant rows, outdated records, or test data at the source level rather than after appending.
Merging Queries: Adding New Columns
The Merge operation in Power Query combines tables horizontally by matching rows based on a common key column, similar to SQL JOINs. Use Merge when you need to enrich existing data with additional attributes from a related table.
- Merge Types - Power Query supports six join types: Left Outer (all rows from left, matching from right), Right Outer, Full Outer, Inner (only matching rows), Left Anti (left rows with no match), and Right Anti. Left Outer is the most commonly used for data enrichment.
- Multi-Column Keys - When a single column doesn't uniquely identify rows, hold Ctrl and select multiple columns as the merge key (e.g., merge on both CustomerID and Date for transactional data).
- Expand Merged Columns - After merging, the result includes a column with nested tables. Click the expand icon to select which columns from the merged table to include, and choose whether to use the original column name as a prefix.
- Fuzzy Matching - Enable fuzzy matching in the merge dialog for scenarios where key values don't match exactly (e.g., "Microsoft Corp" vs "Microsoft Corporation"). Configure similarity threshold and transformation table for consistent matching.
- Merge vs Relationship - For dimension/fact table combinations (e.g., Sales + Products), consider using Power BI relationships instead of merging in Power Query. Relationships are more memory-efficient and support complex DAX calculations across related tables.
Adding New Data Sources to Existing Models
When you need to bring in entirely new data sources (a new database, API, or file) to complement your existing Power BI data model, the process involves connecting the new source, transforming the data, and defining relationships to existing tables.
- Get Data - In Power BI Desktop, click Home > Get Data and select the appropriate connector (SQL Server, Excel, Web, OData, REST API, SharePoint, etc.). Configure connection parameters and credentials.
- Transform in Power Query - Before loading the new data, apply transformations in the Power Query Editor: rename columns to match your naming conventions, change data types, remove unnecessary columns, and apply filters.
- Define Relationships - After loading, navigate to the Model view and create relationships between the new table and existing tables. Match on common key columns (CustomerID, ProductID, DateKey) with the appropriate cardinality (one-to-many, many-to-one).
- Update DAX Measures - Review existing DAX measures to determine if they need modification to incorporate the new data source. Measures using ALL, ALLEXCEPT, or CALCULATE with specific table references may need adjustment.
- Test Thoroughly - Validate that existing reports and visuals continue to work correctly after adding the new source. Check that filters, slicers, and drill-through actions behave as expected with the expanded data model.
Incremental Refresh: Automated Data Addition
Incremental refresh is the enterprise approach to continuously adding new data to Power BI datasets. Instead of refreshing the entire dataset on each cycle, incremental refresh loads only new and modified records, dramatically reducing refresh time and resource consumption.
- How It Works - You define a date range parameter (e.g., load the last 3 years of data, refresh only the last 30 days). Power BI partitions the dataset by date and only refreshes partitions within the refresh window, leaving historical partitions untouched.
- Configuration Steps - Create RangeStart and RangeEnd parameters in Power Query (DateTime type). Filter your date column using these parameters. In the Power BI Desktop model view, right-click the table and select "Incremental refresh" to configure the policy.
- Requirements - The data source must support query folding (the ability to push filter predicates to the source). SQL Server, Azure SQL, Azure Synapse, Dataverse, and OData sources support query folding. Excel and CSV files do not.
- Real-Time Data - Combine incremental refresh with DirectQuery for the latest partition to get near-real-time data. Historical partitions use Import mode for fast performance, while the current partition queries the source directly.
- Licensing - Incremental refresh is available in Power BI Pro (with limitations) and fully featured in Premium Per User and Premium Per Capacity. Premium supports up to 48 refreshes per day and larger dataset sizes.
Managing Data Source Credentials and Gateway
When adding new data to Power BI datasets published to the service, credential management and gateway configuration determine whether scheduled refreshes succeed.
- Cloud Data Sources - Azure SQL, SharePoint Online, Dataverse, and other cloud sources authenticate directly through the Power BI service using OAuth or service account credentials configured in dataset settings.
- On-Premises Data Sources - SQL Server, Oracle, file shares, and other on-premises sources require an on-premises data gateway. Install the gateway on a server with network access to your data sources and configure data source credentials in the gateway admin portal.
- Credential Updates - When adding a new data source to an existing published dataset, you must update the data source credentials in the Power BI service (Workspace > Dataset settings > Data source credentials) before scheduled refresh will work.
- Gateway Clustering - For enterprise deployments, configure gateway clusters with multiple gateway members for high availability and load distribution across data sources.
Why Choose EPC Group for Power BI Data Management
EPC Group has designed and optimized Power BI data models for Fortune 500 organizations for since 1997. As a former Microsoft Gold Partner (2000 to program retirement, the oldest in North America) and current Microsoft Solutions Partner, our Power BI practice brings deep expertise in data modeling, Power Query optimization, incremental refresh, and enterprise dataset management. Our founder, Errin O'Connor, authored the bestselling Microsoft Press book on Power BI, making EPC Group the authoritative partner for enterprise analytics implementations.
- End-to-end Power BI data model design from source analysis through production deployment
- Incremental refresh configuration that reduces refresh times by 80-90% for large enterprise datasets
- Complex data integration patterns connecting 10+ data sources into unified analytics models
- Performance optimization that delivers sub-second query response times on billion-row datasets
Frequently Asked Questions
What is the difference between Append and Merge in Power BI?
Append adds rows (vertical combination) by stacking data from two or more tables with the same column structure. It is like adding new records to an existing table. Merge adds columns (horizontal combination) by matching rows from two tables on a common key, similar to a SQL JOIN.
Use Append when:
- Combining data from the same source structure (e.g., monthly sales files).
Use Merge when:
- Enriching data with attributes from a lookup table (e.g., adding customer region from a customer dimension table).
How do I add data from a new Excel file to my existing Power BI dataset?
Open your Power BI Desktop file (.pbix). Go to Home > Get Data > Excel, and select the new file. In Power Query, review and transform the data as needed.
Next, use Home > Append Queries to combine the new data with your existing table. Alternatively, you can store all Excel files in a single folder. Use the Folder connector, which automatically picks up new files on each refresh without manual intervention.
Can I add data to a published Power BI dataset without using Desktop?
There are several options for managing data in Power BI. You can use Power BI Dataflows to create and modify data transformation logic directly in the Power BI service browser.
- The Power BI REST API allows for programmatic data push to datasets.
- Power BI streaming datasets accept real-time data through API calls.
- For enterprise scenarios, modify the dataset in Power BI Desktop, republish it, and let scheduled refresh handle data loading automatically.
What is incremental refresh and when should I use it?
Incremental refresh is a Power BI feature that refreshes only new and modified data rather than reloading the entire dataset. Use it when your dataset is large (millions of rows), your data source supports query folding, and you have a reliable date/timestamp column for partitioning. It reduces refresh times from hours to minutes, lowers source system load, and stays within Power BI dataset size limits by managing historical data partitions efficiently.
Why is my data refresh failing after adding a new data source?
The most common cause is missing or invalid credentials in the Power BI service. After adding a new data source and republishing, navigate to the dataset settings in the Power BI service and configure credentials for the new data source under "Data source credentials." If the source is on-premises, ensure the on-premises data gateway includes the new data source in its configuration. Also verify that the gateway service account has appropriate permissions to access the data source.
// Append two queries with the same schema:
let
Source = Table.Combine({Sales2024, Sales2025})
in
SourceRelated Power BI & Microsoft guides
Related Resources
Continue exploring power bi insights and services