Forum Discussion
Helper IHow to delete staging tables?
I've created a report that populates two tables with identical columns but different data sets. Each of these are sourced via SQL queries that run against an Oracle database.
Using the "Combine -> Append Queries -> Append Queries As New" option I then create a third table combining the data sets of the first two. Having checked the size of the resultant .pbix file before/after creating this merged data set, it is clear that Power BI saves the merged copy of data separately.
From a visual creation perspective, I would only like to use data from the merged table. As such, to save on the amount of space taken within the .pbix file, how can I go about deleting the first two "staging" tables after the third table has been created?
Thanks, I'll give that a try
6 Replies
- GilbertQ
Super User
Hi there
Due to the way Power Query works, you would have to keep both of the staging tables.
The reason is that before the "Append Queries as New" can run it first has to bring in the source data from your 2 SQL Queries. So without those staging tables the Merged table would not be able to be created.
What I can suggest is either or if possible to combine the SQL queries, so that the data coming into your model is in one result set?
Or if you are looking to make it smaller, only bring in the columns that you need. As well as change the data types of the columns to their relevent data types (if they are not auto selected), as this can also save space in the PBIX model.
How large is your PBIX BTW?
ayupHi
Thanks for the quick reply.
Re your suggestion about combining the SQL, yes, this is a possible approach. However, this introduces complexity to the database in creating a merged data set. This adds a query performance hit to the database that I was trying to avoid by performing the data merge in PBI.
As for the size of the PBIX file, it's approx 17Mb. The combined data set has approx 70k rows.
- GilbertQ
I understand if you want to have the least impact on your database then I would suggest that you stick with your current approach.
17MB in size is quite a small model. Considering even in the free version you get 1GB.
What you could also possibly do, is try and Append the data from staging table 2, back into Staging table 1? That could possibly save you some space, as well as data refreshing time.
