•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Multiple Row Headers into PQ?
Our take
Merging multiple PDFs with dynamic row headers in Power Query can streamline your weekly data management. When dealing with varying column headers, such as different dates in each document, it's essential to identify the row where the new headers begin. By setting up a query that dynamically recognizes these headers, you can consolidate your data effortlessly. This approach allows for automatic updates to your pivot tables, ensuring your analysis remains current with minimal manual intervention. Let's explore how to achieve this effectively in Power Query.
I have a collection of PDFs I get weekly and I’d like to merge them together into one big query. However, the problem I run into is that dates are used as column headers. So if I batch load a folder of these documents, I see that row 27 for example might be the start of the new column headers. The data would look like this:
Product 4/1 4/2 4/3 4/4 4/5
Apples 1 0 3 2 1
Oranges 1 5 3 2 7
It just changes those dates at the top when a new weekly PDF is cooked up. How do I solve for that in PQ? I’d like to be able to merge them together from a folder and update my pivot tables weekly based on what files are in the folder.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Power Query - Manual Entry and Multiple UsersI have a file with details of open purchase orders. PO numbers are in the rows and there are several columns with various PO details. It is linked via PQ to two other files. It is used by multiple buyers and I'd like to dumb it down as much as possible. I need to be able to do two things that I haven't figured out yet: Each buyer is responsible for updating delivery ETAs on their open purchases. These are updated at different times for a each vendor throughout the week. In the shared file that we previously used, buyers had a sheet for each vendor and we used VLOOKUPS to pull data from these sheets. Obviously, that doesn't work with a PQ chart. Is there a solution to this that I'm not thinking of? I need a column where buyers can manually add notes to each cell. submitted by /u/free-range-human [link] [comments]
- How to streamline Power Query report.Hi, I have. Abasic understanding of PowerPoint Query and am looking for some guidance that will streamline a report I create. Right now I download2 excel files that I upload to PQ to merge the relevant data into 3rd report that I can distribute. I'm currently moving the 2 reports from the download folder into a 3rd reporting folder. Then I open up the excel file I have and open the PQ editor and go to the report 1 and double click the source and replace it with the new excel report I just downloaded and then I repeat this for the 2nd report. Then I let PQ do its thing to create the merged report. I do this weekly. Here are is what I want to accomplish. Can I auto upload the 2 reports from the reporting folder into excel/PQ rather than manually uploading them and how? Can I retain the old report data so I can show week over week changes? Thanks. submitted by /u/ExpertAd4657 [link] [comments]
- Saving PQ file as CSV - Software that I am uploading it to says it sees values in my blank rows?I feel like this is either a simple solution or a complicated one, but I've been looking at it for too long that my brain feels fried. I basically have a file that I've manipulated in PQ. Say it has a header and two rows after the header of data. When I go to upload it into my accounting software, everything uploads fine, but the software thinks there are values in row 4 up to row 7. Is there something that I can do in PQ to clear out all of the rows that aren't being used or is there something else I have to do outside of PQ? submitted by /u/ArugulaFit655 [link] [comments]
- Power Query Help Merging Spreadsheets with Compound HeadersI've been using Power Query to combine tables from multiple workbooks. It's fairly straightforward when every worksheet has exactly the same headers in exactly the same order in the top row. However, I'm trying to merge many different spreadsheets which have "compound headers," meaning each column's unique identifier is the top 4 rows. (Row 1 is the result type, Row 2 is the Cycle Number, Row 3 is the Analyte Name, and Row 4 is the Unit.) To complicate things even more, not every spreadsheet contains data for all the same headers, because sometimes we don't test all the same chemicals, and sometimes we don't test in the same units. See an example of just 4 of the worksheets I'm working with here: https://drive.google.com/drive/folders/1iZpvy7OSmltpduB0DdGKoGRTysHXHu_j?usp=sharing I've deleted all the actual data and replaced it with a blue block, but you can see the layout. I'd like to preserve all the data in the resulting merge. In other words, I don't want to only keep columns that exist in every spreadsheet. I want to keep all columns that appear at least once. The end goal is to be able to filter the table to find all the samples named "CCV" or "RLV" for example and trend their respective data. (We don't always include the data in the sample name in Column B, so adding a column for the source file name would be necessary as well, so that we know which date the data is for.) Please let me know how I might do this with Power Query, or if there's a better way. We have dozens of these files, and we'd really like to avoid copying and pasting all day. Thanks so much for the help. submitted by /u/7Xe3V7Ur [link] [comments]