+ Reply to Thread
Results 1 to 14 of 14

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

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

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

    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
    Attached Files Attached Files
    Last edited by reddwarf; 05-16-2020 at 05:03 AM. Reason: update

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

    Re: Power Query help

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


    Enthusiastic self-taught user of MS Excel who's always learning!

    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.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

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

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

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

    Regards
    Last edited by reddwarf; 05-16-2020 at 05:01 AM. Reason: Uodate

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

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

    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.

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

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

    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.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

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

    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.

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

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

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

    Thanks

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

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

    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.

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

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

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

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,641

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

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

  11. #11
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    3,636

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

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

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

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

    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

  13. #13
    Forum Expert Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    3,636

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

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

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

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

    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.

+ 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. Power Query / Power pivot : M2M relationships problem
    By almourasel in forum Excel General
    Replies: 6
    Last Post: 03-26-2020, 05:33 PM
  2. [SOLVED] Power Query - excel formula translation into Power Query
    By afgi in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-19-2020, 03:38 AM
  3. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  4. Excel Power Query Refresh or Access Query - 2nd Query Run is faster
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-03-2020, 10:16 AM
  5. [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
  6. 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
  7. Replies: 0
    Last Post: 04-05-2018, 01:16 AM

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