Forum Discussion
replace one table with a merged identical table
Good afternoon, everyone,
I have an Excel file with 3000 rows and 6 columns. I need to copy and paste this Excel file into Power BI so there will be no any connection between Power BI and the Excel data source.
Since Power BI limits the copy and paste to 3000 cells, I split the Excel files into a few parts. Then I copy and paste these small Excel files into Power BI and merge these tables using Appdend Queries approach. Until this step, it works perfectly.
Now I need to use this merged table to repace another indentical table named [New York] and then delete all these tables including the merged tables. I just could not go through this step.
Appreciate your help.
Dennis
Hi 12Bowers12 ,
I've had a look through your conversation with Greg_Deckler and feel that you both may be missing a trick here.
I am assuming that you want to copy and paste your data from Excel to PBI because you don't want PBI data to change if the Excel workbook is updated and saved, right?
If so, then create a new query connecting to your Excel file as normal. When the table has loaded, right-click on the table/query name and uncheck 'Include in report refresh'. This will prevent this query from going back to the Excel source and getting updates.
Call this new query 'New York' and you're done.
Apologies if I've misunderstood your requirements.
Pete
Thank you, Pete,
Based on your advice, I uncheck 'Include in Report Refresh' to cut off the dependency on the Excel data source. Since there is an existing table 'New York', I just delete the existing table 'New York' and rename the new table as 'New York'. Then I recreate the visuals based on the new table 'New York'.
The reason I want to cut off the dependency of the table 'New York' on the Excel data source is that the clouds don't allow to set up scheduled refresh due to credential issue.
By the way, what's difference between 'Include in Report Refresh' and 'Enable Load'?
Appreciate your help and Greg's help.
Dennis
12Bowers12 Include in report refresh prevents the query from reaching out to the source. Enable load loads the data into the data model. So, for example, for intermediate queries that feed a main query, you want to disable load but leave the include in report refresh so that those queries still feed the data to the main query. And BA_Pete is correct about turning off refresh query, I guess I didn't go there because the question was about copying and pasting.
12 Replies
12Bowers12 Don't delete them, right-click the queries and uncheck "Enable load"
Greg,
If I uncheck Enable Load on table [New York], all the visuals related to table [New York] are disabled.
Please be advised.
Dennis
Greg,
This question is actually related to other issue:
How to copy and paste an Excel files with more than 3000 cells into a Power BI table?
Dennis
Thank you, Greg,
Do you mean to uncheck "Enable Load" on table [New York]?
I don't want to keep the tables from copyed/ pasted from Excel and the newly merged table in Power BI.
Dennis
Hi 12Bowers12 ,
I've had a look through your conversation with Greg_Deckler and feel that you both may be missing a trick here.
I am assuming that you want to copy and paste your data from Excel to PBI because you don't want PBI data to change if the Excel workbook is updated and saved, right?
If so, then create a new query connecting to your Excel file as normal. When the table has loaded, right-click on the table/query name and uncheck 'Include in report refresh'. This will prevent this query from going back to the Excel source and getting updates.
Call this new query 'New York' and you're done.
Apologies if I've misunderstood your requirements.
Pete
Thank you, Pete,
Based on your advice, I uncheck 'Include in Report Refresh' to cut off the dependency on the Excel data source. Since there is an existing table 'New York', I just delete the existing table 'New York' and rename the new table as 'New York'. Then I recreate the visuals based on the new table 'New York'.
The reason I want to cut off the dependency of the table 'New York' on the Excel data source is that the clouds don't allow to set up scheduled refresh due to credential issue.
By the way, what's difference between 'Include in Report Refresh' and 'Enable Load'?
Appreciate your help and Greg's help.
Dennis
12Bowers12 Include in report refresh prevents the query from reaching out to the source. Enable load loads the data into the data model. So, for example, for intermediate queries that feed a main query, you want to disable load but leave the include in report refresh so that those queries still feed the data to the main query. And BA_Pete is correct about turning off refresh query, I guess I didn't go there because the question was about copying and pasting.