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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vanessafvg
Super User
Super User

Getting rid of source tables after append

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




1 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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

View solution in original post

17 REPLIES 17
Afitzy
New Member

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
Community Champion
Community Champion

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".

 

Hide table in Report View.png

 

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:

 

Move Query to Group.png

 

Collapse Query Group.png

Specializing in Power Query Formula Language (M)

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/

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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

 

Yes that is exactly how it works




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

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? 

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

 

 

Hi there

 

Unfortunately for now you have to refresh all the tables





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Anonymous
Not applicable

Does append stores data physically or Logically?

It stores it physically in the vertipaq compression engine




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

Hi @GilbertQ,

 

Thanks for confirming. 🙂

 

Tamas

Happy to assist




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@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, 

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
Frequent Visitor

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.