Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
2. Filter the file name column based on the parameter.
3. Expand the filterd table to get the excepted result.
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"
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/
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...
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.
2. Filter the file name column based on the parameter.
3. Expand the filterd table to get the excepted result.
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"
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.