+ Reply to Thread
Results 1 to 8 of 8

Power Query: Consolidating data from different worksheets

  1. #1
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2013 Pro (work) Office 365 (home)
    Posts
    169

    Power Query: Consolidating data from different worksheets

    Hi,
    I have inherited an Excel spreadsheet and the data needs normalised.
    See attached mock-up where data is provided for 3 months but the actual spreadsheet has data dating back for the past 5 years.
    I would like to transpose the data into a table as I prefer to work with pivot tables for reporting purposes.
    If someone could help me it would be brilliant as it would save me lots of time.

    Thanks
    Attached Files Attached Files
    Last edited by AliGW; 06-15-2019 at 01:27 PM. Reason: Title updated.

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

    Re: Grid to table

    You can use Power Query for this:

    This query returns normalised data, from your grids:
    Please Login or Register  to view this content.
    You can then pivot as required.
    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...

  3. #3
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2013 Pro (work) Office 365 (home)
    Posts
    169

    Re: Grid to table

    Olly,
    I just want to say, thank you very much for your excellent solution and for showing me the potential of using Power Query for consolidating data.
    There is much to learn with how you done it and even with the applied steps I am still trying to take it all in. Thanks also for the instructional video on your site.

    On the subject of Power Query, can it consolidate data, from different worksheets (26), but with the same data structure? The range is C14:K38 (every other row is blank which I don't need) but I also need data from cells F7, J6, J10 and E46. Do you have any pointers on how I can do this - I will have a go at it - and if I need help I will post a new thread.

    Thanks again for your help, really impressive stuff

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

    Re: Grid to table

    Yes, you can combine data from multiple sheets. Post another thread, with another sample workbook including required output, and we'll take a look.

    Don't forget to mark this thread as SOLVED. Thanks.

  5. #5
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2013 Pro (work) Office 365 (home)
    Posts
    169

    Re: Grid to table

    Thanks. I'll have a go at it first and will post another thread if I get stuck, which is bound to happen lol

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,102

    Re: Grid to table

    I have closed your duplicate thread. I suggest you update the title of this thread to better reflect the issue at hand (I've done it for you).
    Ali


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


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  7. #7
    Forum Contributor
    Join Date
    07-06-2009
    Location
    London, England
    MS-Off Ver
    Excel 2013 Pro (work) Office 365 (home)
    Posts
    169

    Re: Grid to table

    Hi AliGW,
    I think there is a misunderstanding here.
    This thread covers a different Power Query scenario, which Olly solved for me.
    In this same thread I asked was it possible to use Power Query to solve a different scenario and Olly thought, without having sight of the data structure, it might be doable.
    To that end, I marked the thread as solved and posted a new thread yesterday, which you now refer to as duplicate?
    Could you unlock my thread from yesterday as they are completely different scenarios.

    https://www.excelforum.com/excel-gen...ml#post5138224

    Thanks

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    59,102

    Re: Power Query: Consolidating data from different worksheets

    OK - you confused the issue yourself! I will explain why in your other thread.

+ 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. Dynamic Pivot Table - 9 Box Grid
    By cd254 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-27-2017, 04:21 AM
  2. Replies: 1
    Last Post: 10-29-2013, 02:11 AM
  3. Replies: 2
    Last Post: 12-15-2011, 04:14 AM
  4. Excel 2007 : Pivot table grid lines
    By rraze in forum Excel General
    Replies: 1
    Last Post: 07-06-2011, 05:27 AM
  5. Export table to text with grid
    By dannyy in forum Excel General
    Replies: 0
    Last Post: 04-26-2009, 05:43 PM
  6. grid from a table
    By whosoever in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-03-2007, 09:31 AM
  7. Pivot Table Lookup Grid
    By MIVELD in forum Excel General
    Replies: 1
    Last Post: 09-13-2005, 01:05 PM

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