I want to load the latest excel file into my script from a specific folder.
The filename is "DAC_GROUP_2020-03-07T1100"
I am not able to finish the script for it to load the latest file.
This is how the script looks like right now:
Table_Temp:
Load FileName() as File_Name,
(right(FileName(),20)) as Test
// Date(Date#(Right(FileName(),8),'YYYYMMDD'),'DD/MM/YYYY') as DateField
FROM [lib://Dropbox (dm1_qsuser)/AllClients\Simplifi\DAC_Group_*.xlsx]
(ooxml, embedded labels, table is sheet1);
Table_Temp2:
Load
Date(Date#(Left(Test,10),'YYYY-MM-DD'),'DD/MM/YYY') as Date
Resident Table_Temp;
drop Table Table_Temp;
Min_Max:
Load Max(Date) as MaxDate,
Min(Date) as MinDate
Resident Table_Temp2;
LET vMaxDate = Peek('MaxDate',0,'Min_Max');
Final:
Load * FROM [lib://Dropbox (dm1_qsuser)/AllClients\Simplifi\DAC_Group_$(vMaxDate).xlsx]
(ooxml, embedded labels, table is sheet1);
Can someone please help?
