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

bkjohn2016

New Member
Joined
Sep 9, 2016
Messages
38
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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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:
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top