Only import Files in a Timerange from Folder

Question

Hello,&nbsp;i have a huge amount of logfiles in a folder. I only want to import files in a certain timerange, which I can define in a worksheet(TR_Begin and TR_End variables). I managed to filter the files in the sample file query, but in in the transform sample file query I see data from all my logfiles again.Here is the code of my sample file query:&nbsp;let
    EMS_Name = Excel.CurrentWorkbook(){[Name="EMS_Name"]}[Content]{0}[Column1],
    TR_Begin_Y = Excel.CurrentWorkbook(){[Name="TR_Begin_Y"]}[Content]{0}[Column1],
    TR_Begin_M = Excel.CurrentWorkbook(){[Name="TR_Begin_M"]}[Content]{0}[Column1],
    TR_Begin_D = Excel.CurrentWorkbook(){[Name="TR_Begin_D"]}[Content]{0}[Column1],
    TR_End_Y = Excel.CurrentWorkbook(){[Name="TR_End_Y"]}[Content]{0}[Column1],
    TR_End_M = Excel.CurrentWorkbook(){[Name="TR_End_M"]}[Content]{0}[Column1],
    TR_End_D = Excel.CurrentWorkbook(){[Name="TR_End_D"]}[Content]{0}[Column1],
    Source = Folder.Files("C:\Users\user\work\project\EMS-V4-" &amp; EMS_Name),
    #"Filtered Rows" = Table.SelectRows(Source, each [Date modified] &gt;= #datetime(TR_Begin_Y, TR_Begin_M, TR_Begin_D, 0, 0, 0) and [Date created] &lt;= #datetime(TR_End_Y, TR_End_M, TR_End_D, 0, 0, 0)),
    #"C:\Users\user\work\project\EMS-V4-000A\_log_20220127145524" = #"Filtered Rows"{0}[Content]
in
    #"C:\Users\user\work\project\EMS-V4-000A\_log_20220127145524"&nbsp;&nbsp;Thanks for your support.

jennratten · Answer

The source for the Transform Sample File query should one file only (usually determined by a parameter) and should return an error if more than one or none are being returned.&nbsp; &nbsp;Your script for Transform Sample File should look someting like this:

&nbsp;
In the query that invokes the function you will need to apply the folder filters again - those applied in the sample file query are not applied when the function is invoked.&nbsp; &nbsp;
&nbsp;

anonymous · Answer

Hi&nbsp;rkords&nbsp;,
&nbsp;
Have you tried Date/Time Filters after importing the data?

&nbsp;
&nbsp;
&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

kt_bsmart2gethe · Answer

Hi&nbsp;rkords,&nbsp;Filter for the start date and end date should be applied before the invoked step:&nbsp;Please see below code (see above pic indicate where the code should go):letEMS_Name = Excel.CurrentWorkbook(){[Name="EMS_Name"]}[Content]{0}[Column1],TR_Begin_Y = Excel.CurrentWorkbook(){[Name="TR_Begin_Y"]}[Content]{0}[Column1],TR_Begin_M = Excel.CurrentWorkbook(){[Name="TR_Begin_M"]}[Content]{0}[Column1],TR_Begin_D = Excel.CurrentWorkbook(){[Name="TR_Begin_D"]}[Content]{0}[Column1],TR_End_Y = Excel.CurrentWorkbook(){[Name="TR_End_Y"]}[Content]{0}[Column1],TR_End_M = Excel.CurrentWorkbook(){[Name="TR_End_M"]}[Content]{0}[Column1],TR_End_D = Excel.CurrentWorkbook(){[Name="TR_End_D"]}[Content]{0}[Column1],StartDate = #datetime(TR_Begin_Y, TR_Begin_M, TR_Begin_D, 0, 0, 0)EndDate = #datetime(TR_End_Y, TR_End_M, TR_End_D, 0, 0, 0)&nbsp;Source = Folder.Files("C:\Users\user\work\project\EMS-V4-" &amp; EMS_Name),#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? &lt;&gt; true),#"Filtered Rows" = Table.SelectRows(#"Filtered Hidden Files1", each [Date created] &gt;= StartDate and [Date created] &lt;= EndDate,#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Rows", "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")))in#"Expanded Table Column1"&nbsp;RegardsKT&nbsp;&nbsp;&nbsp;

Forum Discussion

Related content

Folder Permissions

Import Folder long path

Filter Old Draft Folders

How to remove headers when importing from folder

Importing data from folder