power query | dynamic selection of source files - 2

[hood2media]

hi,

recently, with help from @dufoq3 , i managed to get the power query formula to combine files for selected / contigous periods (refpower query | dynamic selection of source files).

based on the earlier scenario (i.e. for the contigous period selection), may i kindly seek help to get the power query formula that allows me to select 2 files representing 2 non-contigous periods (e.g. 2015 & 2023)? the reason for this is so i don't have to bring in all files from all the contigous periods if they spread over a large range. by taking in the 2 files for the 2 periods, it'll save time in the data analysis process.

many thanks in advance.
warmest rgds, -nik

[dufoq3 · Accepted solution]

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 😉

[hood2media]

hi & many tks again, @dufoq3.

checked & mod for my working environment. i'm happy to inform you it's running fine as expected.
krgds, -nik

p.s.

for your kind info, i have slightly tinkered your earlier solution where for the selection of 2 different/distinct years, i amended the following line with 'or' (instead of 'and') & used "=" operator (instead of >=, <=) -

FilteredYearsByParameters = 
Table.SelectRows(Filter.folder, each (Number.From([File Year]) = Number.From(#"Year-1") or Number.From([File Year]) = Number.From(#"Year-2")))