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

dufoq3's avatar
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's avatar
    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's avatar
      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

       

       

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