Forum Discussion
anupambit1797
Mar 04, 2025Iron Contributor
Power Query Combine Worksheet
Dear Experts, I have many sheets(>10) sharing only 2 here as an example. Each sheet has a data summary in the end as below, and I want to merge all the sheets into a Single one w...
- Mar 05, 2025
Tried with your files and experienced no issues. But you don't give us the query you created. I assume it's the standard Files From Folder route you took. Creating a parameter, a sample file, a function etc. Did that and all rows were included in the combined table, so that I could filter only the eight row headers (i.e. not the time stamps. But perhaps when you combine all files (more than 10, you say), perhaps you can't see the eight relevant ones in the filter.
Better to skip the fully automatic From Folder process. Try this instead:
Connect to a Folder as usual, filter only the files you need and then DO NOT press the combine button in the Content column! Remove all columns except for Content and Name. Now add a custom column as follows.
= Table.LastN ( Excel.Workbook([Content], true, true) {[Item="Sheet1",Kind="Sheet"]}[Data], 8 )
Reading from the inside out, this will extract the data from Sheet1 from the binary content (with headers promoted) and select the last 8 rows. Now remove the Content column and Expand the Custom column. The result is one table with the last 8 rows of each table.
Kidd_Ip
Mar 05, 2025MVP
Please consider on below:
1. Data Range Selection
- Make sure that Power Query is referencing the correct range in each worksheet. If the summaries you need are at the end of the sheets, they might not be included if the data range is incorrectly identified.
- Open each query in the Query Editor to confirm it captures the correct section of your data.
2. Headers Misalignment
- Check if your sheets have consistent headers in the columns you're trying to merge. If not, Power Query might incorrectly map or ignore some columns.
- You may need to rename headers or transform the data so that column names match across all worksheets.
3. Appending Queries Correctly
- When combining data, use the Append Queries function instead of Merge Queries, as merging requires a matching key between sheets.
- Go to Home > Append Queries to stack your data vertically without losing rows.
4. Dealing with Blank Columns
- If Power Query only retrieves the "Time" column, it’s possible that other columns are blank or filtered out during the process.
- In the Applied Steps pane of the Query Editor, inspect and ensure no steps are removing the other columns.
5. Adjust Output Format
- Check the final query output. You can pivot or unpivot the data if necessary to match your desired format.