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