Use the Append Function in Power BI to combine datasets that have similar columns. Appending tables will stack two data sources on top of each other. Appending in Power BI is equivalent to a Union in SQL. We’ll explore when you should append data and include several tips and tricks to make combing data in Power BI even easier.
We’ll look at how to append datasets together when the column headers match, and what to do when the imported files are different from each other.
Finally, we’ll look at some best practices and complimentary techniques when appending two or more datasets in Power BI.
Table of Contents
Why Appending Data is Important
Appending data in Power BI is similar to the Union Function in SQL. It’s used to consolidate multiple data tables and aligns them when the column names match. It’s an important feature because it allows Power BI developers to work with a single data source at a time.
You should append data source in Power BI to fix a number of potential data problems.
- Data Fragmentation – Power BI makes it easier to work with a single table and avoid fragmentation. A single dataset is easier to manage and maintain in a data model instead of having to manage multiple files and multiple data models.
- Time-based Analysis – Consolidating time-series data into a single table allows time-intelligence. You can easily join the data to a date table and drag and drop it into visualizations to provide consolidated charts and graphs.
- Reporting Efficiency – Data that comes from the same source system typically requires the same data prep and cleaning steps. Appending first and cleaning up data second eliminates the need to do these separately.
- Monthly Reporting – Rather than waiting for report or queries to run YTD, you can use Power BI to consolidate multiple spreadsheets or queries. This means that you can import data more frequently in smaller increments speeding up your process.
- Simplified Data Model – After completing your data prep steps, it’s faster to build a report based off of a single table instead of bringing in multiple tables to combine with complex DAX Formulas.
As you can see, there are a number of benefits to appending data early on in your data prep and transformation process using Power Query.
Let’s look at a couple more considerations before importing data.
What is Column Schema?
Column schema is essentially the blueprint that outlines the structure of a dataset in terms of its columns. It includes information like the column names and data types. Column schema plays a crucial role when you’re appending data from multiple tables or datasets.
Power BI will easily consolidate datasets that have the same column schema but may struggle or create unintended consequences when attempting to combine data that has different column names and data types.
We’ll look at how to combine two tables that have the same column headers, and how to work with data that have different column headers in Power BI.
What is the Append Queries Feature in Power BI?
The append queries feature in Power BI will combine two or more tables by stacking them on top of each other. This function is the same as performing a Union in SQL. You should think of appending data whenever you have multiple tables or Excel sheets with the same type of data.
How to Append Two Datasets in Power BI
The first step to Append two datasets is to import both of them into Power BI. After both tables are available in Power BI follow these steps. Launch the Power Query Editor, navigate to the Home tab of the Power Query ribbon, and click on Append Queries. Select whether to append Queries or Append Queries as New. The Append screen will appear and you can select between appending two tables or three or more tables.
Appending Queries provides two options.
- Append Queries – Combines all datasets into the starting current dataset.
- Append Queries as New – Combines all datasets into a new table. Leaving the original tables as separate tables available in the data model.
After selecting whether to Append Queries or Append Queries as new, Power BI will launch the Append options screen.
The current table is determined by which table is selected prior to pressing the Append Queries button.
Let’s look at an example of combining two tables in Power BI.
How to Append Multiple Tables in Power BI
To append multiple tables in Power BI, first import all datasets into Power BI. Launch the Power Query Editor. From the Power Query editor, Click on Append Queries, select Append or Append as New and select Three or More Tables on the next screen. Move all of the columns you wish to combine to the Tables to Append column by selecting them and pressing the Add >>> button.
After pressing OK, the tables will be unionid together into a single dataset.
There is no limit to the number of tables that you can combine using append queries. Try holding down SHIFT to multi-select tables to move multiple tables to the Tables to Append section at one time.
Example of Appending Queries in Power BI
In the following example, we use a dataset of cookie sales in the Portland, Oregon and Seattle, Washington areas. There are separate tables for each month of sales. By selecting the July table on the left side of our Queries panel in Power Query it is defined as the Current Table.
Example of Append in Power BI
The dataset below applied Append Queries for two tables. July is the current table and August is the one being appended.
As you can see, the August table remains intact but the July table now includes all of the data from the July and August table stacked on top of each other.
Example of Append as New in Power BI
When using Append as New the current and appended tables are left intact. A third new table is created called Append1 by default. This table has the combination of data from the first two tables. Power BI automatically aligns all data based on names of the incoming data schema.
How to Add Data to an Existing Append Query
After appending two ore more datasets in Power BI, you can add additional datasets by either performing a second append function to the new or previously appended table. Follow the same instructions to append and it will walk you through the same steps as previously mentioned. Alternatively, you can select the Appended Query settings in the Applied steps panel of the Power Query Editor.
Once you open the Append options screen, you can add additional data tables to the append query.
How to Append in Power BI when Column Names are Different
When there are different schemas present in incoming data, Power BI will be unable to match the column names and align them. It will typically result in a Column of the Table Wasn’t Found error like the one shown below.
The following are some workarounds to avoid column of the table wasn’t found errors when appending datasets with different column names.
Demote Headers to Append Based on Column Position in Power BI
Prior to appending data in Power BI, delete the Applied Step for Promoted Headers or use the Use Headers as First Row to remove column names. Power BI will assign a generic column name that it can use to union or data together.
The Use Headers as First Rows button is available on the Home Tab of the Power Query Ribbon in the Power Query Editor.
The example below shows two tables that were combined without using column headers. Power BI aligns columns based on column position and avoids an append error.
When Power BI is used to union data with different schemas into a consolidated dataset, it will create a column full of null values. This indicates that there is an extra column when other columns are empty. Adjust the incoming queries in Power Query prior to the append step or prior to importing the data into Power BI.
After the data is consolidated, you can promote headers and apply filters to remove duplicated header lines from appended datasets. We’ll explain how to perform these functions a little later in the article.
If you are combining many different datasets it can be beneficial to disable automatic detection of data types and column headers.
How to disable Power BI from Auto-Detecting Data Types and Promoting Column Headers
To prevent Power BI from automatically detecting data types and promoting column headers, navigate to File > Options and Settings > Options > Data Load > and select Never Detect column types and headers for unstructured sources.
Power BI will no longer detect column names and it will force Power BI to consolidate all data sources based on column position instead of column name when applying an append function.
Transform datasets in Power Query Prior to Appending
Another option is to clean datasets prior to appending them. When you import a new dataset check that the column structure is the same. Consolidate and rename columns as needed to match the schema of the first table that the data will be appended to. The first table is referred to as the current table in Power BI and it’s used as the template for all subsequent datasets.
Prep Data in Excel Prior to Importing into Power BI
Typically column names change on datasets that come from Excel files, or reports prepared by a different department. Whenever possible, we recommend using data direct from a source system to maintain consistency. However, it can be faster to open a file and adjust it prior to importing it into Power BI than it is to apply data cleansing steps if you’re already an Excel expert.
Removing Duplicate Rows in Power BI
After appending data in Power BI a common function is to remove duplicate rows. To remove duplicate rows in Power BI right click on a column header in Power Query and select Remove Duplicates from the Remove Rows menu
Check out our guide to handling duplicates in Power BI for more advanced techniques to handle and remove duplicates using Power Query.
Sorting and Filtering Appended Datasets
Once data is appended, click on the down triangle at the top of a column to Sort and Filter the Column. A filter can be applied to unselect duplicated column headers. It may also be beneficial to sort the appended data to make sure that it is appended correctly. You can visually inspect data that is sorted by date or by category.
After data is prepped you can close and load the Power Query and the appended data will become available in your report view. Consolidated datasets are much easier to work with when building reports and dashboards.
Power BI Append vs Merge
Another way to consolidate data in Power BI is the Merge Function. While Appending stacks tables on top of each other, a Merge will combine tables by placing one on the left, and another on the right to align rows based on a common field value. A merge is the same as a SQL join, or similar to using a VLOOKUP in Excel to bring additional columns into a dataset from another dataset.
The following video further explains the difference between append and merge in Power BI.
Conclusion
Union and Append data in Power BI to make building reporting dashboards faster and easier. You can Append data in Power BI using the Append function. Data can either be appended to a starting data table or you can consolidate multiple tables into a new table leaving the initial data intact.
Power BI will only append data when the column names are the same. To avoid errors data has to be normalized with standardized column headers prior to the append function. Data prep can either be performed directly in Power Query or can be completed in a source system such as Microsoft Excel prior to importing the data into Power BI.
After the data is consolidated, apply any necessary steps such as filtering out column names or headers that exist across data tables, or sort the data so you can more easily manually review it prior to importing it into the report building view for charts, graphs and other visualizations.
Related Articles
- How to Calculate Percentage Change in Power BI
- How to Remove Rows and Promote Headers in Power BI
- How to Find and Remove Duplicates in Power BI
- How to Schedule Power BI Automated Emails and Reports
- How to Add or Remove Blank Rows in Power BI Matrix Visuals
- A Comprehensive Guide to Power BI AI Features