Append in Power Query [Power Query in Excel and Power BI Series - Ep. 2]

In this episode of Pragmatic Works' Power Query series, Allison Gonzalez, a Microsoft Certified Trainer, takes us through the process of appending queries in Power Query. The append function is a simple yet powerful way to combine data from multiple sources into one unified dataset. This process is especially useful when dealing with similar reports, such as monthly, weekly, or annual reports, where the structure remains the same but the data volume varies.

What is Append in Power Query?

Append in Power Query is used to combine two or more datasets that have the same column structure into a single dataset. This can be useful when working with separate files or tables that need to be merged into one for further analysis. The append operation stacks rows from different tables or files together, making it easier to work with larger datasets that span across multiple reports.

Why Use Append?

• Combine Reports: Combine different monthly, weekly, or annual reports into one dataset for easier analysis.
• Maintain Consistency: Ensure that data from different sources with similar column structures can be combined seamlessly.
• Improve Data Organization: Organize and simplify data management by consolidating data into a single table.
• Enhance Reporting: Analyze larger datasets in a single report rather than separate smaller reports.

How to Append Data in Power Query

Allison demonstrates the append function using two sample datasets: credit card complaints and student loan complaints. Both datasets have the same column structure, making them perfect candidates for appending.

Here’s how you can append data in Power Query:

1. Load Data into Power Query: Start by loading the data sources (Excel or CSV files) into Power Query using Power BI or Excel.
2. Check Data Consistency: Ensure the datasets have the same column structure. You can make adjustments in Power Query if necessary.
3. Choose the Append Option: Once your datasets are ready, select the append option. You can append queries as new or append to an existing query.
4. Verify Data Merge: After the append operation, verify that the rows from both datasets are now combined in a single query.
5. Clean Up Data: Optionally, remove any unnecessary sources or columns to avoid redundancy and improve data performance.

Append as New vs. Append Queries

There are two options when appending data:

• Append Queries: This option adds one dataset to an existing query, combining them into one. The original data sources remain separate.
• Append as New: This option creates a new query that combines both datasets into one, keeping the original datasets intact for easier tracking and data management.

Final Steps After Appending

After performing the append operation, it is essential to check the results and clean up your data. You can rename the new combined query to make it more meaningful, like "Complaints," and disable the load for the original data sources to avoid loading them again into Power BI or Excel.

In the video, Allison also highlights how you can use the query dependencies view to trace the original data sources and ensure that the append operation was done correctly. This helps keep the data lineage intact and avoids unnecessary duplicates.

Conclusion

The append function in Power Query is a great way to simplify your data analysis process. By combining multiple datasets with the same column structure, you can streamline your reporting and make it easier to work with large volumes of data. Whether you're working in Power BI or Excel, the process remains consistent, making it an essential skill for data analysts and business professionals.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Excel 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.