Multiple Excel Sheets for a Rolling Historical.

What I would do is use the file name to create a date column then filter on the date column to be within the last two years.

For an example I will use a folder that only creates files on the 1st day of every quarter but this will work with any amount of files

So I have a folder with the following files

2018-01-01.xlsx

2017-10-01.xlsx

2017-07-01.xlsx

2017-04-01.xlsx

2017-01-01.xlsx

2016-10-01.xlsx

2016-07-01.xlsx

2016-04-01.xlsx

2016-01-01.xlsx

2015-10-01.xlsx

2015-07-01.xlsx

2015-04-01.xlsx

2015-01-01.xlsx

I would then duplicate the column and transform it such that it becomes a date column. Then filter this date column so the date is either in the last 24 months or in the current month

Once you have these files you can then combine the binaries in the column and get the combined data.

I havent done this set as my example doesnt have a binaries column. This does rely on every file having the same internal structure and the file names following a set structure

I have put the power query code below so you can see the example. To view this copy and paste the code into a blank query via the advanced editor in the Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNA1MAQivYqc4gqlWB2wmLmuoQGmmIE5FjETLGIY5plhMc8Mi3lmWMwzw2KeKRbzTLGYZ4rFPFMU82IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FileName = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"FileName", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "FileName", "FileName - Copy"),
    #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"FileName - Copy", "Date"}}),
    #"Extracted First Characters" = Table.TransformColumns(#"Renamed Columns", {{"Date", each Text.Start(_, 10), type text}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Extracted First Characters",{{"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each Date.IsInPreviousNMonths([#"Date"], 24) or Date.IsInCurrentMonth([#"Date"]))
in
    #"Filtered Rows"

I hope this helps.

HI Anonymous,

So you want to know how to get data from excel files(binary type) and combine them, right?
If this is a case, you can refer to below formulas:

1. Add column to get data from contents and merge detail tables in each rows:

#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Table.Combine(List.Transform(Excel.Workbook([Content])[Data], each Table.PromoteHeaders(_, [PromoteAllScalars=true]))))

2. Transform above column to table.

    Custom1 = Table.Combine(#"Added Custom"[Custom])

Regards,

Xiaoxin Sheng