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
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
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!
Hi marshalltj67
...since all 3 of those files will have different character lengths?
Well, if the targeted file name is known (i.e. '06 Jan 2024 Tasking List' = 24 chars) you don't need an additional parameter, a constant (24) is enough
In the attached query I updated step SelectedTodaysFile as follow:
SelectedTodaysFile = Table.SelectRows(FilteredExtension, each Text.StartsWith([Name], FileStartsWith) and Text.Length([Name]) = 24 + 5 )(+5 as the [Name] includes the Extension. Extension length = 5 when the file is of type .xlsx)
Makes sense? Does the job or do you need something more 'sophisticated'?
- That worked perfect thank you!
The only thing I noticed was if there was an autosave file that is the same number of characters length that was queried. Is there a way to only query "real" files? For example, ~$05 Jan 2024 Tasking List.xlsx which is 26 + 5 (31) characters which is the same as 05 Jan 2024 Retasking List.xlsx which is 26+5 (31) characters.
I can definitely work around it and add a Text.Contains for "Retasking" vs "Tasking" but I wanted to see if you knew how to get around autosave or hidden files?
Thanks!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