Power Query: Consolidating data from different worksheets

[reddwarf]

See attached mock-up containing 2 worksheets. My actual workbook extends to 30 worksheets and a new sheet is added each month. With the current layout it is hard for me to build reports (I don't own the workbook btw - it is sent to me by Accounts).

How do I consolidate data from different worksheets (30+) that contain the same data structure? The range I need to consolidate is C14:K37 (every other row is blank and I don't need it) and I also need data from cells F7, G9, J6, J9, J11 and E42, E44, E46,K42 and K44.

I looked at Power Query (PQ) but it seems I have to append data by creating tables (is this right?) - can PQ automate a routine to consolidate every worksheet in the workbook

Thanks for looking

[AliGW]

Much as I love PowerQuery, I am not sure that it's going to be the best option in your case with the data layout you have. Shall I move this to the VBA section for you?

[reddwarf]

I posted a thread earlier this week and Olly (without sight of the data structure) thought this solution might be doable with PowerQuery.

https://www.excelforum.com/excel-pro...ml#post5135736

Happy to take advice whatever you think is the best way forward

Thanks

[AliGW]

Your last post suggests that this is the same query as your previous thread. If it is not, then why would Olly's suggestion about PQ have any relevance to this thread? This is all very confusing ...

Based on what I have seen in THIS thread, I am not sure that PQ is the best way.

[reddwarf]

Thanks.
I agree - slightly confusing on my part. Note to self - more haste less speed next time on quick reply

[AliGW]

No worries - we have it sorted now. You may now wish to edit the title of the other thread.

[Olly]

Okay, after all the thread confusion...

Here's a solution to summarise ALL data in one normalised table. It relies on referring to an external workbook, and combines data from all worksheets:

Please Login or Register  to view this content.

Whilst this does normalise your data, it is rather inefficient, as all the dimension attributes of the claim are repeated on every fact row. What would make more sense is to create a data model, with a detailed fact table, and a summary dimension table:

Detail:

Please Login or Register  to view this content.

Summary:

Please Login or Register  to view this content.

Load these to the Data Model (PowerPivot), and create a relationship between them:

Attachment 628502

Now you can report against this data model quite efficiently.

See attached workbook for examples.

In all queries above, I've deliberately left the code somewhat verbose, to make it easier to follow each step. There's a bit of optimisation we could apply, though.

[reddwarf]

Olly,
Yes - confusion all resolved - [insert embarrassing icon ]
In advance, as I am trying to take it all in, but many thanks for your expertise help with the attached Power Pivot / Query solutions.
A quick test on the test data shows everything is working correctly.
I look forward to applying your method to the live workbook when I return to the office on Wed
I will post back by way of update.

Thanks very much - Power Query and Power Pivot is a bit of a game changer IMO

[Olly]

Power Query and Power Pivot is a bit of a game changer IMO

Yes, it really is. I encourage everyone to explore and embrace the possibilities it opens up

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[reddwarf]

I'm running into issues adapting the fsolution provided on the sample data.
My sample data in cells C14:C37 contains naming conventions such as:

Category
Category 1
(blank)
Category 2
(blank)

...but in reality the actual data reads:

Category:
Salaries
(blank)
Training
(blank)

The query fails to return records as it is looking for the term 'category'.

Any ideas?

Thanks

[Olly]

The query, as written, expects the Category column header to be "Category" (as in your sample file), not "Category:" (as in your post above)... you see the difference?

If your table header for this column is actually "Category:", then you just need to change this in the query (four occurrences, in the All Data query)

It's helpful when sample data provided matches actual data as closely as possible...

[reddwarf]

Thanks, and well spotted with "Category:" - learning as I go with Power Query / Pivot which are new ways of doing things for me.

It's helpful when sample data provided matches actual data as closely as possible...

Yes, I was being simplistic about my sample file as the data is sensitive; however, in hindsight, I was too simplistic. For example, the sample file I provided contains Excel tables but the live workbook does not - I don't know why I done that (() ) but Power Query is expecting a table on every worksheet - is it possible to import just a range, say C14:K38 without creating new tables on the live workbook?

This exercise is a great learning curve for me and thanks for all your help, keeping the statements verbose has helped me no-end to follow the process and logic.

[Olly]

The queries as written are getting data from the worksheets, regardless of whether tables exist or not.

As you're learning, every single difference / change in spec can make a difference to how a query needs to be written...! Spaces, punctuation, UPPER / lower case, text vs numbers - these can all make a difference. It can nearly all be handled, but it's hard to write queries for the unexpected.

If you need further advice with this, then I suggest posting a file as close to your real life data as possible - please leave the structure and data types completely unchanged, just adjust the data values as required to your required level of anonymity.

[reddwarf]

See attached anonymised file, containing 2 worksheets, which is representative of the actual live workbook. I have used the code from Post #7

Please Login or Register  to view this content.

I keep getting errors as follows:

Attachment 629092

Thanks for your help (and patience)

[Olly]

There are a number of structural changes to your source sheet (different column positions for fields, different labels used...)

Here's an updated query which works with your attachment in post #15:

Please Login or Register  to view this content.

[reddwarf]

Thank you - worked first time for all worksheets.
Can I ask - do you write the code or does Excel generate it for you, or is it a combination of both? Very impressed