power query | dynamic selection of source files - 2

Hi again,

so I created it for you a bit more complex.

1.) Create parameter and call it exactly Years or create blank query and delete whole code, then paste there this one (but don't forget to rename it!)

"2015 - 2018, 2020-2022, 2024" meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

2.) Create another blank query and paste there this code. Just edit address to your folder (as last time) in 2nd step Source

let
    paramterYears =  
        [v_yearSplitSingle = Text.SplitAny(Years, ",.;"),
         v_singleYearList = List.RemoveNulls(List.Transform(v_yearSplitSingle, each try Number.From(Text.Trim(_)) otherwise null)),
         v_multiYearRows = List.Select(v_yearSplitSingle, each Text.Contains(_, "-")),
         v_multiYearList = List.Transform(v_multiYearRows, each Text.Split(_, "-")),
         v_multiYearFinalList = List.Combine(List.Transform(v_multiYearList, each {Number.From(_{0})..Number.From(_{1})})),
         v_allYearsCombine = List.Sort(List.Combine({v_singleYearList, v_multiYearFinalList}))
        ][v_allYearsCombine],
    Source = Folder.Files("Y:\Downloads\PowerQuery\TableCombine"),
    Ad_FileYear = Table.AddColumn(Source, "File Year", each Number.From("20" & Text.Start([Name], 2)), Int64.Type),
    FilteredYearsByParameters = Table.SelectRows(Ad_FileYear, each List.Contains(paramterYears, Number.From([File Year]))),
    Ad_BinaryToTable = Table.AddColumn(FilteredYearsByParameters, "DataTable", each Excel.Workbook([Content], true, true){0}[Data], type table),
    CombinedTables = Table.Combine(Ad_BinaryToTable[DataTable])
in
    CombinedTables

Now you are able to define years by parameter Years this way:

• you can separate single years by using these 3 separators (i.e 2018, 2020, 2022😞
  1. ,
  2. .
  3. ;
• you can also use ranges i.e. 2015-2020
• you can also combine both i.e. 2015-2018, 2020-2022, 2024 to filter years:
  2015, 2016, 2017, 2018, 2020, 2021, 2022, 2024

I hope this will meet your expectations 😉