cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Connecting to Folder but filtering by file name

Hello,

 

We are conecting to a folder which has multiple files -- basically 3 different file formats.  Is there a way when we connect to the folder -- maybe going into 'Advanced' where it shows it in M code -- and filtering by specific file names?  i.e. We have file names "Positions", "Transactions" and "Returns" all in the same folder.  Only want to view where file names begin with 'Position' or end with .xls -- like filtering positions* or *.xls.

 

Thanks!

Dan

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Connecting to Folder but filtering by file name

@dancarr22,

Add a custom column in Query Editor using the following formula, then filter the custom column to "Yes".

if Text.Contains([Name], "Equity") then "Yes" else "No"

You can review the example below.
1.PNG2.PNG



Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

Re: Connecting to Folder but filtering by file name

@dancarr22,

Change the code in your Advance Editor to the following:

let
    Source = Folder.Files("YouPath\folder"),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xls"))
in
    #"Filtered Rows"

Then in Query Editor you are able to view only those files with .xls in Query Editor, there is an example for your reference.
1.PNG


Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper III
Helper III

Re: Connecting to Folder but filtering by file name

Thanks!  This is great -- answer 90% of my question.

One follow-up then will close this: how would I filter by file name instead of file extension -- i.e. All files that begin with or contain '%Equity%'.

 

Thanks for your assistance with this -- greatly appreciated.

Highlighted
Microsoft
Microsoft

Re: Connecting to Folder but filtering by file name

@dancarr22,

Add a custom column in Query Editor using the following formula, then filter the custom column to "Yes".

if Text.Contains([Name], "Equity") then "Yes" else "No"

You can review the example below.
1.PNG2.PNG



Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021