cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sifar786 Member
Member

Listing complete Files paths from Base directory using multiple search parameters

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.

power query question.JPG

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.

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: Listing complete Files paths from Base directory using multiple search parameters

Hi @sifar786 

(1) you can use Merge columns to merge mutiple columns.

(2) as per my knowledge, there is no such feature currently to achieve this.

Maybe someone else can help you.

 

Best Regards
Maggie

 

sifar786 Member
Member

Re: Listing complete Files paths from Base directory using multiple search parameters

Hi @v-juanli-msft (Maggie),

 

For point 1, could you elaborate on how i can merge the columns, if there are unknown sub-folders in between the filepaths?

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors