cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sibrulotte Regular Visitor
Regular Visitor

Loading files from multiple sub folders with the same file ending

Hi, 

our applicative software has an automation that creates a folder every day with a bunch of diffderent files.

I'd like to load only the ones that end with ADDWYG.txt

 

Is there a way to do that?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Loading files from multiple sub folders with the same file ending

Hi @Sibrulotte ,

 

I have created a sample for your reference. Please refer to the steps as below.

 

1. Create a parameter as below in power query.

Capture.PNG

 

2. Filter the file name column based on the parameter.

fil.PNG

 

3. Expand the filterd table to get the excepted result.

3.PNG

 

For more details, please check the M code as below.

let
    Source = Folder.Files("D:\Case\xxxxxxx"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], Parameter1)),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}})
in
    #"Changed Type"

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Loading files from multiple sub folders with the same file ending

Hi @Sibrulotte ,

 

Parameter in power query should help you. Please refer to the online document.

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Sibrulotte Regular Visitor
Regular Visitor

Re: Loading files from multiple sub folders with the same file ending

Thanks for the reply.

I looked into it and not sure how allowing a parameter to be set would help me in this case.

I'll attempt a few things with it but not optimist...

Community Support Team
Community Support Team

Re: Loading files from multiple sub folders with the same file ending

Hi @Sibrulotte ,

 

I have created a sample for your reference. Please refer to the steps as below.

 

1. Create a parameter as below in power query.

Capture.PNG

 

2. Filter the file name column based on the parameter.

fil.PNG

 

3. Expand the filterd table to get the excepted result.

3.PNG

 

For more details, please check the M code as below.

let
    Source = Folder.Files("D:\Case\xxxxxxx"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], Parameter1)),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}})
in
    #"Changed Type"

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Sibrulotte Regular Visitor
Regular Visitor

Re: Loading files from multiple sub folders with the same file ending

Alright, definitly getting the correct usage, but I'm weary: 

In these folders I have a mega ton load of files that I do not want to touch or try to query or this will severlyu slow down my extraction. Will this not do that at the first step?

 

I don't have enough files for the moment but 60 days down the line it would turn to a total cluster.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)