How to use Power Query to Combine Multiple Files that have different headings | Summary and Q&A

90.9K views
February 5, 2022
by
Access Analytic
YouTube video player
How to use Power Query to Combine Multiple Files that have different headings

TL;DR

With a simple tweak to the code, you can consolidate multiple files in Power Query and bring in all columns, even if the files have different headings.

Install to Summarize YouTube Videos and Get Transcripts

Questions & Answers

Q: Why does the standard method of consolidating files in Power Query not bring in all columns if the files have different headings?

The standard method of consolidating files in Power Query only expands columns from the sample file, which is typically the first file in the folder. As a result, any columns that are unique to other files are not included in the consolidation.

Q: What is the solution to ensure that all columns are brought in during consolidation?

The solution involves using a modification to the code that uses the "list.transform" function to gather the column names from each table and then the "list.union" function to create a list of unique column names. This ensures that all columns are included when expanding the data.

Q: How can you modify the code to bring in all columns during consolidation?

To modify the code, you need to wrap the existing code in a "list.transform" function, using "each table.column name" to gather the column names from each table. Then, use "list.union" to create a list of unique column names. This modified code will bring in all columns during consolidation.

Q: What is the benefit of using the modified code to consolidate files in Power Query?

The benefit of using the modified code is that it allows you to bring in all columns from multiple files, even if the files have different headings. This ensures that no data is left out during the consolidation process.

Q: Are there any limitations or considerations when using the modified code?

One limitation is that the modified code may be difficult to remember, so it is recommended to save it for future use. Additionally, if you need to format the columns after consolidation, you may need to reference them or use other techniques, which may require additional steps.

Summary & Key Takeaways

  • The standard method of consolidating multiple files in Power Query may not bring in all columns if the files have different headings.

  • By using a solution from a blog post by Gil Reiv, you can modify the code to ensure that all columns from all files are brought in during consolidation.

  • The modification involves using the "list.transform" function to gather the column names from each table and then using "list.union" to create a list of unique column names before expanding the data.

Share This Summary 📚

Summarize YouTube Videos and Get Video Transcripts with 1-Click

Download browser extensions on:

Explore More Summaries from Access Analytic 📚

Summarize YouTube Videos and Get Video Transcripts with 1-Click

Download browser extensions on: