Importing file names from Folder structure using a filter?

[RJJ]

Is it possible to import file names from a huge shared folder structure? Without actually read all files, but filtering names in the reading process? For instance using a wildcard character * or a regular expression:  Source folder  = ‘F:\part1a\*\part3a\*\customerfiles’ ?

[v-yulgu-msft]

Hi @RJJ,

In Power BI, you can use Query Parameter to achieve your goal. The Query Parameter can be used in Power Query (Query Editor->Advanced Editor).

For more details, please follow below links:

Power BI Desktop Query Parameters, Part 1

Creating M Functions From Parameterised Queries In Power BI

Best regards,

Yuliana Gu

[RJJ]

Thanks a lot for your response! The articles you pointed to may help, though I haven’t seen yet how query parameters can help in this case. Just to give some more information about the challenge: Have a file structure; basically it is constructed in this way:

• Level 1. folders: names of sale areas, a total of 300 sales areas, static
• Level 2.  folders: names of sale sites, 5 to 200 per area, nearly static
• Level 3. Folders: static and fixed categories, a total of 12 categories, but only category 3, 6 and 9 will have customer data files that shall be investigated by the Power BI model.
• Level 4 keeps the customer data files to be used in the model, all together possible 500.000 – 1.000.000 files. Dynamic, files come and go.  Level 4 also keeps more subfolder structures that shall not be used in the model.

The initial plan was to read everything from top level folder, then filtering by level 3 folders, and observing how it performs in Power BI. However, in category1, level 4, there are subfolder names giving the following response in Query editor:

Apply query changes: 
Failed to save modifications to the server. 
Error returned: 'OLE DB or ODBC error: [DataSource.NotFound] File or Folder: We couldn't find the folder 
'.... very long subfolder name, not recognized by Windows, put in there by some 3th party software'

Consequently: the fixed categories on level 3 that is not used, should be filtered away before reading from disk giving this 'OLE DB or ODBC error’.

(The fallback plan is to use a script generating  the list of folders and file names to be analysed in the model, but wanted to avoid it since more components in the solution will increase the overall complexity)