How to delete staging tables?

[ayup]

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?

[GilbertQ]

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?

[ayup]

Hi

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.

[Gabriel]

---

@guavaq wrote:

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.

---

Even though this solution solves this particular problem by avoiding having staging tables to delete, it does not answer the original question:

If, for whatever reason, you NEED to delete staging tables after they are being used (merged or appended), how do you do it?

Is there a way?

Thanks in advance!

Gabriel

[Gabriel]

Anybody has had the opportunity to find a solution to this?

I still can't figure out how to delete staging tables after being used and no longer needed.

Any assistance here will be greatly appreciated.

Thanks in advance,

Gabriel

[ayup · Accepted solution]

Thanks, I'll give that a try