I have a bunch of files that I want to use Power Query with. The files have two headers that are not on subsequent rows. What I would like to do is move the first header line along with the one row of data below them to the beginning of the row with the second row of headers. I'm a noobie at Power Query and I've watched a lot of videos but I have found anything to address this specific issue. Any help would be greatly appreciated.
This:
| Header 1 | Header 2 | Header 3 | Header 4 | Header 5 | |
| data | data | data | data | data | |
| Header 6 | Header 7 | Header 8 | Header 9 | Header 10 | Header 11 |
| data | data | data | data | data | data |
| data | data | data | data | data | data |
| data | data | data | data | data | data |
To this:
| Header 1 | Header 2 | Header 3 | Header 4 | Header 5 | Header 6 | Header 7 | Header 8 | Header 9 | Header 10 | Header 11 |
| data | data | data | data | data | data | data | data | data | data | data |
| data | data | data | data | data | data | data | data | data | data | data |
| data | data | data | data | data | data | data | data | data | data | data |
One way would be to have two queries. The first (let's call it Query1) gets the data from the workbook, then just keeps the top 2 rows.
The second gets the data from the workbook, then removes the top 3 rows (assuming there is a blank row between the data sets as in your example), then adds a custom column using the formula =Query1 which will add the table from the first query to every row of the second. Then simply click the expand table button at the top of the custom column.
Thank you so much!!!
I was getting close but needed that custom column with =Query1 and expand table.
Glad we could help. 🙂
I have a similar situation where I am trying to make a unique payroll information table, like company code, dept, and pay code. This table pulls from multiple files for different company codes and new files added every payroll. Just like the original post, I have two headers of information, needing to pull the company code and pay date from Header 1 and Header 2, in my case, to Headers 6-10. Following the solution, I could do this for one file. But when I use the Data source From Folder and tried this solution, I didn't get the correct results. I first tried putting one file in the Folder and made the 2 queries in the Helper Queries section, making some modifications there like removing unnecessary columns, etc. For one file, it worked, but after adding more files, I didn't get the right output. Query1 gets the Company Code and Pay Date, and Query2 gets the dept and paycode. When I added more files, it seemed to "append" the Company Code and Pay Date from the new files to the same dept and paycode from the original source file. So not sure how to "append' Query1 header date to Query2 date but for each file only, and still be able to add newer files as payrolls are produced.
Hope this makes sense and any help is appreciated! If I need to make a new thread, please let me know. I have used PQ in the past but it has been a couple of years.
I have attached files to try to give clarity to my request. There are 3 sample Payroll files and a Desired Output file. My attempts fail in that when I try to add the Employer Number column to the Merged column of Distribution and Large Account, I don't get just that unique combination of that particular file's relationship of Employee number to the Merged Column.
Hope the samples help.
Hi Steve,
For best results please start a new thread with your question. You can link back to this question in your new post.
Mynda
