power query | dynamic selection of source files

Hi, logic could be semilar. Preserve parameters YearFrom and YearTo. Then it is not necessary to combined it twice (months first and years afterwards) - just do it once is enogh.

You should store monthly files wit names:

• 2101 for January 2021
• 2102 for February 2021
• 2103 for March 2021
• etc...

You can see step Ad_FileYear where I combined prefix "20" with first two characters of name of each stored month-file to extract Year. In next step there is filter using our 2 parameters.

Just be sure that your folder contains only monthly files (or subfolders with monthly files) - in other case you have to apply additional filter to combine only correct monthly files.

let
    Source = Folder.Files("Y:\Downloads\PowerQuery\TableCombine"),
    Ad_FileYear = Table.AddColumn(Source, "File Year", each "20" & Text.Start([Name], 2), Int64.Type),
    FilteredYearsByParameters = Table.SelectRows(Ad_FileYear, each (Number.From([File Year]) >= Number.From(YearFrom) and Number.From([File Year]) <= Number.From(YearTo))),
    Ad_BinaryToTable = Table.AddColumn(FilteredYearsByParameters, "DataTable", each Excel.Workbook([Content], true, true){0}[Data], type table),
    CombinedTables = Table.Combine(Ad_BinaryToTable[DataTable])
in
    CombinedTables

many tks again, dufoq3.

i'm sorry for the late reply as i had to test & do certain modification to it to meet my data source requirement too.

krgds, -nik

i have solved it as follows-

let
Source =
Folder.Files("C:\Mydata"),

Ad_FileYear =
Table.AddColumn(Source, "File Year", each Text.Start([Name], 4), Int64.Type),

FilteredYearsByParameters =
Table.SelectRows(Ad_FileYear,
each (Number.From([File Year]) >= Number.From(YearFrom) and Number.From([File Year]) <= Number.From(YearTo))),

Ad_BinaryToTable = Table.AddColumn(
FilteredYearsByParameters,
"DataTable",
each Table.PromoteHeaders(
Csv.Document([Content], [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv])),type table),

CombinedTables =
Table.Combine(Ad_BinaryToTable[DataTable])

in
CombinedTables

krgds, -nik