Forum Discussion

marshalltj67's avatar
marshalltj67
Brass Contributor
Dec 28, 2023
Solved

How to perform excel data query with multiple changing conditions

Hello All,   I was looking to see if it is possible to perform a data query for a file that changes daily within a folder that changes daily... For example the folder is named 28 DECEMBER 2023 TEXT...
excel
  • Lorenzo's avatar
    Lorenzo
    Jan 06, 2024

    marshalltj67 

     

    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

Lorenzo's avatar
Lorenzo
Silver Contributor
Dec 28, 2023

Hi marshalltj67 

 

Today folder name: '28 DECEMBER 2023 ABC DEF GHI'
Today file name: '28 Dec 2023 Hello World'

 

IF tomorrow folder name is: '29 DECEMBER 2023 DAVID BOWIE'
AND tomorrow file name is '29 Dec 2023 Something Else'

 

then that's doable. A min. of consistency is required to put in place a logic...

marshalltj67's avatar
marshalltj67
Brass Contributor
Dec 28, 2023

Lorenzo 

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!

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 28, 2023

    marshalltj67 

     

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

    • marshalltj67's avatar
      marshalltj67
      Brass Contributor
      Dec 29, 2023

      Lorenzo 

       

      #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!

       

       

      • Lorenzo's avatar
        Lorenzo
        Silver Contributor
        Dec 29, 2023

        marshalltj67 

         

        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

Resources