Creating pivot tables from multiple worksheets with power query, how to copy query for 20 workbooks?

Question

I have 20 different workbooks, each with 12 sheets (Jan-Dec). I need to summarize certian data from each workbook, spliting quartly results.

It is a sales log for 20 locations, with each sales person from the location inputting their leads into their own workbook. What I would like to be able to do is to be able to see at a glance where all locations are at on a weekly basis, but it needs to be Quarter to Date.

Using Power Query I can append queries to get results for each quarter and from there I am creating a pivot table. Is there a way to copy the queries and formatting so that I don't need to do this for each workbook?

Answer 1

I combined 35 unique excel sheet using access. Then import access query into excel worksheet. Here is a short example, that can write in access sql editor

SELECT *, "CSUR" as AllKod
FROM
  [Excel 8.0;HDR=NO;
  Database=\\Uzeleti_tervek\Eves_terv\2014\EDE\CSUR.xls;].[Osszesito$]

UNION ALL SELECT *, "KANV" as AllKod
FROM
  [Excel 8.0;HDR=NO;
  Database=\\Uzeleti_tervek\Eves_terv\2014\EDE\KANVM.xls;].[Osszesito$]

UNION ALL SELECT *, "NKAN" as AllKod
FROM
  [Excel 8.0;HDR=NO;
  Database=\\Uzeleti_tervek\Eves_terv\2014\EDE\NKAN.xls;].[Osszesito$]

UNION ALL SELECT *, "NTAD" as AllKod
FROM
  [Excel 8.0;HDR=NO;
  Database=\\Uzeleti_tervek\Eves_terv\2014\EDE\NATD.xls;].[Osszesito$];

This query "copies" tables below one by one. It is important for good reason the workshhets are the same. In the SQL code AllKod field help in the identification of row. Access sometimes rename the column like F1, F2, F3 etc. Disadvantage in this solution, access can't import numbers only text values from my sheets.