power query | dynamic selection of source files

[hood2media]

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

[dufoq3 · Accepted solution]

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 · Accepted solution]

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

[dufoq3]

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]

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 · Accepted solution]

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]

hi @dufoq3 / all,

if the files i'm working in are csv formatted, please advise on how to resolve this.

for info, i tried doing using following codes-

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 Csv.Document(File.Contents([Content])(), true, true){0}[Data], type table),
CombinedTables = Table.Combine(Ad_BinaryToTable[DataTable])
in
CombinedTables

but i get following error message for all 'DataTable'-

Expression.Error: We cannot convert a value of type Binary to type Text.
Details:
Value=[Binary]
Type=[Type]

krgds, -nik

[hood2media · Accepted solution]

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

[hood2media · Accepted solution]

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

[dufoq3]

You accepted your "thank" response as solution 🙂

[hood2media]

woopsie 😅