Forum Discussion

hood2media's avatar
hood2media
Resolver II
2 years ago
Solved

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...
Need Help
Show and Tell
  • dufoq3's avatar
    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's avatar
    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's avatar
    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

hood2media's avatar
hood2media
Resolver II
1 year ago

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's avatar
hood2media
Resolver II
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