+ Reply to Thread
Results 1 to 17 of 17

Power Query: Consolidating data from different worksheets

  1. #1
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Microsoft 365
    Posts
    184

    Power Query: Consolidating data from different worksheets

    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
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Power Query: Consolidating data from different worksheets

    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?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Microsoft 365
    Posts
    184

    Re: Power Query: Consolidating data from different worksheets

    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

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Power Query: Consolidating data from different worksheets

    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.
    Last edited by AliGW; 06-16-2019 at 06:13 AM.

  5. #5
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Microsoft 365
    Posts
    184

    Re: Power Query: Consolidating data from different worksheets

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

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Power Query: Consolidating data from different worksheets

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

  7. #7
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power Query: Consolidating data from different worksheets

    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.
    Attached Files Attached Files
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  8. #8
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Microsoft 365
    Posts
    184

    Re: Power Query: Consolidating data from different worksheets

    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

  9. #9
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power Query: Consolidating data from different worksheets

    Quote Originally Posted by reddwarf View Post
    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

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: Power Query: Consolidating data from different worksheets

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

  11. #11
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Microsoft 365
    Posts
    184

    Re: Power Query: Consolidating data from different worksheets

    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

  12. #12
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power Query: Consolidating data from different worksheets

    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...

  13. #13
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Microsoft 365
    Posts
    184

    Re: Power Query: Consolidating data from different worksheets

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

    Quote Originally Posted by Olly View Post

    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.
    Last edited by reddwarf; 06-20-2019 at 08:36 AM. Reason: typo

  14. #14
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power Query: Consolidating data from different worksheets

    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.

  15. #15
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Microsoft 365
    Posts
    184

    Re: Power Query: Consolidating data from different worksheets

    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)
    Attached Files Attached Files
    Last edited by reddwarf; 06-20-2019 at 03:33 PM.

  16. #16
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Power Query: Consolidating data from different worksheets

    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.

  17. #17
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Microsoft 365
    Posts
    184

    Re: Power Query: Consolidating data from different worksheets

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Power Pivot does not load new column added in Power query
    By ibuhary in forum Excel General
    Replies: 12
    Last Post: 02-19-2019, 03:53 AM
  2. Power Query from Folder - BUT not consolidating the top rows
    By Mikael37167 in forum Excel General
    Replies: 6
    Last Post: 12-10-2018, 05:20 PM
  3. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  4. Replies: 0
    Last Post: 04-05-2018, 01:16 AM
  5. Replies: 1
    Last Post: 11-06-2017, 12:42 AM
  6. Replies: 1
    Last Post: 06-06-2017, 11:38 AM
  7. Replies: 2
    Last Post: 10-17-2015, 11:59 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1