Power Query: Extract data from cells to build a table of data for analysis

[reddwarf]

Hi,
It would be brilliant if this can be done as it would be a real time saver.

See attachment. I have a report on Sheet 1 (table copied from Word). I am trying to extract data from the report to build a table to analyse data. I have 80+ of these reports, all the same layout, each on different worksheets. I have started a Power Query and I can extract data to a table but I don't know how to re-organise the data, from this:

SECTION 4
Section 4, Text 1
Section 4, Text 2
Section 4, Text 3
Section 4, Text 4

to this:

SECTION 4 Section 4, Text 1
SECTION 4 Section 4, Text 2
SECTION 4 Section 4, Text 3
SECTION 4 Section 4, Text 4

And how can PQ extract data from other sheets to do the whole workbook.

Thanks for looking

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do with PQ.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[reddwarf]

Hi Ali,
Apologies. I have amended thread title to summarise what I am trying to do with PQ.

Regards

[AliGW]

Much better - thank you.

I had a look at your workbook. Can you give a clearer idea about what you mean by this:

I have 80+ of these reports, each on different worksheets, and it would be great if someone could help me get it over the finish line.

What does the "finish line" look like? Are we talking about 80+ tables, or is this a consolidation task?

I also noticed that you filtered on a date in your PQ - how would this work going forward? Would the query need to b smart enough to know what date to filter to?

More questions than answers, I am afraid.

[reddwarf]

Thank you Ali,
Yes, fair comment on questions - I need to think of my audience a bit more carefully next time.

1) This is a consolidation task, so only one table but encompassing 80+sheets
2) I didn't intend to filter on date but I need PQ to return the date of each report.

[AliGW]

OK - I understand.

So, the good news is that it's easy to load and append lots of tables in a workbook using the =Excel.CurrentWorkbook() command in a blank query and taking it from there.

It would be really helpful if you could provide a version of the workbook that contains three DIFFERENT tables so that we can come up with a solution that will work for you.

[reddwarf]

Ali,
Just to clarify. The date of each report is located on A6.

Thanks

[AliGW]

Yes, thanks. Please see my request for an updated sample workbook - to clarify, please add two more tabs with tables (different ones) in the exact format they are in the original workbook.

[reddwarf]

H Ali,
Thanks for looking at this.
New workbook attached with 2 new worksheets. The data I am working with is confidential but I have added in related dummy text to convey how reports work. Basically, a new report is created daily and changes are marked in red font. Sections can grow and shrink depending on daily updates though I have never seen more than 15 items per section.

[AliGW]

OK - I'll have a look later (unless one of the PQ wizards gets in first). I've run out of time for now.

[Bo_Ry]

Open Power Query/Get and Transform. Click on New Query.
Open blank query in the editor, launch Advanced Editor and paste in the following code.

Please Login or Register  to view this content.

[reddwarf]

Hi Bo_Ry,
Thank you for reply. Very impressive and much appreciated.
This looks good, 90% there, but I was wondering if you can help me on a couple of points.

1) See post #5. The query is filtering by date but each worksheet has a different date (A5), so how do I remove the date restriction and add a column to show the date of the report?
2) How do I change the source so it picks up all worksheets - currently it is just looking at Sheet3? After a bit of research, following Ali's advice, I added:

Please Login or Register  to view this content.

...but it runs into the date error: "Expression.Error: The column 'Date 06.05.2020' of the table wasn't found."

Thanks

[Bo_Ry]

Excel.CurrentWorkbook() only get Table, not sheet

I Create Function from #11
Plaese change File path in blue

Please Login or Register  to view this content.

[reddwarf]

Thank you for updating the query - it's an amazing what can be done with PQ - I'm learning all the time . I am going to study the code to work how to add a column with the date of the report (A5) - any pointers? Thanks

Also, is there any way PQ can pick up all worksheets within the workbook without using function and hard-coding a path? If I use Excel.CurrentWorkbook() I take it PQ will automatically add all tables to the Query, so that means I have to create tables on each worksheet? The reason being I need to email this document to staff and it needs to be kept simple for them to use as they will not know how to change path and use function, etc.


Appreciate your time and expertise.