Results 1 to 4 of 4

Thread: Power Query to Extract Multiple Tabs from Most Recent Daily Excel Report
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2016
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query to Extract Multiple Tabs from Most Recent Daily Excel Report

    I'm trying to retrieve the most recent iteration of an excel report produced by our IT department on a daily basis and extract multiple tabs broken out by product type and contains sub categorical product data. I've attempted using the interface tools available through PQ and it has produced the following code when using the "import from folder" function:

    Source=Folder.Files("filepath"),
    #"Filtered Rows"=Table.SelectRows(Source, let latest = List.Max(Source[Date created]) in each [Date created] = latest),

    And when I use the "import from excel file" function I receive this code:

    Source=Excel.Workbook(File.Contents("\\filepath\file.xlsx"),null,true),
    Sheet=Source{[Item="SheetName",Kind="Sheet"]}[Data],

    The interface doesn't quite accommodate the integration of these two importing methods but as I ATTEMPT to read the code I feel that it would be possible to string these two methods together to find the most recent iteration of the report and create three separate queries for each of the tabs in the excel report after which I could move on with normal preparation of the data and just refresh as needed.

    How do I make this possible?

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,186
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query to Extract Multiple Tabs from Most Recent Daily Excel Report

    When you have a complex transformation on a single file, then want to apply this to all files in a folder, you can do this

    1. Transform a single file
    2. Turn above into a function
    3. Apply function to entire folder.

    You luck is in - I wrote a blog post about this about a year ago.

    Combine Excel Workbooks with Power Query – Method 1 - Excelerator BI
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  3. #3
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,811
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query to Extract Multiple Tabs from Most Recent Daily Excel Report

    Edit: this is a reaction to post #1, while at the same time post #2 came in, so I'm not reacting to post #2.

    The Source in the first code is a so called navigation table with Excel files, while the Source in the second code is also a navigation table, but in this case with the elements of 1 Excel file that can be used as input for further processing (typically tables and worksheets).

    In order to get from the result of your first code (the latest Excel file) to a navigation table with the elements of that Excel file:
    Remove all columns except "Content" (unless you still need other columns as well).
    Add a custom column with the custom column formula: = Excel.Workbook([Content]).
    This will create a column with a nested table.
    Expand these using the button in the column header.
    Now you can remove the Content column and you have the same situation for the latest file, as the Source step in the second code for the specified Excel file.
    You can save this as a query (with connection only) and use as a basis for your other queries.
    Last edited by MarcelBeug; Jan 26th, 2017 at 01:32 AM.
    Specializing in Power Query (M).
    Using Excel 2007 (Dutch) and Excel 2016 (Office 365 ProPlus) (Dutch/English), both on Windows 10.
    Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry.

  4. #4
    New Member
    Join Date
    Sep 2016
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query to Extract Multiple Tabs from Most Recent Daily Excel Report

    Thank you both for both the excellent suggestions. In the end, I built the first query and just copied the code and pasted into a blank query and augmented as needed for product type and tab name. The data contexture varied slightly so I removed the detect data type from my step list and added it back in and everything worked fine. Thanks again!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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