Getting rid of source tables after append

[vanessafvg]

If you append a whole lot of tables in 1, is there a way of getting rid of the original tables without losing the data - iit just seems redundant to keep the original tables.

When I try to delete them though it says the appended table is depending on it, I am sure I have seen it done before where it can be removed?

thanks

[Afitzy]

Has anyone found a solution to this? I have a similar situation where I do not want to store all the queries that have been appended. 
Is there an option where one has new monthly data (Data A/B/C) added to historical data (Data Hist). The process would be somethig like:
Data A is appended to Data Hist. Data Hist 'Saves' this state and Data A can be removed without Data Hist being affected. 

Appreciate any help 🙂 

[MarcelBeug]

No, you can't delete them, but you can hide them from the Report View

In the Fields Pane of the Data tab, right-click the table and choose "Hide in Report View".

In the Query Editor, you can move such tables to a separate group (right-click query name in the Query list) and collapse the group:

[vanessafvg]

hi yes, thanks i do know about hiding it, i just really didn't want it to load 2x, i was wondering how to reduce the load.  I have a client file to evaluate and they have a variety of queries but are duplicating some of them, referencing the table would probably be a better option?  

thanks for the response. and here is the response i got on the global group which had some good tips too.  unfortuantely i have no idea how to get the link off facebook!  yes im clueless help, but here the group site page is

https://www.facebook.com/groups/powerbi/

[GilbertQ · Accepted solution]

Hi @vanessafvg

What you could do to increase performance is to disable the loading of your source tables in the Query Editor.

This will not only make the data load a bit quicker, but it will also mean that it will not load into your data model. Which means that your data model will be smaller and more efficient.

You can read this blog post by Rezza Rad where he goes into detail.

Performance Tip for Power BI; Enable Load Sucks Memory Up

[tvandorffy]

Hi @vanessafvg,

I would like to ask a followup question.

If I have several queries in my Power Bi Power Query and then I just append all of them into a new "Super Query"

and I switch off "Enable load" to all my component tables (I would not need them in my model anyway),

would their contents still refresh if I hit the "Refresh" button on my report page?

I am asking this because if I switch off "Enable load" on a query, the "Include in report refresh" option becomes

disabled, so I am not sure.

What I would want is that if I hit report refresh, my several component table would refresh, and consequently,

my "Super Query" would also refresh since its components are refreshed.

Thanks a lot,

Tamas

[GilbertQ]

Yes that is exactly how it works

[Anonymous]

Hi @GilbertQ , so to be extremely clear here. if I disable the load of my source tables, and keep only the appended table enabled, my appended table will be refreshed with new data if I update the data in my raw data files? 

[gk2go]

@GilbertQ, is it possible not to refresh the combined tables, after their first import?

[GilbertQ]

Hi there

Unfortunately for now you have to refresh all the tables

[Anonymous]

Does append stores data physically or Logically?

[GilbertQ]

It stores it physically in the vertipaq compression engine

[tvandorffy]

Hi @GilbertQ,

Thanks for confirming. 🙂

Tamas

[GilbertQ]

Happy to assist

[vanessafvg]

thanks @v-sihou-msft , @MarcelBeug and @GilbertQ for the great tips.

[v-sihou-msft]

@vanessafvg

In this scenario, if you have source query referenced in append operation, you can't delete the source one since the combined query depends on it. So if you want to combine multiple queries, you can write multiple sources in your Power Query. Then combine them together via one step.

let

Source1 = xxxxxx,

Source2 = xxxxxx,

Source3 = xxxxxx,

Source4 = xxxxxx,

#”Appended Query” = Table.Combine({Source1,Source2,Source3,Source4}),

in

#”Appended Query”

Regards, 

[MelvinC]

Hi there! Nice solution but is there an easier way to achieve this if I have over 200+queries to append? This number is also expected to keep growing as newer queries (tables) are appended from time to time. The default way queries are appended is really neat as you'd just have to choose append 3 or more and shift the queries from one box to the other...  

[ravib]

Hi Melvin,

If you're having 200+ tables to append, it looks like you're better off considering the below options:
1. If you're having one DB source, write a view with a union statement for all those tables, and read that view from PowerBI.

2. If they are files, assemble them to a folder, and use "Folder" data source to connect to it. Use "Combine and Load" or "Combine and Transform" to load into PBI.