Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DougCampbell
New Member

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

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,

 

 

1 ACCEPTED SOLUTION
dhruvinushah
Responsive Resident
Responsive Resident

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









View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

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
Responsive Resident
Responsive Resident

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









Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors