Forum Discussion
How to perform excel data query with multiple changing conditions
Added the following step between the Source & SelectedTodaysFolder steps:
NoSystemHiddenFile = Table.SelectRows(Source, each ([Attributes]?[Hidden]? <> true) and ([Attributes]?[System]? <> true) ),Not tested but that should solve the issue - let me know
The text in the folder name and file name will always be the same. Only the date will change with their respectable format.
Thank you!
OK. Couple of add. questions:
#1 Will i.e. '28 DECEMBER 2023' be at the beginning of the folder name?
#2 What exact version of Excel do you run?
#3 Is the target file an Excel file (.xlsx)?
#1 Yes. The format of the folder is as follows: 29 December ###, #####, & ##### with the "29 December" text changing daily to the appropriate date.
Note. The yesterday folder "28 December ###, #####, & #####" will remain in the overarching folder as a archived backup; therefore, the only two folder to chose from are "28 December ###, #####, & #####" and "29 December ###, #####, & #####".
#2 I am running Microsoft Office Professional Plus 2016 and I have no ability to update to an earlier version.
#3 Yes. The target file is a Microsoft Excel Worksheet (.xlsx) with the format as follows: 29 Dec 2024 ######## #####.
Thanks!
In attached file 2 queries + a PARAM sheet
PARAM sheet:
You must update the path according to your folders structure. You'll see it's currently D:\Lorenzo\Downloads\Projects\MarshallTj because my test environment is:
where the 29 DECEMBER 2023 xxx xxx and 28 DECEMBER 2023 yyy yyyy folders are underneath D:\Lorenzo\Downloads\Projects\MarshallTj
Above folders respectively contain:
- 29 Dec 2023 Sample_Workbook.XLSX
- 28 Dec 2023 Previous_Sample_Workbook.xlsx
Queries:
TodaysFile: Selects the .xlsx in the appropriate folder according to today's date
YourQuery: Opens the above workbook. Add the steps you need...
Any question/issue let me know
- Good Evening!
Is there a way I can use Text.Length in the last step of the TodaysFile query to select the correct file? It is getting me to the correct folder aligned with today's date (Thank you so much for the help on this part!); however I have 3 files in that folder named as such:
05 Jan 2024 Tasking List.xlsx
05 Jan 2024 Retasking List.xlsx
05 Jan 2024 FM List.xlsx
I was able named a cell in the PARAM sheet for the file I want and then call it in the using "#FileName" = ExcelWorkbook() >>> "Similar code to your folder path code" and then using the code to query a name that contains "#FileName" and it worked!...however, since "Retasking" and "Tasking" both have "Tasking" in it, it finds both files.
I was wondering if I could name another cell with the number of characters the "05 Jan 2024 Tasking List" file would be as FileNameCharLength, then define it in the code using the same process I used for the filename, i.e., "#FileNameCharLength" = ...., then call it in query using Text.Length somehow since all 3 of those files will have different character lengths?
Thanks!