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 TEXT TEXT and the file is named 28 Dec 2023 TEXT TEXT TEXT and it get updated every day. 

 

I got it to work with the consistent name format and I am able to refresh it; however, that file will then be replaced with a different file tomorrow and the folder will be renamed. 

 

I am using an older version of excel but have access to Get External Data - From Other Sources - From XML Data Import and New Query - From File - From Workbook. I should be able to use Power BI as well..

 

Thanks!

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

16 Replies

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

Resources