Only import Files in a Timerange from Folder

[rkords]

Hello,

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:

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-" & EMS_Name),
    #"Filtered Rows" = Table.SelectRows(Source, each [Date modified] >= #datetime(TR_Begin_Y, TR_Begin_M, TR_Begin_D, 0, 0, 0) and [Date created] <= #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"

 Thanks for your support.

[KT_Bsmart2gethe]

Hi @rkords,

Filter for the start date and end date should be applied before the invoked step:

Please see below code (see above pic indicate where the code should go):

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],

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)

Source = Folder.Files("C:\Users\user\work\project\EMS-V4-" & EMS_Name),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Filtered Rows" = Table.SelectRows(#"Filtered Hidden Files1", each [Date created] >= StartDate and [Date created] <= 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"

Regards

KT

[v-stephen-msft]

Hi @rkords ,

Have you tried Date/Time Filters after importing the data?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jennratten]

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.   Your script for Transform Sample File should look someting like this:

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.