Querying A New Version of the Same Excel Doc Every Month While Retaining Previous Queries

[DougCampbell]

So I am working with source data that is far from ideal. 

Every month I am going to be provided with an Excel workbook with data that I need to pull into Power BI. The workbook has about 30 tabs, all of which are formatted differently, though the formatting remains the same from month to month. I currently have about 30 different queries to pull the data needed from each sheet and append it all together and add a column to identify the month.

When I get the new monthly workbook, it will have all new data. I need to run all of the same queries on the new workbook, but also retain the previous month's data, so solutions that I have found that just help you quickly update the data source for your existing queries do not seem to work.

I can copy the queries and go through the update the source for each one by one, but it doesn't seem like a very elegant solution. 

I am hoping that someone might have an idea to do this in a more efficient way.

Thanks,

[AlexisOlson]

Power BI is not designed to be a database that stores historical data. Ultimately, you should have a way of storing your historical data somewhere other than inside Power BI. Ideally, you could load your data into something like an actual database, but if that isn't feasible, you might consider something like saving all your workbooks in a folder and changing your queries from connecting to a single file to loading all the files from that folder (current and historical appended together).

Recommended reading for this suggestion:
https://exceloffthegrid.com/power-query-import-all-files-in-a-folder/

[dhruvinushah · Accepted solution]

Ideally, the best way would be to query that source to feed you the data via an API / Query or something that can directly connect to Power BI, but I can understand that usually that is not always possible. 

In your case, @DougCampbell  , I would suggest talking to your IT Database Admin and ask him to set up a SQL Server (or any other relational database) for you. You can then have monthly updated running on that database that performs the transformations and adds the data from the excel files into tables into the db. 
You can then query that db to Power BI using the in-built Power BI connectors (can be found in get data). 
You won't have to worry about storing that data in Power BI as the db will automatically store that data into the db tables for you. You can then worry about how to form your query for the same. 

This would be the solution I would recommend if the data was coming from an external source and needed to be saved for the longer run. 
For the integration / transformation part of the project you can ask your technical DBA to handle the decisions regarding which relational db to use/deploy (as long as you are able to query from it to Power BI). They can even use Azure Data Factory or Synapse for the deploying to Azure SQL DB. 
Many newer storage methods have also been developed: for example - using parquet files to store your data in cloud storage blogs. You can then query the parquet files directly. 

All of this will require help from other technical resources. If you are looking to do this on your own and only in Power BI, I would suggest looking into setting up Incremental Refresh and appending the newly added data. However as time passes by, your report will grow bigger and you might face refresh issues. 

Hope this helps. Thumbs up would be great! Thanks