Log In
Register
Home
September 14, 2021
Offline
Good day,
I have a file that has sections separated by blank rows and merged columns in one worksheet.
want to use PowerQuery to pull out specific sections of the report based on a title of the specific cell reference and stop when there is a blank row.
Then repeat this process for a subsequent section in the worksheet.
Please see attached pic for details.
[Image Can Not Be Found]
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Jason,
Nothing attached.
Rather than a picture, can you create a file with sample data? Will be much easier to help you.
Trusted Members
October 18, 2018
Offline
crossposted: https://www.mrexcel.com/board/.....e.1181744/
September 14, 2021
Offline
Please see attached
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
In the attached file, change the path to your sample file in the "Test" query.
Basically, the idea is to add a new column to identify the target headers, fill down that column then remove nulls. Grouping by that custom column will create a table with 2 rows, with data separated for Failed and Manual. The tables are extracted into 2 separate queries loaded to sheets.
Next time, please don't forget to add links to other sites where you have posted your question, nobody wants to waste time solving a problem that was already solved elsewhere, cross posting may potentially be a free time waste for many enthusiasts.
Welcome to our forum!
September 14, 2021
Offline
This was very good information. Thank you
I found Ruth's video that helped me create an index column and use the following to dynamically find "Failed Transactions" - the first section of the report that I need. I'll promote this row to headers.
Dynamically find header rows and promote them in Power Query - YouTube
I'll promote this row to headers.
My current issue now is this...
I need to keep the data under "Failed Transactions" until the FIRST blank row, AND then delete all the cells after this first blank row ("Manual Transaction" will come after this blank row, which I want to delete all these rows and I'll make this section its own query as you suggested).
Any suggestions?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
This was very good information. Thank you
Have you seen the file attached to my previous message? It has the solution as well,. not just "good information", why would you need other suggestions?
September 14, 2021
Offline
Oh my goodness. I failed to review the attachment.
This is just incredible!!!
You just made someone incredibly happy
This is my first time using a forum. I'm the biggest fan you have now.
Thank you, thank you, thank you!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Great, glad to hear you're happy.
Please mark the topic on other forums where you posted this question as solved, to stop them from spending time on your question.
September 14, 2021
Offline
- Sorry about the "cross" questions. I had no idea that the forums are so related. I'll make sure not to do that again.
Again, you are tremendously appreciated!
September 14, 2021
Offline
Follow-up question:
I understand now how you created the "Test" query.
But how do I create the two new queries for "Failed" and "Manual"?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Just start a new blank query with this formula:
= Table.SelectRows(Test, each _[Custom]="Failed")
(replace "Failed" with "Manual" for the other one)
Then apply the other steps I did in the sample file.
1 Guest(s)
