cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RJJ Frequent Visitor
Frequent Visitor

Importing file names from Folder structure using a filter?

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

2 REPLIES 2
v-yulgu-msft Super Contributor
Super Contributor

Re: Importing file names from Folder structure using a filter?

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
RJJ Frequent Visitor
Frequent Visitor

Re: Importing file names from Folder structure using a filter?

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)

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 25 members 790 guests
Please welcome our newest community members: