Re: Listing complete Files paths from Base directo...

Anonymous · ‎10-04-2019

I have a Table named tblBase which shows a BasePath , ClientName, Year. The ClientName will be typed in by user and the Year selected from a dropdown. Together these three will form a concatenated Base Directory i.e. BasePath\ClientName\Year\. As indicated, BasePath will be static, but ClientName and Year will be User inputs or selections.

Using this Base Directory, i want to search specific files listed in below table named tblFiles, such that, part of their sub-folder names are known and others unknown. The known ones are listed below in the table at their particular position from the Base Directory.

e.g.

test1.csv may have a filepath like \\BaseDirectory\{unknown sub-folder}\branding\{unknown sub-folder}\{unknown sub-folder}\

test2.csv may have a filepath like \\BaseDirectory\aesop\{unknown sub-folder}\markus\geneva\

test3.csv may have a filepath like \\BaseDirectory\{unknown sub-folder}\financials\{unknown sub-folder}\res\

test4.csv may have a filepath like \\BaseDirectory\model sim\{unknown sub-folder}\{unknown sub-folder}\markup\

1] Using this information, how can i list out the complete File paths in the Filepath column? Eventually i want to pull these files into Power Query again for further transformations.

2] What if - for some files, i just know the static sub-folder names in their Filepaths, but don't know their actual positions? In such a case, how can the power query code be made more dynamic?

As a note - these files may appear in other sub-folders, other than the hierarchy suggested in above table. So those need to be excluded from the search.