odbc - Excel Power Query. Run multiple queries by list of templates to join tables in different files

Question

I have a question: I have 60+ tables in dbf with columns: year, product, value. Tables have different years data.

EXAMPLE.

Table 1
Year  product value
1993     Apple  98.45
1994   Mushrooms 67.54

Table 2
Year  product value
1992  Apple  95.45
2021  Melon 112.0

I need a pivot table(to consolidate) all tables in one table.

My way:

Let
DatesList={1992, 1993,1994,1995,2021},
Tbl=Odbc.Query("dsn=my_custom_dsn", "select * from c:data1993.dbf"),
Result=List.Accumulate (DatesList, Tbl, (state, current) =>Table.Join(Tbl, "product", Query.odbc("dsn=my_custom_dsn", "select * from c:data" +Text.From(current) +".dbf", "product")

in
 Result

Its ok, but results only for the last date. How to save Table between dates

Please, help

question from:https://stackoverflow.com/questions/65892089/excel-power-query-run-multiple-queries-by-list-of-templates-to-join-tables-in-d

Answer 1

I think you are overcomplicating.

Try this:

let
      DatesList = Table.FromList({1992,1993,1994,1995,2021}, Splitter.SplitByNothing(), {"Year"}, null, ExtraValues.Error)
    , #"Added Custom" = Table.AddColumn(DatesList, "Data", each Odbc.Query("dsn=my_custom_dsn", "select * from c:data" & Number.ToText([Year]) & ".dbf"))
    , #"Expanded Data" = Table.ExpandTableColumn(#"Added Custom", "Data", {"product", "table"}, {"product", "table"})
in
    #"Expanded Data"

Remember in your question you misspelled Odbc.Query