power query | dynamic selection of source files

hi,

i'm trying to create a dynamic query parameter function using power query that should allow me to dynamically select the source files. kindly allow me to also elaborate abit regarding my working environment / requirement as follows:

1. every month i get data in csv format which i then save either in a local drive or a sharepoint folder.

each monthly file has 23 columns and between 550k-600k of rows;

2. the files are stored in a folder named after the year in which those data are in. thus, each 'year' folder should contain 12 files for the 12 months in a year. for example, folder '2023' should contain files from jan to dec of 2023;

3. i'd append data for the 12 months for each year & name the appended files like, '2022-agg' & '2023-agg';

since the size of each month is big, consequently, the appended data for 12 months for the aggregated data for each year will also be big;

4. then, i'd also appended the data for every 3 running years for me to do further data cleaning / treatment before analyzing further using dax / data modelling in pbi dekstop.

note, this round of appended file for 3 years (36 months) will even be larger.

my requirement actually is to be able to dynamically specify 3 years of data using a dynamic query parameter function in power query. for example, if i wish to do analysis of 2015~2017 data, i'd like to have the power query function that enables me to select '2015-agg', '2016-agg', & '2017-agg'. i may do the same for other 3-year periods (e.g. 2018~2020 or 2021-2023).

i'd appreciate assistance to create the dynamic query parameter function using power query based on above working scenerio.

krgds, -nik

Need Help

Show and Tell

dufoq3
2 years ago
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
hood2media
2 years ago
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
hood2media
1 year ago
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

8 Replies

dufoq3
Community Champion
2 years ago
Hi, create 2 parameters: YearFrom and YearTo. Then copy this code to Blank Query in PQ and change in 1st step 'Source' address to your folder.

let Source = Folder.Files("Y:\Downloads\PowerQuery\TableCombine"), FilteredAggFiles = Table.SelectRows(Source, each Text.Contains([Name], "-agg.xls")), #"Inserted Text Before Delimiter" = Table.AddColumn(FilteredAggFiles, "File Year", each Number.From(Text.BeforeDelimiter([Name], "-")), Int64.Type), FilteredYearsByParameters = Table.SelectRows(#"Inserted Text Before Delimiter", each ([File Year] >= Number.From(YearFrom) and [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
- hood2media
  Resolver II
  2 years ago
  thanks for your fast response, dufoq3.
  
  prior to your feedback, i reviewed the file size of the appended files in xls which i found too large/bulky to handle. thus, i have decided the individual monthly files be appended through power query in power bi. for example, the pq for the 2022 which is based on appended jan22-dec22 tables is as flws-
  
  let
  Source = Table.Combine({#"2201", #"2202", #"2203", #"2204", #"2205", #"2206", #"2207", #"2208", #"2209", #"2210", #"2211", #"2212"})
  in
  Source
  
  [similar step will b done for other years where the table name for each year will be the year no. i.e. 2015, 2016, ..., 2023].
  
  so, using the 2 parameters YearFrom and YearTo, will you kindly show me how the append the year tables e.g. YearFrom 2021 YearTo 2023? currently, i have managed to get the appending done by hard-coding it (e.g. = Table.Combine({#"2021", #"2022", #"2023"}).
  
  warmest rgds, -nik
  - dufoq3
    Community Champion
    2 years ago
    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

Forum Discussion

power query | dynamic selection of source files

8 Replies

Related content

Convert Dynamic Data source

Dynamic Source path

Dynamic images based on selections

Dynamic Filtering based on date selection

Copy Activity Dynamic Source Connection