Forum Discussion
Solved
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 with all the below data only:-
But when , I merge the sheets, in the Column "A", only Time is there but all the stats are gone and not merged:-
And after merge all that needed data is lost, and only Time stamps are there:-
What wrong , am I doing here?
Br,
Anupam
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.
Now I understand what happened. You set the data type for the TIME_STAMP column to datetime. That errors out all rows that do not contain valid date/time stamps in that column. These errors occur on your statistics rows which display blanks when you load the table to Excel. See picture below.
So, if you want to go with only UI clicks, remove the rows with date/times first. Then set the data types where the TIME_STAMP column now only contains texts. Obviously, you would rename that column at this stage.
But why not learn how to tweak the code as explained earlier. It will be a lot cleaner that way.
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.
Also Riny_van_Eekelen for the,
= Table.LastN ( Excel.Workbook([Content], true, true) {[Item="Sheet1",Kind="Sheet"]}[Data], 8 )
Can I do it using UI?
Br,
Anupam
Hi Riny_van_Eekelen , Thanks attached is the Query I used.
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.
